postgres-syntax-lateral-joins

Quick Reference :

A LATERAL subquery in the FROM clause is allowed to reference columns from preceding FROM items. Without LATERAL, each FROM subquery is evaluated once, independent of every other item. With LATERAL, the subquery is re-evaluated FOR EACH ROW of the preceding items, with their column values bound. The result rows are then joined with the source rows in the standard JOIN-tree fashion. The keyword is REQUIRED for (SELECT ...) subqueries that cross-reference earlier items ; the keyword is OPTIONAL for function_call(args) set-returning functions because function arguments may always reference earlier FROM items.

The two dominant join shapes are FROM outer, LATERAL (...) sub (implicit CROSS JOIN ; rows where the subquery returns zero rows are DROPPED) and FROM outer LEFT JOIN LATERAL (...) sub ON true (LEFT outer ; rows where the subquery returns zero rows are KEPT with NULLs in the sub-columns). Picking the wrong one is the single most common bug : "my query suddenly lost half the rows" is almost always a missing LEFT JOIN LATERAL ON true. LATERAL is mandatory for top-N-per-group (ORDER BY + LIMIT k inside the subquery, correlated to the outer row), for expanding JSON arrays per row (jsonb_array_elements), and for per-row computed joins where the inner table's filter depends on the outer row's columns.

When To Use This Skill :

ALWAYS use this skill when :

• Writing top-N-per-group queries (3 latest posts per user, 5 highest-priced items per category)
• Calling a set-returning function (unnest, generate_series, jsonb_array_elements) with arguments from a preceding FROM item
• Expanding a JSON array column to rows alongside the outer row's other columns
• Joining to a subquery whose predicate depends on the outer row
• Deciding between a scalar subquery, a regular JOIN, and a LATERAL JOIN