MySQL Lateral Derived Tables for Complex Subqueries

This article explains how to use lateral derived tables in MySQL to execute advanced, dependency-based subqueries. Introduced in MySQL 8.0, the LATERAL keyword allows a derived table to reference columns from preceding tables in the same FROM clause. You will learn the core syntax of lateral joins, see how they solve common SQL limitations like “top N per group” problems, and understand how to write more efficient and readable queries.

Understanding Lateral Derived Tables

In standard SQL, a derived table (a subquery in the FROM clause) is evaluated independently. It cannot reference columns from other tables listed in the same FROM clause. If you attempt to do so, MySQL will throw an error stating that the column is unknown.

The LATERAL keyword changes this behavior. By placing LATERAL before a subquery, you convert it into a lateral derived table. This allows the subquery to reference columns of preceding tables, acting similarly to a correlated subquery but behaving like a join. MySQL evaluates the lateral subquery for each row of the outer table.

Basic Syntax

The syntax for a lateral derived table requires placing the LATERAL keyword immediately before the subquery:

SELECT *
FROM table1 t1,
LATERAL (
    SELECT * 
    FROM table2 t2 
    WHERE t2.foreign_id = t1.id
) AS derived_table;

You can also use explicit join syntax, which is the recommended practice for readability:

SELECT *
FROM table1 t1
LEFT JOIN LATERAL (
    SELECT * 
    FROM table2 t2 
    WHERE t2.foreign_id = t1.id
) AS derived_table ON TRUE;

Practical Example: Finding the Top N Rows per Group

A classic use case for lateral derived tables is retrieving the top \(N\) records for each category. Suppose you have a categories table and a products table, and you want to find the two cheapest products in each category.

Using a lateral join, you can write:

SELECT 
    c.category_name, 
    p.product_name, 
    p.price
FROM categories c
INNER JOIN LATERAL (
    SELECT product_name, price
    FROM products
    WHERE category_id = c.id
    ORDER BY price ASC
    LIMIT 2
) AS p ON TRUE;

How This Works:

  1. MySQL iterates through each row in the categories table (c).
  2. For every category, the LATERAL subquery executes, substituting c.id into its WHERE clause.
  3. The subquery sorts the matching products by price and limits the result to the top 2.
  4. The results are merged into the final output.

Practical Example: Reusing Calculated Columns

Another major benefit of lateral derived tables is the ability to reuse calculated values in the same SELECT block without repeating complex expressions.

Imagine calculating a discounted price and then calculating a tax based on that discounted price:

SELECT 
    o.id AS order_id,
    o.original_price,
    calc.discounted_price,
    calc.discounted_price * 0.15 AS sales_tax
FROM orders o
INNER JOIN LATERAL (
    SELECT o.original_price * (1 - o.discount_rate) AS discounted_price
) AS calc;

Without LATERAL, you would have to duplicate the expression o.original_price * (1 - o.discount_rate) to calculate the sales tax, or wrap the entire query in an outer select.

Key Advantages of Lateral Joins