Як працюють похідні таблиці й LATERAL у MySQL?

Похідна таблиця (derived table) - підзапит у FROM, результат якого використовується як тимчасова таблиця:

SELECT c.name, t.revenue
FROM categories c
JOIN (
    SELECT category_id, SUM(total) AS revenue
    FROM orders
    GROUP BY category_id
) t ON t.category_id = c.id;

Звичайна похідна таблиця не бачить таблиць зовнішнього запиту - вона обчислюється сама по собі.

LATERAL (MySQL 8.0.14+) дозволяє похідній таблиці посилатися на колонки таблиць, що стоять лівіше в FROM. Вона обчислюється для кожного рядка зовнішньої таблиці - як корельований підзапит, але може повертати кілька рядків і колонок.

Класична задача - «топ-3 замовлення кожного користувача»:

SELECT u.name, top.id, top.total
FROM users u
JOIN LATERAL (
    SELECT o.id, o.total
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY o.total DESC
    LIMIT 3
) AS top ON TRUE;

З індексом (user_id, total) кожен підзапит бере три рядки прямо з індексу.

Альтернатива - віконна функція:

SELECT * FROM (
    SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
    FROM orders o
) ranked
WHERE rn <= 3;

Вона нумерує всі замовлення, а потім відкидає зайві. На великій таблиці, коли користувачів у вибірці небагато, LATERAL з індексом зазвичай швидший; коли потрібні всі користувачі - віконна функція простіша.

Що варто знати:

• LEFT JOIN LATERAL ... ON TRUE - залишити користувачів без замовлень.
• Оптимізатор MySQL може «злити» просту похідну таблицю з зовнішнім запитом (derived merge) або матеріалізувати її. Поведінку видно в EXPLAIN (DERIVED).
• MySQL до 8.0.14 LATERAL не підтримує - там цю задачу розв'язували змінними чи корельованими підзапитами.

Докладніше в документації: LATERAL-похідні таблиці