LATERAL Joins

Correlated subqueries in FROM

A LATERAL subquery can refer to columns of tables earlier in FROM, so it runs logically once per outer row. It is the cleanest way to get the top N related rows per row (each customer's latest two orders) and to call set-returning functions per row. Use CROSS JOIN LATERAL to drop rows with no results or LEFT JOIN LATERAL ... ON true to keep them. An index on (customer_id, ordered_on) makes it fast.

Each customer's two most recent orders, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). John has no orders and is dropped by CROSS JOIN LATERAL; the others show their latest two orders of any status.

-- Each customer's two most recent orders
SELECT c.name, recent.id, recent.ordered_on, recent.amount
FROM customers c
CROSS JOIN LATERAL (
  SELECT o.id, o.ordered_on, o.amount FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.ordered_on DESC LIMIT 2
) AS recent
ORDER BY c.name, recent.ordered_on DESC;

Output:

 name  | id  | ordered_on | amount  
-------+-----+------------+---------
 Asha  | 106 | 2026-02-28 |  700.00
 Asha  | 102 | 2026-01-20 |  300.00
 Meera | 107 | 2026-03-01 |  150.00
 Meera | 105 | 2026-02-14 | 2000.00
 Ravi  | 104 | 2026-02-02 |  450.00
 Ravi  | 103 | 2026-01-21 |  800.00
(6 rows)

Compare with ROW_NUMBER

LATERAL with LIMIT can stop early per customer using an index, while ROW_NUMBER ranks all rows first.

Quick check: How do you keep outer rows with no LATERAL results?

• Use UNION
• CROSS JOIN LATERAL
• Add DISTINCT
• LEFT JOIN LATERAL (...) ON true