LATERAL JOIN в PostgreSQL: последняя запись и top-N на сущность

Что делает LATERAL

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

Из-за этого LATERAL часто описывают как «коррелированный подзапрос, который умеет возвращать несколько строк и несколько колонок». Формулировка точная: скалярный подзапрос в SELECT отдаёт ровно одно значение, а LATERAL — целый набор полей, который дальше используется как обычная таблица.

Конструкция есть в стандарте SQL и поддерживается PostgreSQL, MySQL начиная с 8.0.14 и Oracle. В SQL Server у неё другое имя — CROSS APPLY и OUTER APPLY, но смысл тот же.

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

LEFT или CROSS: где теряются строки

Самая частая ошибка с LATERAL — потеря сущностей без связанных записей. CROSS JOIN LATERAL не оставит пользователя, у которого ещё нет ни одного заказа: подзапрос вернул ноль строк, значит и в результате его не будет. Для отчёта «сколько у нас клиентов» это сразу занижает базу.

LEFT JOIN LATERAL ... ON true ведёт себя как обычный LEFT JOIN: пользователь остаётся, а поля из правой части приходят как NULL. Условие ON true выглядит странно, но оно обязательно синтаксически — связь уже выражена внутри подзапроса, и повторять её в ON не нужно.

Практическое правило: если левая таблица определяет базу отчёта — берите LEFT JOIN LATERAL. CROSS JOIN LATERAL уместен, когда отсутствие правой записи означает, что строка вообще не должна попасть в результат.

SELECT u.user_id, u.email,
       o.order_id, o.created_at AS last_order_at, o.amount
FROM users u
LEFT JOIN LATERAL (
  SELECT order_id, created_at, amount
  FROM orders
  WHERE user_id = u.user_id
    AND status = 'paid'
  ORDER BY created_at DESC, order_id DESC
  LIMIT 1
) o ON true
ORDER BY o.created_at DESC NULLS LAST;

Top-N на сущность

Главное преимущество LATERAL проявляется, когда N больше единицы. «Два последних события каждого пользователя», «три самых дорогих заказа каждого клиента», «пять последних сообщений в каждом диалоге» — всё это меняется одним числом в LIMIT.

Здесь важно помнить, что каждая левая строка размножится на N. Пользователь с двумя последними событиями даст две строки, и любая агрегация после такого соединения должна это учитывать. Если дальше считается сумма по пользователю, её нужно брать до LATERAL, а не после.

И обязательно добавьте в сортировку тай-брейкер. При одинаковых метках времени без него результат недетерминирован: сегодня в карточке одно событие, завтра другое, а объяснить это будет нечем.

Полезная деталь для отчётов: внутри LATERAL можно нумеровать записи прямо на месте. Добавьте в подзапрос row_number() OVER (ORDER BY occurred_at DESC) — и в результате появится колонка «какое это событие по счёту от последнего». Дальше по ней удобно раскладывать данные в колонки или показывать только первое событие, не переписывая запрос.

SELECT u.user_id, e.event_name, e.occurred_at
FROM users u
LEFT JOIN LATERAL (
  SELECT event_name, occurred_at
  FROM events
  WHERE user_id = u.user_id
  ORDER BY occurred_at DESC, event_id DESC
  LIMIT 2
) e ON true
WHERE u.registered_at >= date '2026-07-01'
ORDER BY u.user_id, e.occurred_at DESC;

Когда есть способ проще

LATERAL не всегда лучший выбор. Если нужна только дата последнего заказа, обычный GROUP BY с max короче и понятнее. Если нужна одна строка на сущность и вы работаете в PostgreSQL, DISTINCT ON решает задачу компактнее. Если нужен полный рейтинг всех записей — это территория оконных функций.

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

Есть и вопрос читаемости, который часто важнее миллисекунд. LIMIT 2 внутри подзапроса — это дословный перевод фразы «два последних события», и такой запрос понимает человек, который зашёл в него впервые. Версия с row_number() <= 2 во внешнем фильтре требует держать в голове ещё один слой. При прочих равных выбирайте ту форму, которую проще объяснить коллеге.

Индекс решает, будет ли это быстро

LATERAL выполняется для каждой левой строки, поэтому цена одного запуска умножается на размер левой таблицы. Всё держится на том, чтобы внутренний запрос доставал свои N строк мгновенно.

