В стандартном SQL подзапросы в FROM не могут ссылаться на колонки таблиц, которые упомянуты раньше в том же FROM. Каждый источник в FROM — самостоятельная сущность, СУБД может вычислить его независимо.

Это удобно для оптимизатора, но иногда мешает. Хочется написать: «для каждого клиента — top-3 его заказа по сумме». В стандартном SQL это решается через window-функции или коррелированные подзапросы в SELECT. PostgreSQL же даёт чистый и читаемый инструмент: LATERAL.

Базовый случай: подзапрос видит соседа

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

Логически это эквивалентно вложенному циклу: «для каждой строки customers c — выполни подзапрос с подстановкой c.id, и склей результат». Похоже на LEFT JOIN с коррелированным подзапросом, но синтаксис чище и оптимизатор лучше его обрабатывает.

Без LATERAL подзапрос отверг бы ссылку на c.id — это другая ветка FROM. С LATERAL PostgreSQL для каждой строки customers выполняет подзапрос отдельно, ограничивая результат двумя последними заказами.

Обрати внимание на LEFT JOIN LATERAL (...) recent ON TRUE. Условие ON TRUE — обычный приём: предикат «связи» уже внутри подзапроса (WHERE o.customer_id = c.id), а ON TRUE просто склеивает результат. LEFT JOIN нужен для того, чтобы клиенты без заказов всё равно остались в выводе.

Top-N per group — основное применение

Идиома «top-N по группам» — самая частая причина использовать LATERAL. До window-функций (модуль 8) и без LATERAL она решалась громоздкими подзапросами с ROW_NUMBER(). С LATERAL — две строки.

Альтернатива через window-функции:

SELECT * FROM (
  SELECT c.full_name, o.id, o.placed_at,
         ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY o.placed_at DESC) AS rn
  FROM customers c JOIN orders o ON o.customer_id = c.id
) WHERE rn <= 2;

Window-функции мы пройдём в модуле 8. Для маленьких N (top-1, top-3) LATERAL обычно эффективнее: он останавливается после LIMIT N, а window-функция считает ранг для всех строк.

LATERAL как самый дешёвый «параметризованный JOIN»

LATERAL полезен не только для top-N. Любой случай, когда «правая» сторона JOIN должна вычисляться в зависимости от левой — кандидат на LATERAL.

Запрос двухуровневый, но логика прозрачная: «для каждого клиента возьми 3 последних заказа, для каждого посчитай его сумму, потом просуммируй». Без LATERAL это не уместилось бы в один запрос так компактно.

Распаковка массивов и JSON

Второе классическое применение LATERAL — разворачивание массивов и JSON-полей. PostgreSQL имеет функции unnest, jsonb_array_elements, jsonb_each — они возвращают множество строк на каждый входной аргумент. С LATERAL их удобно «привязать» к таблице слева.

Здесь unnest(c.tags) — set-returning function, она для каждой c.tags возвращает столько строк, сколько элементов в массиве. LATERAL разрешает ссылку на c.tags. Аня с тегами ['vip', 'early_adopter'] превратится в 2 строки результата.

Аналогично с JSON:

JSONB и его операторы — отдельный модуль (9), но идиома LEFT JOIN LATERAL jsonb_each(...) стоит запомнить уже сейчас.

Стандарт vs PostgreSQL

LATERAL появился в стандарте SQL:1999, но широко используется именно в PostgreSQL и Oracle. MySQL имеет LATERAL с версии 8.0.14, но он используется реже из-за плохой оптимизации. SQL Server долгое время предлагал свой синтаксис CROSS APPLY / OUTER APPLY — это семантически то же самое, что INNER JOIN LATERAL / LEFT JOIN LATERAL.

Если ты пишешь PostgreSQL-specific код — пользуйся LATERAL смело. Это один из лучших инструментов языка.

Когда НЕ использовать LATERAL

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

Простое equi-join’ивание лучше делать обычным JOIN ON. LATERAL нужен там, где подзапрос зависит от строки слева чем-то большим, чем простое равенство — например, через ORDER BY + LIMIT, через LIMIT N на коррелированном условии, или через set-returning function.

Чек-лист

• LATERAL разрешает подзапросу в FROM ссылаться на таблицы слева.
• Синтаксис: LEFT JOIN LATERAL (subquery) alias ON TRUE — условие связи внутри подзапроса.
• Главное применение: top-N per group, особенно для малых N.
• Второе: разворачивание массивов и JSON через unnest / jsonb_each / jsonb_array_elements.
• Под капотом — nested loop: эффективен, если внутренний подзапрос быстрый и использует индекс.
• Эквивалент в SQL Server — CROSS APPLY / OUTER APPLY.