MySQL's LATERAL Keyword: Understanding Correlated Derived Tables

If you've ever needed to fetch a parent record alongside multiple aggregate metrics from a child table — say, the largest deal for each salesperson along with the customer name and date — you've likely encountered one of MySQL's more frustrating limitations. Before version 8.0.14, a derived table in the FROM clause could not reference columns from other tables in the same query. The workaround was ugly: multiple correlated subqueries in the SELECT list, each re-traversing the same index, or massive pre-aggregated temporary tables that exhausted memory buffers and spilled to disk.

MySQL 8.0.14 introduced the LATERAL keyword, a feature formalized in the SQL:1999 standard that had been available in PostgreSQL since version 9.3 and in SQL Server under the name APPLY for years before that.¹ The keyword fundamentally changes what derived tables can do by allowing them to reference columns from tables that appear earlier in the FROM clause. This article examines how LATERAL works, when it outperforms alternatives, and where it can cause problems.

Why Derived Tables Were Limited

To understand LATERAL, we need to understand what it fixed. In MySQL 5.6 and 5.7, derived tables — subqueries in the FROM clause — were evaluated as independent, static sets.¹ The optimizer materialized them once and their contents remained fixed for the duration of the query. If you tried to reference an outer column inside a derived table, the parser rejected the query with ERROR 1054: Unknown column.

This isolation forced developers into patterns that wasted resources. The most common was repeating correlated subqueries in the SELECT list:

SELECT
  s.name,
  (SELECT MAX(amount) FROM deals WHERE salesperson_id = s.id) AS max_deal,
  (SELECT customer_name FROM deals WHERE salesperson_id = s.id
   ORDER BY amount DESC LIMIT 1) AS top_customer,
  (SELECT deal_date FROM deals WHERE salesperson_id = s.id
   ORDER BY amount DESC LIMIT 1) AS top_deal_date
FROM salespeople s;

Each correlated subquery performs its own index lookup on the deals table. For three metrics, that's three separate index traversals per salesperson. With ten metrics, it's ten. The redundancy compounds as the number of columns grows.

The alternative — building a massive derived table with all possible aggregates and joining it back — often performed even worse. The derived table would scan the entire child table, exhaust the tmp_table_size and max_heap_table_size memory buffers, and spill temporary results to disk.²

How LATERAL Works

The LATERAL keyword prefixes a derived table in the FROM clause and grants it the ability to reference columns from tables to its left in the join order.¹ Conceptually, this introduces a "for-each" loop into the data retrieval layer: for each row from the driving table, the lateral derived table is evaluated with that row's column values bound as parameters.

Here's the same query rewritten with LATERAL:

SELECT
  s.name,
  ld.max_deal,
  ld.top_customer,
  ld.top_deal_date
FROM salespeople s
JOIN LATERAL (
  SELECT
    MAX(amount) AS max_deal,
    customer_name AS top_customer,
    deal_date AS top_deal_date
  FROM deals
  WHERE salesperson_id = s.id
  ORDER BY amount DESC
  LIMIT 1
) AS ld ON TRUE;

The derived table references s.id from the outer query — something that would have been impossible without LATERAL. The subquery executes once per salesperson row, but retrieves all three columns in a single index traversal. For queries returning ten or more columns from child tables, the difference in logical reads is substantial.

Join Type Restrictions

The left-to-right dependency model imposes constraints on which join types are valid. If a lateral derived table references the left operand, the join must be an INNER JOIN, CROSS JOIN, or LEFT OUTER JOIN. A RIGHT OUTER JOIN is prohibited because the right operand cannot produce rows when the left operand yields no matching row — which contradicts the semantics of a right join.¹

Logically enough, the inverse applies when the lateral table is the left operand referencing the right operand: only INNER JOIN, CROSS JOIN, or RIGHT OUTER JOIN are permitted. Since MySQL does not support FULL OUTER JOIN natively, lateral full outer joins require emulation with UNION ALL and anti-join filtering.

Lateral Position	Allowed Join Types	Prohibited
Right operand (references left)	INNER JOIN, CROSS JOIN, LEFT JOIN	RIGHT JOIN, FULL OUTER JOIN
Left operand (references right)	INNER JOIN, CROSS JOIN, RIGHT JOIN	LEFT JOIN, FULL OUTER JOIN

Execution Mechanics: The Iterator Model and Rematerialization

The performance characteristics of LATERAL are tied to MySQL's internal execution model — specifically the Volcano-style iterator engine introduced during the MySQL 8.0 refactoring. In this model, each query operation (scan, join, sort) is implemented as an independent iterator that pulls rows from its child iterators on demand. Traditional derived tables use one of two strategies within this framework: merging (folding the subquery into the outer query block) or materialization (executing once and caching results in a temporary table).² A lateral derived table cannot use either approach directly because its contents change with each outer row.

