LATERAL joins for per-row correlated subqueries

A LATERAL join reruns a mini-query once for each row in the main results, using that row's own values as input - so the answer shifts to fit each row instead of staying fixed for the whole table.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Per-row correlated subqueries with LATERAL

SELECT customers.full_name, sub.order_id, sub.order_date FROM customers, LATERAL (SELECT order_id, order_date FROM orders WHERE customer_id = customers.customer_id ORDER BY order_date DESC LIMIT 3) sub

Result:

full_name	order_id	order_date
Alex Park	O104	2026-04-01
Alex Park	O098	2026-03-12
Alex Park	O088	2026-02-22
Jordan Lee	O110	2026-04-05

Step by step

After SELECT, list the column names you want, separated by commas.
```
SELECT customers.full_name, sub.order_id
```

FROM tells SQL which table to pull the data from.

SELECT customers.full_name, sub.order_id
FROM customers, LATERAL (SELECT order_id FROM orders WHERE customer_id = customers.customer_id ORDER BY order_date DESC LIMIT 3) sub;

Practice

Try each one in your head first, then open the answer.

1. For each row in orders, fetch up to 3 matching customers rows on customer_id using a LATERAL subquery.

SELECT orders.*, sub.* FROM orders, LATERAL (SELECT * FROM customers WHERE customers.customer_id = orders.customer_id LIMIT 3) sub

2. For each row in orders, fetch up to 3 matching shipments rows on order_id using a LATERAL subquery.

SELECT orders.*, sub.* FROM orders, LATERAL (SELECT * FROM shipments WHERE shipments.order_id = orders.order_id LIMIT 3) sub

3. For each row in orders, fetch up to 3 matching returns rows on order_id using a LATERAL subquery.

SELECT orders.*, sub.* FROM orders, LATERAL (SELECT * FROM returns WHERE returns.order_id = orders.order_id LIMIT 3) sub

4. For each row in products, fetch up to 3 matching reviews rows on product_id using a LATERAL subquery.

SELECT products.*, sub.* FROM products, LATERAL (SELECT * FROM reviews WHERE reviews.product_id = products.product_id LIMIT 3) sub

5. For each row in products, fetch up to 3 matching inventory rows on product_id using a LATERAL subquery.

SELECT products.*, sub.* FROM products, LATERAL (SELECT * FROM inventory WHERE inventory.product_id = products.product_id LIMIT 3) sub

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed