SQL is not just a bag of commands - it is a language with a grammar. Once you understand how a statement is built, which order its clauses must appear in, and how the database actually reads them, most "why won't this run?" errors stop being mysteries. This guide is about the rules of that grammar, not a command reference.
What this guide covers
- Anatomy of a SQL statement
- The clauses of a SELECT and their written order
- Logical query processing order
- Identifiers, quoting & case sensitivity
- Literals: strings, numbers, dates, booleans
- Operators & precedence
- NULL and three-valued logic
- Expressions, functions & CASE
- Comments, whitespace & terminators
- Common syntax errors & how to read them
- Dialect differences at a glance
Anatomy of a SQL statement
Every SQL statement is assembled from a small set of building blocks. Learn to spot them and you can read - and repair - almost any query. The pieces are keywords, identifiers, clauses, expressions, literals, operators, and the statement terminator. If you are new to the language entirely, start with the SQL fundamentals and the introduction to SQL, then come back here for the grammar.
Consider this ordinary query with every part labelled:
-- keyword identifier keyword identifier
SELECT first_name, salary * 1.10 AS raised
FROM employees
WHERE department_id = 7
ORDER BY raised DESC;- Keywords are reserved words that give a statement its shape: SELECT, FROM, WHERE, ORDER BY. They are case-insensitive in every major database, but the near-universal convention is UPPERCASE so the skeleton of the query jumps out.
- Identifiers name things: tables (employees), columns (first_name), aliases (raised), schemas, and functions.
- Clauses are the major sections, each introduced by a keyword: the SELECT clause, the FROM clause, the WHERE clause, and so on.
- Expressions are anything that evaluates to a value: salary * 1.10, department_id = 7, a literal, a column reference, or a function call.
- Literals are fixed values written directly into the query: 1.10, 7, or a quoted string.
- The terminator is the semicolon ;. It marks the end of one statement and is what lets you send several statements in a single batch.
SQL is free-form: whitespace and line breaks between tokens do not matter, so the query above could be written on one line. Formatting is purely for humans. What does matter is that the tokens appear in a valid order - and that is the next thing to master.
Qualified names and aliases
An identifier can be qualified with a dotted prefix to remove ambiguity: schema.table.column. You only need the parts required to be unambiguous, so e.salary is enough when e uniquely identifies a table. When two joined tables share a column name, qualifying is mandatory or the engine complains the reference is ambiguous:
SELECT e.name, d.name AS dept
FROM employees e
JOIN departments d ON e.department_id = d.id;An alias is a temporary name introduced with AS (optional for tables, and the keyword is often dropped). Column aliases rename output; table aliases give you a short handle to qualify columns. The one hard rule about aliases is when they become visible, which the logical processing order dictates - covered below.
Keyword vs. identifier: a word only counts as a keyword when it sits where the grammar expects one. SELECT count FROM ... is legal because count here is being read as a column name, not the COUNT function - context decides.
The clauses of a SELECT and their written order
A SELECT statement must have its clauses in one specific written order. You may omit clauses, but you may never reorder them. Put WHERE after ORDER BY and the parser rejects the whole statement.
SELECT -- 1. which columns / expressions to return
FROM -- 2. source tables and joins
WHERE -- 3. filter individual rows
GROUP BY -- 4. collapse rows into groups
HAVING -- 5. filter the groups
ORDER BY -- 6. sort the result
LIMIT -- 7. cap the number of rowsGet the order wrong and you get a parse error before the query ever touches your data:
SELECT name FROM employees
ORDER BY name
WHERE active = 1; -- WHERE cannot follow ORDER BYSELECT name FROM employees
WHERE active = 1
ORDER BY name;The written order is fixed, but here is the twist that trips up almost everyone: it is not the order the database evaluates the clauses in. Understanding that difference is the single most useful thing in this guide.
Logical query processing order
SQL is a declarative language. You describe the result you want; the engine decides how to produce it. Conceptually, though, the clauses are processed in a fixed logical order that differs from the written order. Every result you can reason about must be explainable by this sequence:
Walk through what each step actually does:
FROM and JOIN
The engine gathers the source tables and combines them, producing a working set of rows. Table aliases defined here are visible to every later step.
WHERE
Rows that do not satisfy the predicate are discarded. This happens before grouping and before the SELECT list is evaluated.
GROUP BY
The surviving rows are folded into groups. After this step you can only reference grouping columns and aggregate expressions.
HAVING
Groups are filtered. Because it runs after grouping, HAVING can use aggregates like COUNT(*) > 5 that WHERE cannot.
SELECT
Now - and only now - the SELECT list is computed. This is where column aliases are created.
DISTINCT
Duplicate rows in the projected result are removed.
ORDER BY
The result set is sorted. Because it runs after SELECT, ORDER BY can use aliases defined in the SELECT list.
LIMIT / OFFSET
Finally, the sorted result is trimmed to the requested window of rows.
Why a SELECT alias will not work in WHERE
This is the classic consequence of the logical order. WHERE is evaluated at step 2, but the alias is not created until SELECT at step 5. At the moment WHERE runs, the alias simply does not exist yet.
SELECT price * quantity AS total
FROM order_lines
WHERE total > 100; -- error: "total" is unknown hereYou have two correct options: repeat the expression, or wrap the query so the alias exists in an outer scope.
-- Option A: repeat the expression
SELECT price * quantity AS total
FROM order_lines
WHERE price * quantity > 100;
-- Option B: filter in an outer query
SELECT * FROM (
SELECT price * quantity AS total FROM order_lines
) t
WHERE total > 100;The same logic explains why ORDER BY total is allowed - sorting happens at step 7, long after the alias was created at step 5. For a fuller tour of the query clauses in action, see the data query language commands.
Watch out: MySQL and MariaDB bend this rule and do allow SELECT aliases in GROUP BY, HAVING, and even parts of ORDER BY. That is a vendor extension - it is not standard SQL and it will not port to PostgreSQL or SQL Server.
Identifiers, quoting & case sensitivity
An identifier is the name of a database object. Unquoted (or "regular") identifiers must follow rules: start with a letter or underscore, contain only letters, digits, and underscores, and not collide with a reserved word. When you need a name that breaks those rules - spaces, punctuation, a reserved word, or a specific letter case - you must quote it, and each database quotes differently.
| Database | Identifier quote | Example | String literal quote |
|---|---|---|---|
| MySQL / MariaDB | Backtick ` | `order` | Single quote ' |
| PostgreSQL | Double quote " | "order" | Single quote ' |
| SQL Server | Brackets [ ] | [order] | Single quote ' |
| Oracle / DB2 | Double quote " | "ORDER" | Single quote ' |
| ANSI standard | Double quote " | "order" | Single quote ' |
The most common beginner mistake is using double quotes for a string value. In standard SQL and PostgreSQL, double quotes mean "identifier," so this fails looking for a column named Draft:
SELECT * FROM orders WHERE status = "Draft";SELECT * FROM orders WHERE status = 'Draft';Case sensitivity
Keywords are always case-insensitive. Identifier case sensitivity, however, varies and is a real source of portability pain:
- PostgreSQL folds unquoted identifiers to lowercase. MyTable and mytable are the same object, but "MyTable" (quoted) is a distinct, case-sensitive name.
- Oracle folds unquoted identifiers to uppercase.
- SQL Server case sensitivity depends on the database collation; default installs are case-insensitive.
- MySQL column and alias names are case-insensitive, but table names inherit the case sensitivity of the underlying file system - case-sensitive on Linux, insensitive on Windows.
Practical rule: use lower_snake_case for every table and column and never quote identifiers in day-to-day code. You then sidestep case-folding surprises and reserved-word clashes entirely, on every engine.
Reserved words
Reserved words are keywords the parser will not accept as a bare identifier. Name a column order or user and most engines will error until you quote it. Better still, pick a name that is not reserved. A sampling of words that bite people:
| Reserved word | Why people hit it | Safer name |
|---|---|---|
| order | An "orders line" column, or clashes with ORDER BY | order_id, sort_order |
| user | Reserved in PostgreSQL & SQL Server | app_user, account |
| group | Clashes with GROUP BY | group_name, team |
| date / timestamp | Also data-type keywords | created_at, event_date |
| value | Reserved in several engines | amount, setting_value |
| check / key | Constraint keywords | is_checked, key_name |
Literals: strings, numbers, dates, booleans
A literal is a value written directly into a statement. Each type has its own syntax.
String literals
Strings are wrapped in single quotes. To include a literal single quote inside the string, double it - that is the ANSI-standard escape and it works everywhere:
INSERT INTO notes (body) VALUES ('O''Brien signed off');
-- stored value: O'Brien signed offMySQL additionally allows a backslash escape ('O\'Brien') and double quotes for strings, but neither is portable. Stick to doubling the quote. PostgreSQL also offers "dollar quoting" ($$O'Brien$$) which needs no escaping at all - handy inside function bodies.
Numeric literals
Numbers are written unquoted: integers (42, -7), decimals (19.99), and scientific notation (1.5e3 for 1500). Quoting a number turns it into a string, which forces implicit conversions and can silently defeat an index.
Date and time literals
There is no universal date literal syntax. The portable approach is an ISO-8601 string that the engine casts to a date, and the safest year-month-day format is 'YYYY-MM-DD':
SELECT * FROM events
WHERE starts_on >= '2026-01-01'
AND starts_on < '2026-02-01';
-- Standard typed literals (PostgreSQL, SQL standard):
SELECT DATE '2026-07-04', TIMESTAMP '2026-07-04 09:30:00';Watch out: a string like '01/02/2026' is ambiguous - is it January 2 or February 1? Interpretation depends on the server's locale and settings. Always use unambiguous YYYY-MM-DD.
Boolean and NULL literals
PostgreSQL and MySQL accept TRUE / FALSE (MySQL treats them as 1 / 0). SQL Server has no boolean type - you use BIT columns with 1 and 0. The keyword NULL is a literal of its own, and it behaves unlike anything else - which is the next section.
Operators & precedence
Operators combine values inside expressions. When several appear together, precedence decides who binds first, exactly like arithmetic in maths. The table below lists the main operator groups from highest precedence (evaluated first) to lowest.
| Precedence | Category | Operators |
|---|---|---|
| 1 (highest) | Unary / sign | + - (unary), bitwise ~ |
| 2 | Multiplicative | * / % |
| 3 | Additive & concat | + - || |
| 4 | Comparison | = <> < > <= >= |
| 5 | Pattern & range | BETWEEN LIKE IN IS |
| 6 | Logical NOT | NOT |
| 7 | Logical AND | AND |
| 8 (lowest) | Logical OR | OR |
The precedence trap that costs the most is AND binding tighter than OR. This query does not do what its author intended:
-- reads as: active = 1 AND (plan = 'pro' OR plan = 'team')? No.
SELECT * FROM accounts
WHERE active = 1 AND plan = 'pro' OR plan = 'team';
-- actually: (active = 1 AND plan = 'pro') OR plan = 'team'
-- so inactive 'team' accounts sneak in.SELECT * FROM accounts
WHERE active = 1 AND (plan = 'pro' OR plan = 'team');Parentheses cost nothing and remove all doubt. When in doubt, parenthesise. Note also that string concatenation is not portable: the ANSI operator is || (PostgreSQL, Oracle, SQLite), MySQL uses CONCAT() by default, and SQL Server uses + or CONCAT().
NULL and three-valued logic
NULL means "unknown" or "no value," and it is the source of more subtle bugs than any other feature. The critical rule: NULL is never equal to anything, not even another NULL. Any comparison with NULL yields a third truth value - UNKNOWN - not TRUE or FALSE. This is three-valued logic (3VL).
SELECT * FROM users WHERE middle_name = NULL;
-- returns 0 rows: (anything = NULL) is UNKNOWN, never TRUESELECT * FROM users WHERE middle_name IS NULL;
-- use IS NULL / IS NOT NULL to test for the markerBecause WHERE only keeps rows where the predicate is exactly TRUE, both FALSE and UNKNOWN rows are dropped. Here is how the logical operators behave once UNKNOWN enters the picture:
| A | B | A AND B | A OR B | NOT A |
|---|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE | FALSE |
| TRUE | FALSE | FALSE | TRUE | FALSE |
| TRUE | UNKNOWN | UNKNOWN | TRUE | FALSE |
| FALSE | FALSE | FALSE | FALSE | TRUE |
| FALSE | UNKNOWN | FALSE | UNKNOWN | TRUE |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
Two rows from that table are worth memorising: TRUE OR UNKNOWN is TRUE (because one true branch is enough), and FALSE AND UNKNOWN is FALSE (because one false branch dooms an AND). Everything else involving UNKNOWN stays UNKNOWN.
Watch out: NOT IN with a subquery that returns any NULL returns zero rows, because x <> NULL is UNKNOWN for every candidate. Prefer NOT EXISTS, or filter NULLs out of the subquery first. This is a top entry in our common SQL mistakes roundup.
Expressions, functions & CASE
An expression is any fragment that produces a value, and expressions can appear almost anywhere a value is allowed: in the SELECT list, in WHERE, in ORDER BY, inside function arguments. They nest freely.
Function calls
A function call is an identifier followed immediately by parentheses holding zero or more argument expressions. There must be no space rules to worry about, but the parentheses are mandatory even when there are no arguments (NOW(), CURRENT_DATE being a rare keyword exception). Explore the catalogue in SQL functions and the printable SQL functions list.
SELECT
UPPER(last_name) AS surname,
ROUND(salary / 12, 2) AS monthly,
COALESCE(nickname, first_name) AS display
FROM employees;The CASE expression
CASE is SQL's inline conditional. It evaluates branches top to bottom and returns the first match, so it is an expression that yields a value - not a control-flow statement. There are two forms:
-- Searched CASE: each WHEN is a full condition
SELECT name,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
ELSE 'C'
END AS grade
FROM results;
-- Simple CASE: compares one expression to values
SELECT CASE status
WHEN 1 THEN 'Active'
WHEN 0 THEN 'Inactive'
ELSE 'Unknown'
END
FROM accounts;Omit the ELSE and unmatched rows get NULL - a common surprise. Because CASE is an expression, you can nest it, put it in ORDER BY for custom sort orders, or wrap it in an aggregate for conditional counts. That last trick is worth knowing: since aggregates ignore NULL, a CASE that returns a value only for rows you care about becomes a filtered count in a single pass:
SELECT
COUNT(*) AS total,
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
COUNT(CASE WHEN amount > 100 THEN 1 END) AS big_orders
FROM orders;Note how the second column uses SUM of 1s and 0s, while the third relies on COUNT ignoring the implicit NULL from the missing ELSE. Both patterns are idiomatic and portable across every dialect.
Comments, whitespace & terminators
SQL has two comment styles. A double-hyphen -- starts a comment that runs to the end of the line. A /* ... */ pair delimits a block comment that can span multiple lines and, in most engines, nest.
-- line comment: everything after the dashes is ignored
SELECT id, -- trailing comments are fine too
name
/* block comment
spanning several lines,
handy for disabling a chunk */
FROM customers;MySQL quirk: the -- comment marker must be followed by whitespace (a space or newline). --this is not a comment in MySQL, whereas -- this is. MySQL also supports # as a line comment.
Whitespace - spaces, tabs, newlines - separates tokens and is otherwise ignored, so you are free to indent and align for readability. The semicolon terminates a statement. A single statement rarely needs one, but it is required to separate multiple statements in a batch, and many tools (and stored-procedure bodies) demand it. Get in the habit of ending every statement with ;.
Common syntax errors & how to read them
Parser errors look intimidating but they follow a pattern: the engine tells you roughly where it got confused and what it expected. The location it reports is usually just after the real mistake, because the parser reads left to right and only notices the problem when the next token does not fit.
| Message (paraphrased) | Usual cause | Fix |
|---|---|---|
| "syntax error at or near ORDER" | A clause is missing or out of order just before ORDER | Check the clause written before it (often a stray comma or missing FROM) |
| "column 'total' does not exist" | Using a SELECT alias in WHERE / GROUP BY | Repeat the expression or wrap in a subquery |
| "unterminated quoted string" | An apostrophe in data not doubled | Double the inner quote: 'O''Brien' |
| "unknown column 'Draft'" | Double quotes used for a string value | Use single quotes for strings |
| "must appear in GROUP BY clause" | Selecting a non-aggregated column with GROUP BY | Add the column to GROUP BY or wrap it in an aggregate |
| "missing right parenthesis" | Unbalanced parentheses in an expression or subquery | Count opening vs. closing brackets |
When you hit a "syntax error near X," resist staring at X. Look at the token before it - a missing comma, an extra comma, a misspelled keyword, or a clause in the wrong place is almost always sitting just to the left. Reading errors this way turns most of them into thirty-second fixes.
Dialect differences at a glance
The core grammar in this guide is standard and portable, but every engine adds its own spelling for a handful of common tasks. Keep this summary handy when moving code between systems.
| Feature | MySQL / MariaDB | PostgreSQL | SQL Server |
|---|---|---|---|
| Identifier quoting | `name` | "name" | [name] |
| Row limiting | LIMIT 10 | LIMIT 10 | TOP 10 / OFFSET FETCH |
| String concat | CONCAT(a,b) | a || b | a + b |
| Auto-increment | AUTO_INCREMENT | SERIAL / IDENTITY | IDENTITY(1,1) |
| Current time | NOW() | NOW() | GETDATE() |
| Boolean type | TINYINT(1) | BOOLEAN | BIT |
| Alias in WHERE | Extended (allowed in some clauses) | Standard (not allowed) | Standard (not allowed) |
None of these change the fundamental grammar - keywords, clauses, the logical processing order, and three-valued logic are identical across all three. Learn the rules once and only the surface spelling changes. Keep the SQL cheat sheet nearby, and when you are ready to build real fluency, work through the structured SQL beginner course.
Putting it together
SQL rewards a little grammar knowledge out of all proportion. Know the seven clauses and their written order, hold the logical processing order in your head, respect single quotes for strings and IS NULL for the unknown marker, and parenthesise your AND/OR logic - and the language stops fighting you. Everything else is vocabulary you can look up as you go.