Instead, MySQL uses rematerialization. The engine provisions an internal temporary table structure, then for every row fetched from the driving table, it injects the correlated column values into the lateral subquery's execution plan, executes the subquery, and overwrites the contents of the temporary table with the newly generated results.³ This cycle repeats until the driving table is fully exhausted.

The EXPLAIN FORMAT=TREE output, introduced in MySQL 8.0.16, makes this behavior visible within the hierarchical iterator tree. The lateral join node appears with 'Invalidate materialized tables' and 'Materialize (invalidate on row from D)' annotations, indicating that the derived table is rematerialized for each outer row. In the traditional EXPLAIN output (non-TREE format), the Extra column shows Rematerialize.³ This marker is a critical diagnostic indicator — it confirms that the subtree is being repeatedly executed for every outer loop iteration. The tree output also reveals whether the lateral operation uses eq_ref (unique index lookup) or ref (non-unique index lookup) during its rematerialization phase, which dictates the ultimate per-iteration latency.

Aspect	Traditional Derived Table	Lateral Derived Table
Execution frequency	Once per query	Once per driving table row
Dependency	Independent	Binds to preceding table columns
Storage	Static temporary table	Dynamic temporary table (overwritten per iteration)
EXPLAIN marker	Materialize	Rematerialize

Optimizer Hints and Pushdown

The MERGE and NO_MERGE hints, which instruct the optimizer whether to fold a derived table into the outer query block, have no effect for lateral derived tables. Because the outer parameter references make query block merging mathematically impossible without violating relational algebra, the optimizer defaults strictly to rematerialization.²

Derived condition pushdown (DERIVED_CONDITION_PUSHDOWN) has limited effectiveness with lateral joins. The lateral binding syntax itself acts as a de facto pushdown of join conditions, leaving little room for the optimizer to push additional external constraints into the lateral block — especially when the block contains aggregates or LIMIT clauses.⁴ Furthermore, if the lateral block contains a LIMIT clause, the optimizer may reject condition pushdown entirely: applying the filter before or after the limit would fundamentally alter result set semantics.

The STRAIGHT_JOIN modifier interacts with lateral joins in a particular way. Because a lateral join mathematically forces a specific execution order — the driving table must be evaluated first — an INNER JOIN LATERAL already enforces order similar to STRAIGHT_JOIN. However, applying STRAIGHT_JOIN globally to a complex query containing both standard and lateral joins disables critical semijoin transformations (such as FirstMatch, LooseScan, and DuplicateWeedout) for the non-lateral portions, potentially degrading their performance.¹ Administrators are cautioned against using STRAIGHT_JOIN as a blanket directive when lateral joins are present in the same statement.

Core Use Cases: Row-Specific Aggregations

Perhaps the most common use case is retrieving a parent record alongside multiple derived metrics from a child table. Before LATERAL, this required either repeated correlated subqueries or massive pre-aggregated temporary tables.

LATERAL enables chaining: one lateral table can reference another lateral table declared earlier in the same FROM clause.⁵ For example, a first lateral table calculates the maximum deal amount for a salesperson, and a second lateral table uses that result to locate the specific customer:

SELECT
  s.name,
  ld.max_amount,
  ld2.customer_name
FROM salespeople s
JOIN LATERAL (
  SELECT MAX(amount) AS max_amount
  FROM deals
  WHERE salesperson_id = s.id
) AS ld ON TRUE
JOIN LATERAL (
  SELECT customer_name
  FROM deals
  WHERE salesperson_id = s.id AND amount = ld.max_amount
  LIMIT 1
) AS ld2 ON TRUE;

This eliminates redundant index scans by passing the first lateral table's output into the second.

Top-N Per Group

Fetching the N most recent records per category — such as the 5 latest comments for each active user — was historically one of MySQL's more frustrating patterns. The traditional approaches relied on brittle user-defined variables or self-joining Cartesian products that scaled poorly.⁶

LATERAL solves this by scoping an ORDER BY ... LIMIT N to each parent row:

SELECT u.username, c.comment_text, c.created_at
FROM users u
JOIN LATERAL (
  SELECT comment_text, created_at
  FROM comments
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 5
) AS c ON TRUE
WHERE u.is_active = TRUE;

When combined with JSON_ARRAYAGG and JSON_OBJECT (available since MySQL 5.7.22), this pattern generates document-style API payloads directly from the relational engine — the lateral table retrieves the top N rows, and the outer query serializes them into a JSON array in a single column.⁶

