LATERAL Joins vs Correlated Subqueries — When to Choose What
LATERAL lets a subquery or set-returning function see columns from preceding FROM items. It shines for per-row Top-N and table functions. Compare it with correlated subqueries and with a window Top-N.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Top-3 requests per user
Prefer
Match the shape to the cardinality
Every customer in a large table: one ROW_NUMBER partition, then filter. A short list of users, or a set-returning function: LATERAL with LIMIT.
- LATERAL can stop after N rows for that outer key.
- A window ranks the population in one pass.
- Sketch both, then read EXPLAIN (ANALYZE). Do not guess the winner.
Alternative
A scalar subquery, or LATERAL with no LIMIT
A scalar correlated subquery cannot return three full rows. LATERAL without LIMIT cross-joins the user’s entire history.
- EXISTS is a semi-join. It does not return payload rows.
- JOIN LATERAL ON true drops users with no requests. LEFT JOIN keeps them.
- Nested-loop shape follows the outer cardinality. Watch that number.
Overview
LATERAL lets a subquery or set-returning function see columns from preceding FROM items. It shines for per-row Top-N and table functions. Compare it with correlated subqueries and with window Top-N.
SELECT u.id, r.*
FROM users u
JOIN LATERAL (
SELECT *
FROM requests r
WHERE r.user_id = u.id
ORDER BY r.created_at DESC
LIMIT 3
) r ON true;Without LATERAL, that subquery cannot reference u.id.
Flow
- 1
1 Take one outer row
- next2 Bind the outer keys
- 2
2 Bind the outer keys
- next3 Order, LIMIT, or call an SRF
- 3
3 Order, LIMIT, or call an SRF
- next4 Emit zero to N rows
- 4
4 Emit zero to N rows
- next5 Repeat for each outer row
- 5
5 Repeat for each outer row
Lesson map
LATERAL Joins vs Correlated Subqueries — When to Choose What
LATERAL lets a subquery or set-returning function see columns from preceding FROM items. It shines for per-row Top-N and table functions. Compare it with correlated subqueries and with a window Top-N.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB a["1 Take one outer row"] b["2 Bind the outer keys"] c["3 Order, LIMIT, or call an SRF"] d["4 Emit zero to N rows"] a -->|1 Take one outer row| b b -->|2 Bind the outer keys| c c -->|3 Order, LIMIT, or call an SRF| d
That repeat is why the plan is often a nested loop. Outer cardinality is the multiplier. How to read the node is EXPLAIN and EXPLAIN ANALYZE, not an access-method lecture.
Comparative matrix
| Shape | Returns | Best when |
|---|---|---|
| ROW_NUMBER plus filter | Full rows, one pass over groups | Uniform Top-N for every group |
| LATERAL plus LIMIT | 0..N rows per outer | Sparse outers, early stop, or a table function |
| Correlated scalar subquery | One value | A single column such as last_seen |
| EXISTS or IN | Boolean semi-join | Existence, not a payload |
SELECT u.id,
(SELECT MAX(r.created_at) FROM requests r WHERE r.user_id = u.id) AS last_seen
FROM users u;Rewrites for that single value: a LEFT JOIN to an aggregate, or a window. For Top-3 full rows, LATERAL or the ranking window wins over a scalar subquery.
JOIN LATERAL ... ON true is the usual spelling when the lateral subquery already filters. It behaves like CROSS JOIN LATERAL. LEFT JOIN LATERAL keeps outer rows that match nothing.
Set-returning functions
SELECT u.id, g.x
FROM users u
JOIN LATERAL unnest(u.tag_ids) AS g(x) ON true;unnest, jsonb_to_recordset, and generate_series keyed off outer columns are idiomatic LATERAL. For functions, Postgres often allows the correlation even when you omit the keyword. Write LATERAL anyway so the dependency is obvious. A window does not replace unnest.
When the window still wins
If you need Top-3 for every customer in a large table, one ROW_NUMBER partition can beat a nested-loop LATERAL over all customers. It can also lose, depending on how selective the outer set is. Sketch both. Then EXPLAIN (ANALYZE) and read it on EXPLAIN. The indexes series hub is Indexes, cardinality, and EXPLAIN. Stop at the plan shape.
Nearest-neighbor style, as a shape only:
SELECT p.id, n.*
FROM points p
JOIN LATERAL (
SELECT id, loc <-> p.loc AS dist
FROM points o
WHERE o.id <> p.id
ORDER BY o.loc <-> p.loc
LIMIT 5
) n ON true;Production geo wants the right extension and index. That index is not this lesson. The lesson is LATERAL plus LIMIT.
Pick in this order
- 1
One value
Scalar subquery or a grouped left join. - 2
Existence
EXISTS. Do not LATERAL a payload you will throw away. - 3
Top-N for every group
ROW_NUMBER on the ranking page. - 4
Top-N for a few outers, or an SRF
LATERAL with LIMIT. - 5
No LIMIT
Fanout. Fix the shape before you read a plan.
Sandboxes
The Python sketch is the mental model: for each outer id, sort that id’s rows and keep three. It is not a claim about the planner.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Deep dive · Plan words you are allowed to say
“Nested loop per outer row” and “one sort for the window” are enough. If the interviewer asks which scan or which index, move to EXPLAIN and EXPLAIN ANALYZE and the indexes hub. Do not invent access-method detail on this page.
Pitfalls
Latest timestamp per user. Top 3 orders for 20 users you already filtered. Top 3 orders for every customer. Assign scalar, LATERAL, or ROW_NUMBER, and say what you would look for in EXPLAIN.
Interview Q&A
What does LATERAL enable?
Answer
A FROM item, including a set-returning function, may see columns from items to its left. It is evaluated per outer row.
LATERAL vs a correlated subquery?
Answer
LATERAL returns a set: multiple columns and multiple rows. A scalar subquery returns one value.
Top-N per user: LATERAL or window?
Answer
Both can be correct. LATERAL plus a supporting access path fits a small outer set. A window fits ranking the whole population. See Ranking and Top-N.
What is JOIN LATERAL ... ON true?
Answer
The lateral subquery already applied the predicate, so the join condition is constant true. Same idea as CROSS JOIN LATERAL.
Why LEFT JOIN LATERAL?
Answer
It keeps outer rows that produced zero lateral rows. An inner join drops them.
What plan shape should you expect?
Answer
Often a nested loop. Watch outer cardinality. Read the actual plan on EXPLAIN.
Can a window replace unnest?
Answer
Not cleanly. Expanding an array or a JSON set is LATERAL’s home.
EXISTS vs LATERAL?
Answer
EXISTS is a boolean semi-join. LATERAL is for when you need the payload rows.
How portable is LATERAL?
Answer
Postgres and MySQL 8+ have it. SQL Server spells the idea CROSS APPLY / OUTER APPLY. Check the dialect.
What is the classic mistake?
Answer
Forgetting LIMIT inside the lateral subquery, so every outer row fans out to its full child set.