Краткий ответ

Такую корреляцию обеспечивает LATERAL. Он разрешает производной таблице обращаться к столбцам уже обработанных строк источника слева, поэтому подзапрос получает контекст текущей строки внешнего запроса.

Без LATERAL обычная производная таблица, как правило, не видит столбцы соседнего внешнего источника. Логически lateral-подзапрос может вернуть для каждой внешней строки ноль, одну или несколько строк.

Исторический контекст

Обычные производные таблицы появились как способ представить результат подзапроса в роли временного источника данных для внешнего запроса. Такой подзапрос рассматривался как самостоятельное выражение: его результат не зависел от конкретной строки другой таблицы.

Практические задачи часто требуют обратного поведения: например, найти несколько последних событий для каждого клиента. LATERAL расширяет модель производной таблицы и позволяет выразить зависимое, или коррелированное, табличное вычисление. В некоторых СУБД близкую возможность предоставляет оператор APPLY, но конкретный синтаксис зависит от диалекта SQL.

Постановка проблемы

Предположим, для каждого клиента нужно получить две последние операции. Если сначала соединить всех клиентов со всеми операциями, а затем ранжировать результат, запрос может обработать большой промежуточный набор данных.

Если попытаться сослаться из обычной производной таблицы на идентификатор текущего клиента, такой столбец обычно окажется вне области видимости. Неверное решение приводит либо к ошибке области видимости, либо к вычислению одного общего результата вместо отдельного результата для каждого клиента.

Подробное решение

LATERAL изменяет область видимости справа от него: подзапрос может использовать столбцы источников, расположенных слева в FROM. Логически он рассматривается для каждой строки левого источника, хотя оптимизатор может выбрать другой физический план, если это сохраняет результат.

WITH customers(id) AS (
    VALUES (1), (2)
), events(id, customer_id, created_at) AS (
    VALUES
        (101, 1, '2024-01-03'),
        (102, 1, '2024-01-02'),
        (103, 1, '2024-01-01'),
        (201, 2, '2024-01-04')
)
SELECT c.id, e.id AS event_id
FROM customers AS c
LEFT JOIN LATERAL (
    SELECT e.id
    FROM events AS e
    WHERE e.customer_id = c.id
    ORDER BY e.created_at DESC
    LIMIT 2
) AS e ON true;

Для каждого клиента подзапрос использует его id, сортирует только его операции и ограничивает результат двумя строками. LEFT JOIN LATERAL сохраняет клиента, даже если подзапрос не нашёл операций; при обычном CROSS JOIN LATERAL такой клиент исчез бы.

Важно отличать логическую возможность от гарантии производительности. При подходящем индексе, например по клиенту и дате операции, lateral-план может быстро находить верхние строки для каждого клиента. Но при большом числе внешних строк многократный запуск подзапроса может быть дороже одного set-based решения с оконной функцией.

Если lateral-подзапрос возвращает несколько строк, внешняя строка размножается соответственно. Если нужно ровно одно значение, скалярный коррелированный подзапрос имеет другое ограничение: он должен вернуть не более одной строки, иначе возникает ошибка.

Ситуация из практики

В API нужно показать каждого клиента и две последние операции. Вариант с оконной функцией сначала читает операции, нумерует их внутри групп клиентов, а затем фильтрует строки с номерами один и два. Его плюс — единая обработка набора; минус — при ограниченном числе нужных операций он может прочитать больше данных, чем требуется.

Вариант со скалярными подзапросами неудобен: для двух операций понадобятся отдельные выражения или дополнительная агрегация, а несколько строк напрямую вернуть нельзя. LATERAL естественно возвращает небольшой табличный результат для каждого клиента.

В данном случае выбран LEFT JOIN LATERAL с индексом по customer_id и created_at. Это сохраняет клиентов без операций и позволяет эффективно получать верхние строки; фактическую выгоду необходимо подтверждать планом выполнения и объёмами данных.

Что кандидаты часто упускают

1. Чем отличается LEFT JOIN LATERAL от CROSS JOIN LATERAL?

Ответ: CROSS JOIN LATERAL исключает левую строку, если зависимый подзапрос не вернул ни одной строки. LEFT JOIN LATERAL сохраняет её, подставляя NULL в столбцы правой части. Поэтому выбор определяется требуемой кардинальностью результата, а не только возможностью корреляции.

2. Обязан ли СУБД физически запускать lateral-подзапрос заново для каждой строки?

Ответ: нет. Это логическая семантика коррелированного табличного выражения, а не обязательный алгоритм исполнения. Оптимизатор может применить переписывание, использовать индекс, кэширование или другой эквивалентный план, если результат и видимые свойства запроса сохраняются.

3. Когда оконная функция предпочтительнее LATERAL?

Ответ: оконная функция часто лучше, когда нужно обработать значительную часть всей таблицы операций или получить ранжирование сразу для многих групп. Она позволяет выполнить операцию над набором данных единым планом. LATERAL особенно удобен при небольшом числе строк на внешнюю запись, точечном поиске и наличии подходящего индекса; при большом внешнем наборе множество зависимых обращений может стать узким местом.