If the child table has a composite index on the foreign key and the sort column, the storage engine performs an index range scan and aborts immediately once the limit is reached. This reads only the exact number of rows required, drastically reducing logical reads compared to a full-table rank operation.

Dynamic Function Application

For geographic calculations, recommendation engines, or mathematical modeling, queries often need to apply a CPU-intensive function — such as the Haversine distance formula — to each row and return the best match from a secondary dataset. A classic example is finding the nearest landmark to each user's coordinates.

A standard CROSS JOIN forces the engine to calculate the distance function between every user and every landmark — an O(M * N) Cartesian product — before sorting and filtering. A LATERAL join inverts this: it binds the user's coordinates to the subquery, calculates distances only for nearby landmarks (potentially using spatial bounding boxes pushed into the lateral query), sorts the localized subset, and limits to one result.⁷ This transforms the operation from a quadratic O(M * N) Cartesian product into a sequence of O(M * log N) lookups.

Performance: LATERAL vs. Alternatives

The performance differential between LATERAL, window functions, and correlated subqueries depends on query selectivity, data volume, and index availability.

LATERAL vs. Window Functions

MySQL 8.0 introduced window functions (ROW_NUMBER(), RANK(), LEAD(), LAG()) alongside lateral joins, giving developers multiple tools for complex analysis.⁸ The choice between them comes down to how much data you need.

LATERAL excels with high selectivity. When the outer query targets a small subset of rows — say, the latest transaction for 1,000 specific users out of a 10-million-row table — a lateral join performs 1,000 rapid index lookups. The window function equivalent must scan, partition, and rank the entire 10 million rows before the outer filter can narrow the result set. In empirical comparisons on a 1-million-row dataset with a limit of 1,000 rows, lateral queries completed in approximately 4 milliseconds versus roughly 17 seconds for the window function approach.⁹

Window functions excel with full-table analysis. When the query operates on the entire dataset — global leaderboards, percentile ranks, moving averages across all products — a window function performs a single sequential scan. A lateral join would force millions of rematerialization cycles, creating a severe CPU bottleneck. TPC-H benchmark variations demonstrate this dichotomy vividly. In variations of TPC-H Q2, which evaluates minimum supply costs across global regions, window function variants proved up to 40% faster, with the lateral approach incurring approximately 20% overhead from the sheer volume of rematerialization cycles.¹⁰

LATERAL vs. Correlated Subqueries

A lateral derived table and a correlated subquery appear similar on the surface — both execute a nested query parameterized by the outer row. The mechanical difference is significant.

A traditional correlated subquery returns a single scalar value: one column, one row.¹ Retrieving three columns requires three separate subqueries and three separate index lookups per row. A LATERAL derived table returns multiple columns and rows, retrieving all needed data in a single index traversal per outer row.⁷

For simple existence checks (EXISTS), a correlated subquery may execute with slightly less iterator overhead. For multi-column data extraction, the lateral join is substantially more efficient.

Feature	LATERAL Derived Table	Correlated Subquery	Window Function
Output	Multiple rows, multiple columns	Single row, single column	One scalar per row
Placement	FROM / JOIN clause	SELECT / WHERE / HAVING	SELECT (with OVER)
Execution	Rematerialized loop per outer row	Evaluated per outer row	Single scan and sort
Best for	Top-N per group, multi-column extraction	Existence checks, simple metrics	Full-table ranking, percentiles

Diagnosing Performance with EXPLAIN ANALYZE

The EXPLAIN ANALYZE command (available since MySQL 8.0.18) is essential for profiling lateral joins. Unlike traditional EXPLAIN, which provides static estimates, EXPLAIN ANALYZE executes the query and measures actual time, row counts, and loop iterations within each iterator.¹¹

The most critical metric is the loops parameter on the inner rematerialization node. This value exactly matches the actual row count of the outer driving table scan.¹¹ If a missing index causes the outer query to scan 1,000,000 rows instead of 10, the output will show the lateral subquery executing 1,000,000 loops. This direct correlation makes EXPLAIN ANALYZE the primary tool for identifying catastrophic missing indexes on the driving table.

In MySQL 8.3 and later, a new JSON format for EXPLAIN ANALYZE (explain_json_format_version=2) maps directly to the tree format, enabling automated programmatic analysis of iterator execution timing for lateral joins.¹²

Cross-Platform Equivalencies

For teams working across database platforms or migrating legacy systems, understanding the equivalents is useful. As noted in the outset, this isn't exactly a new feature for the RDBMS landscape as a whole.

