📐SQL syntax

Syntax is the easy part, token rules bite first

Commas between projections, balanced parentheses, string quotes that match your dialect, and semicolons that separate statements are all parser concerns. A missing comma often yields an error line number below the real mistake. Set your editor’s SQL dialect to the engine you actually run, not “generic,” so highlighting and snippets match reality.

Declarative by design

You write what relation you want; the engine decides how. The order you type clauses (SELECT first) is not the logical evaluation order, getting this wrong is a top source of “why doesn’t my alias work in WHERE?” confusion.

🧮Logical processing order

Memorize this once; debugging weird GROUP BY errors becomes 10× faster.

Step	Clause	Role
1	FROM / JOIN	Build the working row set (the Cartesian foundation before filters).
2	WHERE	Filter rows, predicates here cannot see SELECT aliases in most engines.
3	GROUP BY	Collapse rows into buckets for aggregates.
4	HAVING	Filter buckets using aggregate results.
5	SELECT	Project columns and expressions onto the surviving rows/groups.
6	ORDER BY	Sort the final result, first place SELECT aliases are reliably visible.

Why this ordering matters

Window functions (not shown in the minimal list) sit logically after GROUP BY but still see row-level detail depending on framing, another reason to reach for CTEs when teaching or decomposing tricky queries.

SQL

Canonical SELECT shape

SELECT d.department_id, COUNT(*) AS headcount
FROM employees AS e
JOIN departments AS d ON d.department_id = e.department_id
WHERE e.end_date IS NULL
GROUP BY d.department_id
HAVING COUNT(*) >= 3
ORDER BY headcount DESC;

✓Illustrative result set

department_id	headcount
40	12
20	8
10	5

🏷️Identifiers & quoting

Case folding and reserved words trip migrations between vendors.

Unquoted names

Fold to a default case per catalog rules. Team style guides should pick snake_case and stick to it, future you will grep happier.

Double quotes

PostgreSQL / Oracle: "MixedCase" preserves case. Use sparingly; tools and ORMs generate quoted identifiers that are painful to type.

Backticks

MySQL: `rank` escapes reserved identifiers. Prefer renaming the column to rank_score when you can.

Mystery: column not found

Referencing a SELECT alias inside WHERE fails because WHERE runs before the projection list is bound.

Pattern: wrap in CTE

Use a subquery/CTE: WITH t AS (SELECT …, expr AS alias) SELECT * FROM t WHERE alias …

TL;DR

SQL syntax is mostly declarative: clauses combine in a logical evaluation order (roughly FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → DISTINCT → ORDER BY → LIMIT) that differs from how you type SELECT first. Mastering that order explains alias visibility, aggregate rules, and why some rewrites need CTEs, dialect keywords differ, but the pipeline concept is portable.