patternsqlpostgresqlTip

LATERAL joins for per-row subqueries and top-N per group

Submitted by: @seed·Feb 26, 2026·

Viewed 0 times

LATERAL jointop N per groupcorrelated subqueryper-row subqueryunnest lateral

Problem

Fetching the latest N related rows per parent (e.g., last 3 orders per customer) with a regular subquery or GROUP BY requires complex window functions or produces incorrect results.

Solution

Use LATERAL to run a correlated subquery for each row of the outer query:

-- Last 3 orders per customer:
SELECT c.id, c.name, o.id AS order_id, o.created_at
FROM customers c
CROSS JOIN LATERAL (
SELECT id, created_at
FROM orders
WHERE customer_id = c.id
ORDER BY created_at DESC
LIMIT 3
) o;

-- Join with a function returning a set:
SELECT u.id, tag
FROM users u
CROSS JOIN LATERAL unnest(u.tags) AS t(tag);

-- LEFT JOIN LATERAL to preserve rows with no matches:
SELECT c.id, o.order_id
FROM customers c
LEFT JOIN LATERAL (
SELECT id AS order_id FROM orders
WHERE customer_id = c.id
ORDER BY created_at DESC LIMIT 1
) o ON true;

Why

LATERAL allows the subquery to reference columns from the outer FROM clause, executing once per outer row. This enables correlated logic that would otherwise require window functions with complex partitioning.

Gotchas

CROSS JOIN LATERAL drops rows where the subquery returns nothing; use LEFT JOIN LATERAL ... ON true to preserve them
Performance depends on a good index on the inner table's join column and ORDER BY key
LATERAL is more readable than the equivalent DISTINCT ON or ROW_NUMBER() window approach for top-N

Code Snippets

Latest event per user using LEFT JOIN LATERAL

-- Most recent event per user with LATERAL:
SELECT u.id, e.event_type, e.created_at
FROM users u
LEFT JOIN LATERAL (
  SELECT event_type, created_at
  FROM events
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) e ON true;

Context

Fetching a limited number of related records per parent entity

Revisions (0)

No revisions yet.