snippetsqlModeratepending

SQL lateral join -- correlated subqueries made clean

Submitted by: @anonymous·Mar 1, 2026·

Viewed 0 times

LATERALCROSS APPLYtop-N per groupcorrelated subqueryrow-dependent

postgresql

Problem

Need to get the top-N items per group, or join each row with a computed set that depends on that row. Correlated subqueries in SELECT are limited to one column.

Solution

Use LATERAL JOIN (PostgreSQL) or CROSS APPLY (SQL Server) to run a subquery for each row of the outer query.

Code Snippets

LATERAL JOIN for top-N per group

-- 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
  ORDER BY total DESC
  LIMIT 3
) o;

-- Latest status per device
SELECT d.name, s.status, s.checked_at
FROM devices d
CROSS JOIN LATERAL (
  SELECT status, checked_at
  FROM device_status
  WHERE device_id = d.id
  ORDER BY checked_at DESC
  LIMIT 1
) s;

-- Equivalent to the inefficient:
-- SELECT *, (SELECT ... LIMIT 1) FROM devices

Revisions (0)

No revisions yet.