âš¡IntermediateJoins
LATERAL JOIN
Use LATERAL to reference earlier FROM items in subqueries for row-by-row computation.
Overview
LATERAL allows a subquery (or function call) in the FROM clause to reference columns from preceding FROM items like a correlated subquery but in the FROM clause. This enables "for each row in the left table, compute this subquery" patterns that would be impossible or awkward otherwise.
Common uses: "top N per group" queries, calling set-returning functions per row, and complex per-row calculations.
Key Points
- •FROM t, LATERAL (SELECT ... WHERE ... = t.col) AS sub
- •LATERAL can reference columns from preceding FROM items
- •LEFT JOIN LATERAL: keeps rows from left even if lateral returns nothing
- •Often used for "top N per group" without window functions
- •Implicit LATERAL: FROM t, UNNEST(t.array_col) AS element
Examples
LATERAL for top-N per group
-- Top 2 orders per customer
SELECT c.name, o.id, o.total
FROM customers c
CROSS JOIN LATERAL (
SELECT id, total FROM orders
WHERE customer_id = c.id
ORDER BY total DESC
LIMIT 2
) o;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4