What’s the difference between lateral derived tables and correlated subqueries in JOIN contexts?
Lateral Derived Tables vs Correlated Subqueries in JOINs
Both lateral derived tables and correlated subqueries allow referencing columns from an outer query, but they work differently inside JOINs.
- 1
A LATERAL subquery behaves like a table that can refer to columns from the row currently being processed in the outer table.
- 2
It is evaluated once per outer-row, similar to a correlated subquery, but it participates in the JOIN as a table.
- 3
MySQL implements this behavior using the keyword LATERAL (supported since MySQL 8.0.14).
Here, the LATERAL subquery is treated as a derived table that depends on each row of customers.
- 1
A correlated subquery also runs once per outer row but is not part of a JOIN clause.
- 2
It returns a scalar value (or sometimes an existence check), not a table.
- 3
Often used in SELECT, WHERE, and HAVING rather than in JOIN clauses.
- 1
LATERAL returns a table, correlated subquery returns a scalar or boolean.
- 2
LATERAL participates in JOINs, giving you multiple columns and rows.
- 3
LATERAL can simplify rewriting correlated subqueries into JOIN-based solutions.
- 4
Correlated subqueries cannot be joined to other tables—LATERAL can.
- 5
LATERAL often improves readability when joining to a result that depends on the outer row.
In JOIN-heavy queries, LATERAL is more flexible because it behaves like a dependent table source rather than an expression.