MySQL LATERAL Join: Run a Subquery for Each Row

A normal subquery in FROM (a derived table) cannot see the other tables of the same query. A LATERAL derived table can. It is run once for each row of the tables before it, like a loop. Support starts in MySQL 8.0.14.

query.sql

SQL

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    ordered_on DATE NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL
);

INSERT INTO customers (name, city) VALUES
    ('Maya Shah',    'Pune'),
    ('Rohan Verma',  'Delhi'),
    ('Sana Ali',     'Pune'),
    ('Tara Nair',    'Kochi'),
    ('Uday Singh',   'Delhi');

INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
    (1, '2026-05-02',  680.00, 'delivered'),
    (1, '2026-05-20', 1299.00, 'delivered'),
    (2, '2026-05-21',  145.00, 'shipped'),
    (3, '2026-06-01',  450.00, 'delivered'),
    (3, '2026-06-05',   80.00, 'cancelled'),
    (3, '2026-06-18',  999.00, 'shipped'),
    (2, '2026-06-20',  220.00, 'pending');

The latest order of each customer

query.sql

SQL

SELECT c.name, last_order.id AS order_id, last_order.ordered_on, last_order.total
FROM customers c
LEFT JOIN LATERAL (
    SELECT o.id, o.ordered_on, o.total
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.ordered_on DESC, o.id DESC
    LIMIT 1
) AS last_order ON TRUE
ORDER BY c.id;

Output

OUTPUT

+-------------+----------+------------+---------+
| name        | order_id | ordered_on | total   |
+-------------+----------+------------+---------+
| Maya Shah   |        2 | 2026-05-20 | 1299.00 |
| Rohan Verma |        7 | 2026-06-20 |  220.00 |
| Sana Ali    |        6 | 2026-06-18 |  999.00 |
| Tara Nair   |     NULL | NULL       |    NULL |
| Uday Singh  |     NULL | NULL       |    NULL |
+-------------+----------+------------+---------+
5 rows in set

For each customer, the subquery runs with that customer’s c.id and returns their most recent order. The ORDER BY ... LIMIT 1 inside the subquery is what makes it a “top 1 per customer”. LEFT JOIN ... ON TRUE keeps customers without orders (Tara and Uday). The reference to c.id inside the subquery is allowed because of LATERAL.

Top N per row

Change LIMIT 1 to LIMIT 2 for each customer’s two most recent orders:

query.sql

SQL

SELECT c.name, o2.id AS order_id, o2.ordered_on, o2.total
FROM customers c
JOIN LATERAL (
    SELECT o.id, o.ordered_on, o.total
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.ordered_on DESC, o.id DESC
    LIMIT 2
) AS o2 ON TRUE
ORDER BY c.id, o2.ordered_on DESC;

Output

OUTPUT

+-------------+----------+------------+---------+
| name        | order_id | ordered_on | total   |
+-------------+----------+------------+---------+
| Maya Shah   |        2 | 2026-05-20 | 1299.00 |
| Maya Shah   |        1 | 2026-05-02 |  680.00 |
| Rohan Verma |        7 | 2026-06-20 |  220.00 |
| Rohan Verma |        3 | 2026-05-21 |  145.00 |
| Sana Ali    |        6 | 2026-06-18 |  999.00 |
| Sana Ali    |        5 | 2026-06-05 |   80.00 |
+-------------+----------+------------+---------+
6 rows in set

Sana has three orders and shows only the latest two. Without LATERAL, “top N per group” needs a window function.

The window function version

query.sql

SQL

SELECT name, id AS order_id, ordered_on, total
FROM (
    SELECT c.name, c.id AS customer_id, o.id, o.ordered_on, o.total,
           ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY o.ordered_on DESC, o.id DESC) AS rn
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
) ranked
WHERE rn <= 2
ORDER BY customer_id, ordered_on DESC;

Output

OUTPUT

+-------------+----------+------------+---------+
| name        | order_id | ordered_on | total   |
+-------------+----------+------------+---------+
| Maya Shah   |        2 | 2026-05-20 | 1299.00 |
| Maya Shah   |        1 | 2026-05-02 |  680.00 |
| Rohan Verma |        7 | 2026-06-20 |  220.00 |
| Rohan Verma |        3 | 2026-05-21 |  145.00 |
| Sana Ali    |        6 | 2026-06-18 |  999.00 |
| Sana Ali    |        5 | 2026-06-05 |   80.00 |
+-------------+----------+------------+---------+
6 rows in set

Same result. Which is better?

	LATERAL	Window function
Reads like	A loop over the left rows	A ranking over all rows
Best when	An index on `(customer_id, ordered_on)` exists and you need few rows per group	You need the rank itself, or many groups have few rows
Cost	One index lookup per left row. Very fast with the right index	Processes all rows of the joined set, then filters
Version	8.0.14+	8.0.0+

With a large child table and a small parent list, LATERAL with an index on (customer_id, ordered_on) reads only a few rows per customer. A window function would rank every order row first.

Expressions that depend on the left row

LATERAL is also useful for calculating a value once and using it in several columns:

query.sql

SQL

SELECT c.name, stats.order_count, stats.spent, stats.average
FROM customers c
JOIN LATERAL (
    SELECT COUNT(*) AS order_count, SUM(o.total) AS spent, ROUND(AVG(o.total), 2) AS average
    FROM orders o
    WHERE o.customer_id = c.id AND o.status <> 'cancelled'
) AS stats ON stats.order_count > 0
ORDER BY c.id;

Output

OUTPUT

+-------------+-------------+---------+---------+
| name        | order_count | spent   | average |
+-------------+-------------+---------+---------+
| Maya Shah   |           2 | 1979.00 |  989.50 |
| Rohan Verma |           2 |  365.00 |  182.50 |
| Sana Ali    |           2 | 1449.00 |  724.50 |
+-------------+-------------+---------+---------+
3 rows in set

The aggregate subquery is evaluated per customer, and ON stats.order_count > 0 drops those without orders.

Rules

LATERAL works only on a derived table in FROM.
It must come after the tables it refers to (to the right of them in the FROM list).
You can use JOIN LATERAL, LEFT JOIN LATERAL (needs ON) or , LATERAL.
Older servers (before 8.0.14) and MariaDB give a syntax error. MariaDB does not support LATERAL at all.

lightbulb

The index decides

A LATERAL subquery is fast only if its WHERE and ORDER BY can use an index. Check with EXPLAIN.