INNER, OUTER, LATERAL, self-joins, join amplification traps, and WITH RECURSIVE — how PostgreSQL composes relations.
SQL is a language for composing relations. These lessons cover the JOIN operators that matter most in practice — including the LATERAL join that unlocks per-row subqueries, and WITH RECURSIVE for graphs and hierarchies — and the traps (amplification, NULL propagation) that catch developers off guard.
Paired course
Advanced JOIN TechniquesGo deeper with structured exercises, datasets, and production patterns.
Forget the set-theory Venn diagrams. A JOIN composes two collections of objects into a new collection that has the properties of both.
A JOIN does not just combine tables — it multiplies rows. One-to-many joins inflate counts silently, and aggregate queries over joined data produce wrong results unless you account for it.
Follow a parent reference up a tree of unknown depth — a whole river basin — with a query that refers back to its own output.
LATERAL lets the right-hand subquery reference columns from the left side — enabling top-N per group in a single scan without window functions and outer filters.
Go deeper
These lessons are the foundation. The book gives you the complete treatment, with real datasets you can run and the production patterns that turn thousands of lines of code into simple queries.
Get the Book — $89