Нужен составной индекс, который соответствует и связи, и сортировке: сначала ключ соединения, затем поля из ORDER BY в том же направлении. Тогда база берёт первые строки прямо из индекса и останавливается на LIMIT, не сортируя всю историю пользователя.

Проверять это стоит через EXPLAIN ANALYZE: во внутреннем узле должно быть обращение по индексу, а не последовательное сканирование, и число loops должно совпадать с количеством левых строк — тогда понятно, во что обходится один запуск.

-- порядок колонок повторяет связь и ORDER BY подзапроса
CREATE INDEX orders_user_recent_idx
  ON orders (user_id, created_at DESC, order_id DESC)
  WHERE status = 'paid';

-- частичный индекс уместен, когда отчёты всегда смотрят только оплаченные
EXPLAIN ANALYZE
SELECT u.user_id, o.order_id
FROM users u
LEFT JOIN LATERAL (
  SELECT order_id FROM orders
  WHERE user_id = u.user_id AND status = 'paid'
  ORDER BY created_at DESC, order_id DESC LIMIT 1
) o ON true;

Проверка: сколько строк вернулось и почему

После соединения с LATERAL первым делом сверяют количество строк. Для варианта с LIMIT 1 их должно быть ровно столько же, сколько в левой таблице после её фильтров, — ни больше, ни меньше. Больше означает, что LIMIT не сработал или в подзапросе осталось лишнее соединение. Меньше — что вместо LEFT стоит CROSS и сущности без записей молча выпали.

Вторая проверка — доля пустых значений. Если в карточке клиента у 40% пользователей нет последнего заказа, это может быть правдой, а может быть слишком узким фильтром внутри подзапроса: например, status = 'paid' при том, что большая часть заказов лежит в статусе «в обработке».

Такие проверки удобно писать сразу рядом с запросом и хранить вместе с ним. Ниже — минимальный набор из трёх чисел, который ловит обе ошибки.

WITH base AS (
  SELECT user_id FROM users
  WHERE registered_at >= date '2026-07-01'
),
joined AS (
  SELECT b.user_id, o.order_id
  FROM base b
  LEFT JOIN LATERAL (
    SELECT order_id FROM orders
    WHERE user_id = b.user_id AND status = 'paid'
    ORDER BY created_at DESC, order_id DESC LIMIT 1
  ) o ON true
)
SELECT
  (SELECT count(*) FROM base)                        AS base_rows,
  (SELECT count(*) FROM joined)                      AS joined_rows,
  (SELECT count(*) FROM joined WHERE order_id IS NULL) AS without_order;

Скрытый LATERAL: функции в FROM

Если вы разворачивали JSON-массив через jsonb_array_elements или раскладывали массив через unnest, вы уже пользовались LATERAL, просто не писали это слово: для функций в FROM зависимость от предыдущих источников подразумевается.

Понимание этого помогает в двух местах. Во-первых, становится очевидно, почему такие функции размножают строки — это тот же локальный подзапрос, просто возвращающий несколько записей. Во-вторых, открывается полезный приём с generate_series: построить календарь дат и для каждой даты подтянуть свою порцию данных, сохранив дни без событий.

SELECT d.day,
       coalesce(s.active_users, 0) AS active_users
FROM generate_series(
       date '2026-07-01', date '2026-07-31', interval '1 day'
     ) AS d(day)
LEFT JOIN LATERAL (
  SELECT count(DISTINCT user_id) AS active_users
  FROM events
  WHERE occurred_at >= d.day
    AND occurred_at <  d.day + interval '1 day'
) s ON true
ORDER BY d.day;

Чеклист

LATERAL хорошо читается — именно поэтому его легко поставить туда, где задача решается проще. Проверьте себя по короткому списку.

• Подзапрос действительно зависит от левой строки, а не просто вынесен в FROM?
• Используется LEFT JOIN LATERAL ... ON true, если сущности без связанных записей должны остаться в отчёте?
• В подзапросе есть LIMIT, а в ORDER BY — стабильный тай-брейкер?
• Учтено, что каждая левая строка размножится на N?
• Есть составной индекс по ключу связи и полям сортировки в том же направлении?
• Проверено, что задача не решается короче через GROUP BY, DISTINCT ON или оконную функцию?