PostgreSQL LATERAL JOIN for correlated subqueries

Need to reference a previous table in a subquery or get top-N per group efficiently.

LATERAL JOIN allows subqueries to reference preceding tables:

-- Top 3 orders per customer
SELECT c.name, o.id, o.total, o.created_at
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, total, created_at
  FROM orders
  WHERE customer_id = c.id  -- References c!
  ORDER BY total DESC
  LIMIT 3
) o;

-- Latest order per customer
SELECT c.name, latest.*
FROM customers c
LEFT JOIN LATERAL (
  SELECT id, total, created_at
  FROM orders
  WHERE customer_id = c.id
  ORDER BY created_at DESC
  LIMIT 1
) latest ON true;

-- Running calculations
SELECT d.date, d.revenue,
       stats.avg_7d, stats.total_7d
FROM daily_revenue d
CROSS JOIN LATERAL (
  SELECT AVG(revenue) as avg_7d,
         SUM(revenue) as total_7d
  FROM daily_revenue
  WHERE date BETWEEN d.date - 6 AND d.date
) stats;

-- Unnest with ordinality (expand arrays with position)
SELECT u.name, tag.value, tag.position
FROM users u
CROSS JOIN LATERAL unnest(u.tags) WITH ORDINALITY AS tag(value, position);

-- Call set-returning functions per row
SELECT c.name, g.point
FROM cities c
CROSS JOIN LATERAL generate_series(1, c.population / 1000) AS g(point);

LATERAL is like a for-each loop in SQL: for each row in the left table, run the subquery.

LATERAL JOIN solves the top-N-per-group problem efficiently (one index lookup per group) and enables correlated subqueries that regular JOINs can't express.

Advanced PostgreSQL queries needing per-row subqueries