re:re:actable
Урок 093 минуты чтения

Что такое LATERAL JOIN

На этой странице
Кратко

LATERAL позволяет подзапросу в FROM ссылаться на столбцы таблиц слева — он выполняется (логически) для каждой строки левой части, как цикл for each. Обычный подзапрос в FROM так не может: он вычисляется независимо. Главные применения: top-N per group («3 последних заказа каждого пользователя» с ORDER BY … LIMIT внутри), вызов set-returning функций с аргументами из строки (jsonb_array_elements, unnest), вынесение повторяющихся вычислений. LEFT JOIN LATERAL … ON true сохраняет левые строки, для которых подзапрос ничего не вернул. С индексом по (user_id, created_at) такой запрос очень эффективен.

Вопрос

Что такое LATERAL JOIN и когда он полезен?

Top-N per group

-- 3 последних заказа каждого активного пользователя
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
WHERE u.is_active;
CREATE INDEX orders_user_created_idx ON orders (user_id, created_at DESC);

Для каждого пользователя база читает три первые записи индекса — это быстрее, чем ROW_NUMBER() по всей таблице заказов, если пользователей немного или нужна только часть.

Сохранить строки без результата

-- последний заказ, пользователи без заказов тоже попадут
SELECT u.id, last.total, last.created_at
FROM users u
LEFT JOIN LATERAL (
  SELECT total, created_at FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) last ON true;

Функции, возвращающие наборы

-- развернуть массив JSON у каждой строки
SELECT o.id, item->>'sku' AS sku, (item->>'qty')::int AS qty
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.items) AS item;

Для функций в FROM слово LATERAL можно не писать — PostgreSQL добавляет его неявно.

Повторно используемые вычисления

SELECT p.id, calc.net, calc.net * 0.2 AS vat
FROM products p
CROSS JOIN LATERAL (SELECT p.price - p.discount AS net) calc
WHERE calc.net > 1000;

Так алиас становится доступен в WHERE.

LATERAL или оконные функции

ЗадачаВыбор
Top-N для части групп, есть индексLATERAL + LIMIT
Ранжирование всех строк таблицыROW_NUMBER()
Только top-1 в PostgreSQLDISTINCT ON

Подробнее — top N per group.

Частые ошибки

  • CROSS JOIN LATERAL, когда нужно сохранить строки без результата (нужен LEFT JOIN LATERAL ... ON true).
  • Нет индекса для подзапроса — он выполняется для каждой левой строки с полным сканом.
  • LATERAL по огромной левой таблице, где оконная функция за один проход дешевле.

Что важно помнить

  • LATERAL = коррелированный подзапрос в FROM, который может вернуть несколько строк и столбцов.
  • Идеален для top-N per group с индексом.
  • LEFT JOIN LATERAL ... ON true сохраняет все левые строки.

Мини-шпаргалка

  • CROSS JOIN LATERAL (SELECT ... WHERE x = left.x LIMIT n) alias.
  • LEFT JOIN LATERAL (...) alias ON true.
  • jsonb_array_elements, unnest — в FROM с неявным LATERAL.

Документация

PostgreSQL: LATERAL Subqueries.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам