11 parts · 84 chapters
SQL, The Language
Not a tutorial. SQL as a formal system: what each clause means, in what order the engine evaluates it, and why your intuition about NULL is wrong. Evaluation order and three-valued logic explain nearly every SQL surprise you have ever had, and this module makes both precise.
00
Where SQL came from
Codd’s 1970 paper · SEQUEL, System R, and why the syntax looks like English · The standards, 1986 to 2023 · Why every dialect diverges · Where SQL betrays the relational model
5 ch · ~20 min01The semantics nobody teaches
The logical order of evaluation · The alias rule, stated precisely · Three-valued logic · NULL semantics · NULL in indexes, constraints and sorting · Bags versus sets · Row value expressions
7 ch · ~20 min02Joins, properly
Every join is a filtered cross join · INNER, LEFT, RIGHT, FULL · Join fan-out · Self joins and hierarchies · Semi-joins and anti-joins · The NOT IN + NULL trap · LATERAL — the join that sees the left row · NATURAL JOIN and USING · Non-equi joins
9 ch · ~20 min03Aggregation & grouping
Aggregates and NULL · GROUP BY and functional dependency · HAVING versus WHERE · GROUPING SETS, ROLLUP, CUBE · FILTER, and its workaround · Conditional aggregation and pivoting · COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col) · Ordered-set aggregates and percentiles
8 ch · ~20 min04Window functions
A window is a view per row · PARTITION BY and ORDER BY, decomposed · Frames: ROWS, RANGE, GROUPS · Ranking functions · LAG, LEAD, and the value functions · Running totals and moving averages · Gaps and islands · Deduplication · Execution cost · Named windows
10 ch · ~20 min05CTEs, recursion, subqueries
Subquery types · Correlated subqueries, and the N+1 inside SQL · CTEs as naming, and as optimization fences · Recursive CTEs · Graph traversal and cycle detection · Generating series and gap-filling · Hierarchy models · Recursion limits
8 ch · ~20 min06DDL, constraints, the schema as a contract
Numeric types, and the money rule · Dates, times, and time zones · Strings and collations · Constraints and their cost · Foreign key actions · Deferrable constraints · Generated columns · Domains, enums, and modelling with types · Migration as a language problem
9 ch · ~20 min07DML, transactions, concurrency
INSERT variants · UPSERT and its hazards · UPDATE with a join · DELETE vs TRUNCATE vs DROP · RETURNING · MERGE and its races · Savepoints · Concurrency-safe SQL patterns
8 ch · ~20 min08Advanced & vendor frontiers
JSON in SQL · Arrays and composite types · Full-text search · Temporal tables · TABLESAMPLE · Set operations · Views and materialized views · Procedures, functions, triggers
8 ch · ~20 min09Writing SQL the engine likes
Sargability · Implicit conversions · Keyset pagination · The anti-pattern catalogue · Reading your own plan · Rewriting one query five ways
6 ch · ~20 min10Build a SQL engine
Tokenizer and grammar · AST to logical plan to physical plan · Volcano iterators · Implementing window functions · Recursive CTEs as a fixpoint loop · Connecting to the storage engine
6 ch · ~20 min