SQL Server uses CROSS APPLY (equivalent to INNER JOIN LATERAL) and OUTER APPLY (equivalent to LEFT JOIN LATERAL). The semantics are functionally identical.¹³ If the right-hand table-valued function in a CROSS APPLY yields no rows, the outer row is excluded.

PostgreSQL supports the LATERAL keyword with nearly identical syntax, having introduced it in version 9.3. PostgreSQL extends the concept further by automatically treating set-returning functions (SRFs) like unnest() and generate_series() as implicit lateral expressions.¹⁴ MySQL lacks a direct equivalent to generate_series(), but JSON_TABLE() serves a similar implicit lateral role for JSON data.

When migrating from MySQL 5.6 or 5.7 to 8.0+, auditing the codebase for heavy correlated scalar subqueries and user-defined variable patterns (@row_num) used to simulate windowing is worthwhile. These legacy anti-patterns can be refactored into clean LATERAL joins, typically yielding more readable execution plans, reduced memory footprints, and better index utilization.

Conclusion

• The LATERAL keyword, available since MySQL 8.0.14, lets derived tables reference columns from preceding tables in the FROM clause, enabling patterns previously requiring workarounds.
• Internally, MySQL rematerializes the lateral derived table for each row from the driving table — EXPLAIN FORMAT=TREE shows 'Invalidate materialized tables' and 'Materialize (invalidate on row from D)' annotations, while traditional EXPLAIN output shows Rematerialize.
• LATERAL significantly outperforms window functions for highly selective queries (small subset of rows) but underperforms for full-table analysis.
• Always profile with EXPLAIN ANALYZE to verify that the driving table uses appropriate indexes, since the rematerialization loop count directly tracks the outer table's row count.
• Monitor for optimizer regressions across MySQL version upgrades, as lateral joins are sensitive to changes in the iterator and caching architecture.

Ready to boost your MySQL performance? Contact us for RDBMS Performance Consulting to optimize your database infrastructure.

Footnotes:

1. MySQL 8.4 Reference Manual :: 15.2.15.9 Lateral Derived Tables, https://dev.mysql.com/doc/refman/8.2/en/lateral-derived-tables.html ↩
2. MySQL 8.4 Reference Manual :: 10.2.2.4 Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization, https://dev.mysql.com/doc/refman/8.4/en/derived-table-optimization.html ↩
3. MySQL 8.4 Reference Manual :: 10.8.2 EXPLAIN Output Format, https://dev.mysql.com/doc/en/explain-output.html ↩
4. MySQL 8.4 Reference Manual :: 10.2.2.5 Derived Condition Pushdown Optimization, https://dev.mysql.com/doc/refman/8.3/en/derived-condition-pushdown-optimization.html ↩
5. Using LATERAL Derived Tables To Gather Row-Specific Aggregations In MySQL 8.0.14, https://www.bennadel.com/blog/4222-using-lateral-derived-tables-to-gather-row-specific-aggregations-in-mysql-8-0-14.htm ↩
6. Using LATERAL Joins To Get "Top N" Records From Each Group In MySQL 8.0.14, https://www.bennadel.com/blog/4362-using-lateral-joins-to-get-top-n-records-from-each-group-in-mysql-8-0-14.htm ↩
7. A Deep Dive into SQL LATERAL JOIN | by Gregory Kovalchuk – Medium, https://medium.com/@goldengrisha/a-deep-dive-into-sql-lateral-join-7b09fcb3b745 ↩
8. MySQL 8.4 Reference Manual :: 14.20.1 Window Function Descriptions, https://dev.mysql.com/doc/refman/8.2/en/window-function-descriptions.html ↩
9. Why is a LATERAL JOIN faster than a correlated subquery in Postgres? – Stack Overflow, https://stackoverflow.com/questions/78017083/why-is-a-lateral-join-faster-than-a-correlated-subquery-in-postgres ↩
10. Use Window Functions to Speed Up Correlated Subqueries – Øystein on MySQL Optimizer, http://oysteing.blogspot.com/2021/06/use-window-functions-to-speed-up.html ↩
11. MySQL EXPLAIN ANALYZE, https://dev.mysql.com/blog-archive/mysql-explain-analyze/ ↩
12. New JSON format for EXPLAIN – MySQL :: Developer Zone, https://dev.mysql.com/blog-archive/new-json-format-for-explain/ ↩
13. LATERAL as an Advanced SQL Feature | by Yasin Sari – Medium, https://medium.com/@yasinsari81/lateral-as-an-advanced-sql-feature-3a5c22587ed2 ↩
14. What is the difference between LATERAL JOIN and a subquery in PostgreSQL?, https://stackoverflow.com/questions/28550679/what-is-the-difference-between-lateral-join-and-a-subquery-in-postgresql ↩