LATERAL Joins
LATERAL allows a subquery in FROM to reference columns from earlier items in the FROM clause — it's a correlated subquery that can return multiple rows and be used as a joined table. Each outer row produces a different subquery result.
LATERAL is a correlated subquery that's allowed to sit at the table. Regular FROM subqueries are strangers who must answer your question without knowing anything about you. LATERAL subqueries know exactly who you are (outer row columns) and tailor their answer.
Without LATERAL, subqueries in FROM cannot reference outer table columns. With LATERAL: SELECT u.name, last_orders.total FROM users u, LATERAL (SELECT SUM(amount) AS total FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3) last_orders. Common uses: top-N per group (LATERAL with LIMIT), unnesting arrays with a function (generate_series, unnest), calling set-returning functions per row. LATERAL JOIN vs CROSS JOIN LATERAL: both work; LEFT JOIN LATERAL ... ON TRUE returns the outer row even if the subquery returns no rows.
LATERAL is the SQL equivalent of a flatMap operation — for each outer row, produce zero or more rows from the subquery. This enables patterns impossible with standard joins: 'for each user, find their most recent 5 orders' using LATERAL with LIMIT (not achievable with a window function inside a join without a subquery). LATERAL with unnest: SELECT id, tag FROM articles, LATERAL unnest(tags) AS tag — normalizes an array column into rows. LATERAL with set-returning functions enables table-valued function calls per row. Performance: LATERAL is essentially a correlated subquery — each outer row triggers one execution of the subquery. Index on the correlated column (user_id in the example) is critical.
LATERAL is my go-to for top-N per group and per-row function calls. For 'last 3 orders per user': JOIN LATERAL (SELECT * FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3) last ON TRUE. The index on orders.user_id makes this fast. Without LATERAL, this requires ROW_NUMBER() OVER (...) in a subquery, which is fine but LATERAL is often more readable.
Using LATERAL without an index on the correlated column. Since the subquery runs once per outer row, a sequential scan of orders for each user produces O(users × orders) — catastrophically slow. Index on the join condition column is mandatory.