Lateral joins
LATERAL lets a subquery in FROM refer to columns of tables listed before it, like a per-row function.
In this page:
Syntax
SELECT columns
FROM table1 t1
CROSS JOIN LATERAL (
SELECT columns FROM table2 t2 WHERE t2.key = t1.key LIMIT n
) sub;Lateral joins
A LATERAL subquery runs once per row of the preceding table and can use that row's values, for example to fetch the latest three orders of each customer with ORDER BY ... LIMIT 3.
It is PostgreSQL's answer to top-N-per-group problems. JOIN LATERAL ... ON true keeps the syntax valid.
Note:
- LEFT JOIN LATERAL ...
- ON true keeps customers with no orders.
Example: Lateral joins
shop=# SELECT c.name, o.id AS order_id, o.total
shop-# FROM customers c
shop-# CROSS JOIN LATERAL (
shop(# SELECT id, total FROM orders WHERE customer_id = c.id ORDER BY total DESC LIMIT 2
shop(# ) o
shop-# ORDER BY c.id, o.total DESC;
name | order_id | total
------+----------+-------
Ada | 11 | 70
Ada | 10 | 50
Bob | 13 | 95
Bob | 12 | 20⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Forgetting LATERAL and getting an error about missing columns
- Using it on big tables without indexes
- Confusing it with a correlated subquery in WHERE
Chapter Summary
- LATERAL subqueries can see earlier tables
- Runs per outer row
- Great for top-N per group
- Use ON true with JOIN LATERAL
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: