# LATERAL Joins — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/c-lateral

> A subquery per row.

## 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.

```sql
-- 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.

**Quiz:** How do you keep outer rows with no LATERAL results?

- [ ] Use UNION
- [ ] CROSS JOIN LATERAL
- [ ] Add DISTINCT
- [x] LEFT JOIN LATERAL (...) ON true

*Answer:* LEFT JOIN LATERAL (...) ON true. Like any left join.