LATERAL joins

What LATERAL changes about FROM

Before you reach for an N+1 loop or stack three correlated subqueries, ask yourself: does each result row depend on the current value of an outer row? If yes, LATERAL is the tool that makes it one query instead of many. Normally, the table expressions in a FROM clause are independent: FROM users u, orders o lets o know nothing about u — the join condition links them afterward. A subquery in FROM is the same; it can’t see the other tables. LATERAL removes that wall. A LATERAL subquery (or function) in FROM may reference columns from FROM items that appear before it, and it is evaluated once per row of those earlier items.

That’s the whole idea: a correlated subquery, but in FROM instead of SELECT or WHERE, so it can return multiple columns and multiple rows per outer row rather than a single scalar.

-- For each user, their 3 most recent orders — whole rows, not aggregates
SELECT u.id, u.name, recent.id AS order_id, recent.created_at
FROM users u
CROSS JOIN LATERAL (
  SELECT o.id, o.created_at
  FROM orders o
  WHERE o.user_id = u.id        -- references u — only legal because of LATERAL
  ORDER BY o.created_at DESC
  LIMIT 3
) AS recent;

The inner subquery references u.id, which is only allowed because of LATERAL. For each user row, Postgres runs that little “top 3 orders for this user” query and joins the (up to 3) results back. This is the canonical top-N-per-group solution, and there is no concise non-lateral equivalent that returns whole rows.

CROSS vs LEFT JOIN LATERAL, and set-returning functions

CROSS JOIN LATERAL (...) drops an outer row if its subquery returns nothing — a user with zero orders disappears. To keep every outer row (NULL-extended when the subquery is empty), use LEFT JOIN LATERAL (...) ON true. The ON true is required syntax: a lateral left join still needs an ON, and the correlation already lives inside the subquery, so the join condition is just “always.”

LATERAL also shines with set-returning functions — functions that return many rows, like jsonb_array_elements or generate_series. To explode a JSONB array column into rows, one per element, per parent row:

-- One row per tag in each product's JSONB tags array
SELECT p.id, tag.value AS tag
FROM products p
CROSS JOIN LATERAL jsonb_array_elements_text(p.tags) AS tag(value);

The function call references p.tags, which is implicitly lateral when a function in FROM references an earlier table — Postgres even lets you omit the keyword for functions, but writing it is clearer. This unnest-per-row pattern is everywhere once you store arrays or JSON (unit 06 goes deep on JSONB).

Why LATERAL often beats a correlated scalar subquery

You can sometimes fake per-row results with correlated scalar subqueries in SELECT — but each one returns a single value and you need a separate subquery per column, each re-scanning the same orders. Want the latest order’s id and date and total? That’s three correlated subqueries, three scans. A single LATERAL returns all three columns from one evaluation per row. Fewer scans, fewer round trips than the application-side N+1 loop, and the planner can choose an index-driven nested loop where the inner LIMIT 3 reads only a few index entries per user — turning the 4-second N+1 endpoint into a sub-100 ms single query.