Advanced SQL

Lateral Joins and APPLY

A lateral table expression is parameterized by columns from preceding FROM items, enabling per-row sets such as an index-friendly top-N; PostgreSQL spells this LATERAL, while SQL Server uses APPLY.

The right-hand expression can reference the current left row.
Inner and preserving forms differ on an empty right result.
Top-N per parent is a natural use.
Ordering must be total before limiting.
Lateral is not automatically faster than a window.

SELECT c.customer_id, recent.order_id, recent.placed_at
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT o.order_id, o.placed_at
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'PAID'
  ORDER BY o.placed_at DESC, o.order_id DESC
  LIMIT 3
) AS recent ON TRUE
ORDER BY c.customer_id, recent.placed_at DESC, recent.order_id DESC;

Dialect mapping
Intent	PostgreSQL	SQL Server
Drop parent when right side is empty	`CROSS JOIN LATERAL`	`CROSS APPLY`
Preserve parent when right side is empty	`LEFT JOIN LATERAL ... ON TRUE`	`OUTER APPLY`
Limit correlated rows	`ORDER BY ... LIMIT n`	`SELECT TOP (n) ... ORDER BY`

A customer with five paid orders contributes three recent rows; a customer with none contributes one null-extended row in the preserving form. That output grain differs from a scalar correlated subquery and must be expected by downstream aggregation.