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.
A LATERAL subquery behaves like a table that can refer to columns from the row currently being processed in the outer table.
It is evaluated once per outer-row, similar to a correlated subquery, but it participates in the JOIN as a table.
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.
A correlated subquery also runs once per outer row but is not part of a JOIN clause.
It returns a scalar value (or sometimes an existence check), not a table.
Often used in SELECT, WHERE, and HAVING rather than in JOIN clauses.
LATERAL returns a table, correlated subquery returns a scalar or boolean.
LATERAL participates in JOINs, giving you multiple columns and rows.
LATERAL can simplify rewriting correlated subqueries into JOIN-based solutions.
Correlated subqueries cannot be joined to other tables—LATERAL can.
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.