LATERAL JOIN in PostgreSQL: Top-N per Group and Correlated FROM Subqueries

A plain subquery in FROM is sealed off: it cannot see columns of its sibling tables. LATERAL lifts that restriction — a subquery on the right of a join may reference columns of tables listed to its left. This turns the FROM list into a loop: for each outer row the subquery is evaluated afresh.

Why LATERAL exists

Compare two subqueries. The first is ordinary, and it will not compile because o tries to reach u.id from a table declared outside it:

-- Fails: subquery cannot see u.id
SELECT u.id, o.amount
FROM users AS u,
     (SELECT amount FROM orders WHERE user_id = u.id) AS o;

Add LATERAL and the subquery gains access to u.id. PostgreSQL runs the right side once per users row, substituting that row's values:

SELECT u.id, o.amount
FROM users AS u,
     LATERAL (SELECT amount FROM orders WHERE user_id = u.id) AS o;

Key properties:

• You may only reference tables that appear to the left in the FROM list.
• A comma in FROM before LATERAL is a CROSS JOIN LATERAL.
• The subquery runs row by row, so an ORDER BY and LIMIT tied to the outer row are legal inside it.

Top-N per group

This is the signature LATERAL move. You want the two most expensive orders for each user. A window function can do it too, but LATERAL reads more directly and lets you LIMIT right inside:

SELECT u.id, u.email, top.id AS order_id, top.amount
FROM users AS u
JOIN LATERAL (
       SELECT id, amount
       FROM orders
       WHERE user_id = u.id
       ORDER BY amount DESC
       LIMIT 2
     ) AS top ON true;

The subquery fires once per user and yields at most two rows. The ON true is required: JOIN syntax demands an ON, and the join logic already lives inside the subquery (WHERE user_id = u.id).

LEFT JOIN LATERAL vs the comma form

A subtle but important point. With INNER JOIN LATERAL or the comma form, users with no orders drop out: the subquery returned zero rows, so the outer row vanishes.

-- Users with zero orders disappear
SELECT u.id, top.amount
FROM users AS u,
     LATERAL (SELECT amount FROM orders WHERE user_id = u.id
              ORDER BY amount DESC LIMIT 2) AS top;

To keep every user, use LEFT JOIN LATERAL ... ON true. For users with no orders the subquery columns become NULL, but the row itself stays:

SELECT u.id, top.amount
FROM users AS u
LEFT JOIN LATERAL (
       SELECT amount FROM orders WHERE user_id = u.id
       ORDER BY amount DESC LIMIT 2
     ) AS top ON true;

Gotcha: ON true in a LEFT JOIN LATERAL is deceptive. It looks like a constant-true predicate that makes LEFT pointless, but it does not: the outer row survives with NULLs only when the subquery returns zero rows. The correlation is carried by the inner WHERE, not by ON. Put a real condition in ON and it filters after LATERAL expands, breaking the row-preserving behavior.

Set-returning functions in LATERAL

LATERAL shines with functions like generate_series, unnest, or jsonb_array_elements, which need a value from a neighboring column. Generate one row per month since each user signed up:

SELECT u.id, m.month
FROM users AS u
CROSS JOIN LATERAL generate_series(
       date_trunc('month', u.created_at),
       date_trunc('month', now()),
       interval '1 month'
     ) AS m(month);

Before a function call in FROM the keyword LATERAL is optional — for table functions it is implied. Writing it explicitly is still worthwhile: the code states plainly that there is a correlation on the right.

MySQL and ClickHouse

• MySQL supports LATERAL from 8.0.14, with the same syntax: JOIN LATERAL (...) ON true. On older versions the top-N task is solved with variables or window functions (from 8.0).
• ClickHouse has no general LATERAL. Top-N per group is done with arrayJoin and functions like groupArraySorted, or with LIMIT BY:

-- ClickHouse: top-2 orders per user
SELECT user_id, id, amount
FROM orders
ORDER BY amount DESC
LIMIT 2 BY user_id;

LIMIT N BY is a compact stand-in for the top-N-per-group that PostgreSQL assembles from LATERAL. Keep the link in mind: a correlated subquery in FROM is exactly LATERAL, and its bread and butter is top-N and expanding set-returning functions.