Derived Tables and LATERAL

A derived table is a subquery in FROM, and it needs an alias (error 1248). It fixes the fan-out of Self and Multi-Table Joins: aggregate each child alone, then join. A lateral derived table (MySQL 8.0.14 524 +) may also see tables to its left, so it runs per outer row and, unlike a scalar subquery, returns several columns:

Pre-aggregated derived tables, and each customer's latest order with LATERALSQL

SELECT p.sku, s.units, r.reviews
FROM products AS p
LEFT JOIN (SELECT product_id, SUM(quantity) AS units
           FROM order_items GROUP BY product_id) AS s ON s.product_id = p.id
LEFT JOIN (SELECT product_id, COUNT(*) AS reviews
           FROM reviews GROUP BY product_id) AS r ON r.product_id = p.id
WHERE p.id = 1;
SELECT c.name, latest.id, latest.ordered_at
FROM customers AS c
JOIN LATERAL (SELECT o.id, o.ordered_at FROM orders AS o
              WHERE o.customer_id = c.id
              ORDER BY o.ordered_at DESC LIMIT 1) AS latest
WHERE c.country = 'MY';

Output

+-----------+-------+---------+
| sku       | units | reviews |
+-----------+-------+---------+
| BK-PHP-01 |     3 |       2 |
+-----------+-------+---------+
1 row in set (0.001 sec)
+--------------+----+---------------------+
| name         | id | ordered_at          |
+--------------+----+---------------------+
| Chen Wei     |  8 | 2026-08-20 18:20:00 |
| Farah Rahman |  7 | 2026-07-14 15:55:00 |
+--------------+----+---------------------+
2 rows in set (0.001 sec)

Product 1 shows the true 3 units and 2 reviews, not 6 and 4. Without LATERAL, the second query fails with error 1054 on c.id. LEFT JOIN LATERAL (...) ON TRUE keeps customers with no orders, and LIMIT 3 gives top-N per group, which Ranking Functions also solves with ROW_NUMBER(). The optimizer merges a simple derived table into the outer query and materializes a grouped one.

MENU

Derived Tables and LATERAL