Що таке LATERAL і коли він кращий за звичайний JOIN чи підзапит?

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

Задача: три останні замовлення кожного користувача.

SELECT u.id, u.email, o.id AS order_id, o.total, o.created_at
FROM users u
CROSS JOIN LATERAL (
    SELECT id, total, created_at
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) o;

LIMIT 3 усередині застосовується окремо для кожного користувача - звичайним JOIN чи GROUP BY такого не виразити.

LEFT JOIN LATERAL ... ON true - зберегти користувачів без замовлень:

SELECT u.id, last_order.total
FROM users u
LEFT JOIN LATERAL (
    SELECT total FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1
) last_order ON true;

Коли LATERAL доречний:

• топ-N у групі, особливо з індексом (user_id, created_at DESC): для кожного користувача - коротке сканування індексу, а не сортування всієї таблиці;
• функції, що повертають набори рядків з аргументами з іншої таблиці:

SELECT p.id, tag
FROM posts p, LATERAL unnest(p.tags) AS tag;

• кілька обчислених значень без повторення виразів у SELECT;
• розгортання JSON (jsonb_array_elements, jsonb_to_recordset) для кожного рядка.

Порівняння для «топ-N у групі»:

Ризик: LATERAL - це фактично вкладений цикл. На мільйоні зовнішніх рядків без індексу для внутрішнього запиту він виконає мільйон повних сканувань. Перевіряйте план через EXPLAIN ANALYZE.

У Laravel: joinLateral() і leftJoinLateral() у конструкторі запитів:

$latest = DB::table('orders')
    ->whereColumn('user_id', 'users.id')
    ->orderByDesc('created_at')
    ->limit(3);

User::query()->joinLateral($latest, 'recent_orders')->get();

Докладніше в документації: Табличні вирази: LATERAL