LATERAL JOIN в PostgreSQL: продвинутые запросы с зависимыми подзапросами

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

Зачем нужен LATERAL

Обычный подзапрос не видит другие таблицы из FROM:

-- Скалярный коррелированный подзапрос в SELECT-списке — база стандартного SQL,
-- он и без LATERAL прекрасно видит u.id:
SELECT u.name, (
    SELECT amount FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1
) AS last_order
FROM users u;
-- Работает, но возвращает только одно значение — не несколько столбцов и не несколько строк

-- А вот тот же самый подзапрос, перенесённый в FROM, без LATERAL уже не работает:
SELECT u.name, o.amount
FROM users u,
     (SELECT amount FROM orders WHERE user_id = u.id LIMIT 1) o;
-- ERROR: invalid reference to FROM-clause entry for table "u"

Разница принципиальная: в SELECT-списке коррелированный подзапрос мог всегда возвращать только одно значение. В FROM подзапрос в принципе может вернуть несколько строк и столбцов — но без LATERAL он не видит соседние таблицы из FROM, и это именно то ограничение, которое LATERAL снимает.

LATERAL решает это:

-- ✅ LATERAL: подзапрос видит u.id
SELECT u.name, o.amount
FROM users u,
     LATERAL (SELECT amount FROM orders WHERE user_id = u.id
              ORDER BY created_at DESC LIMIT 1) o;

Синтаксис

-- Неявный LATERAL (CROSS JOIN LATERAL)
SELECT ... FROM table1 t1, LATERAL (subquery using t1) sub;

-- Явный (для LEFT JOIN — сохранить строки без совпадений)
SELECT ... FROM table1 t1
LEFT JOIN LATERAL (subquery using t1) sub ON true;

TOP-N на группу (самый частый случай)

-- Последние 3 заказа для каждого пользователя
SELECT u.id, u.name, o.id AS order_id, o.amount, o.created_at
FROM users u
LEFT JOIN LATERAL (
    SELECT id, amount, created_at
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) o ON true
ORDER BY u.id, o.created_at DESC;

Сравнение с альтернативами:

-- Альтернатива 1: ROW_NUMBER() (стандартный SQL)
SELECT id, name, order_id, amount, created_at
FROM (
    SELECT u.id, u.name, o.id AS order_id, o.amount, o.created_at,
           ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.created_at DESC) AS rn
    FROM users u LEFT JOIN orders o ON o.user_id = u.id
) t
WHERE rn <= 3;

-- LATERAL часто быстрее если есть индекс на (user_id, created_at DESC):
-- для каждого пользователя делает Index Scan с LIMIT
-- ROW_NUMBER() вынужден прочитать все строки

Расчёт на каждую строку

-- Для каждого продукта: сколько продано за последние 30 дней и 7 дней
SELECT
    p.id,
    p.name,
    stats30.quantity AS qty_30d,
    stats30.revenue AS rev_30d,
    stats7.quantity AS qty_7d,
    stats7.revenue AS rev_7d
FROM products p
LEFT JOIN LATERAL (
    SELECT SUM(oi.quantity) AS quantity, SUM(oi.quantity * oi.price) AS revenue
    FROM order_items oi JOIN orders o ON o.id = oi.order_id
    WHERE oi.product_id = p.id AND o.created_at >= NOW() - INTERVAL '30 days'
) stats30 ON true
LEFT JOIN LATERAL (
    SELECT SUM(oi.quantity) AS quantity, SUM(oi.quantity * oi.price) AS revenue
    FROM order_items oi JOIN orders o ON o.id = oi.order_id
    WHERE oi.product_id = p.id AND o.created_at >= NOW() - INTERVAL '7 days'
) stats7 ON true;

UNNEST с LATERAL

-- Развернуть массив тегов с порядковым номером
SELECT p.id, p.name, tag_info.tag, tag_info.position
FROM products p
CROSS JOIN LATERAL UNNEST(p.tags) WITH ORDINALITY AS tag_info(tag, position);

-- Без LATERAL (работает так же, UNNEST неявно LATERAL):
SELECT p.id, p.name, tag, position
FROM products p
CROSS JOIN UNNEST(p.tags) WITH ORDINALITY AS t(tag, position);

generate_series с LATERAL

-- Для каждого пользователя: выручка за каждый из последних 12 месяцев
SELECT
    u.id,
    u.name,
    m.month,
    COALESCE(SUM(o.amount), 0) AS revenue
FROM users u
CROSS JOIN LATERAL generate_series(
    DATE_TRUNC('month', NOW()) - INTERVAL '11 months',
    DATE_TRUNC('month', NOW()),
    INTERVAL '1 month'
) AS m(month)
LEFT JOIN orders o ON o.user_id = u.id
    AND DATE_TRUNC('month', o.created_at) = m.month
    AND o.status = 'completed'
WHERE u.is_active = true
GROUP BY u.id, u.name, m.month
ORDER BY u.id, m.month;

Ближайший сосед (для каждой строки)

-- Для каждого заказа: ближайший предыдущий заказ того же пользователя
SELECT
    o.id,
    o.user_id,
    o.created_at,
    o.amount,
    prev.id AS prev_order_id,
    prev.created_at AS prev_order_date,
    o.created_at - prev.created_at AS days_between
FROM orders o
LEFT JOIN LATERAL (
    SELECT id, created_at
    FROM orders
    WHERE user_id = o.user_id
      AND created_at < o.created_at
    ORDER BY created_at DESC
    LIMIT 1
) prev ON true
ORDER BY o.user_id, o.created_at;

Применение функций к каждой строке

-- Для каждого текста: первые 3 слова
SELECT
    id,
    content,
    words.first_three
FROM articles
CROSS JOIN LATERAL (
    SELECT array_to_string(
        (regexp_split_to_array(content, '\s+'))[1:3],
        ' '
    ) AS first_three
) words;

Когда LATERAL быстрее ROW_NUMBER()

-- Таблица: 10 миллионов заказов, 100 000 пользователей
-- Задача: последний заказ каждого пользователя

-- ROW_NUMBER() — читает ВСЕ 10М строк, потом фильтрует
-- Время: ~15 сек

-- LATERAL с индексом на (user_id, created_at DESC) — для каждого пользователя
-- делает Index Scan с LIMIT 1 → читает ~100K строк
-- Время: ~0.5 сек

-- Индекс для LATERAL оптимизации:
CREATE INDEX ON orders (user_id, created_at DESC);

Конкретные секунды здесь иллюстративны и зависят от железа, версии PostgreSQL и распределения данных — важен не абсолютный результат, а сам факт: без подходящего индекса ROW_NUMBER() и LATERAL читают сопоставимый объём данных, с индексом LATERAL может обойтись на порядки меньшим числом строк.

LATERAL vs Коррелированный подзапрос

-- Коррелированный подзапрос: одно значение
SELECT u.name,
       (SELECT MAX(amount) FROM orders WHERE user_id = u.id) AS max_order
FROM users u;

-- LATERAL: несколько столбцов и строк
SELECT u.name, last.amount, last.created_at
FROM users u
LEFT JOIN LATERAL (
    SELECT amount, created_at
    FROM orders WHERE user_id = u.id
    ORDER BY created_at DESC LIMIT 1
) last ON true;

-- LATERAL эффективнее когда нужно несколько полей из подзапроса
-- (избегает N коррелированных подзапросов)

Подводные камни

1. LATERAL видит только предыдущие элементы FROM, не последующие. Порядок таблиц в FROM имеет значение — переставите местами, получите ошибку:

-- ❌ orders ещё не объявлен в момент, когда LATERAL его ищет
SELECT * FROM LATERAL (SELECT * FROM orders WHERE orders.user_id = u.id) sub, users u;
-- ERROR: invalid reference to FROM-clause entry for table "u"

-- ✅ Сначала users, потом LATERAL, который на него ссылается
SELECT * FROM users u, LATERAL (SELECT * FROM orders WHERE orders.user_id = u.id) sub;

2. ON true в LEFT JOIN LATERAL (...) ON true — не формальность. Вся логика фильтрации уже внутри подзапроса, но синтаксис LEFT JOIN требует какое-то условие соединения — ON true буквально означает «да, соединяем, условие не нужно». Забыть его — получить ошибку синтаксиса.

3. LATERAL выполняется построчно, как nested loop. Для каждой строки внешнего запроса подзапрос внутри LATERAL запускается заново. Без индекса на условии внутри подзапроса (в примерах выше — на orders(user_id, created_at DESC)) вместо быстрого Index Scan на каждой итерации получите Seq Scan × N строк внешнего запроса — это медленнее, чем ROW_NUMBER(), а не быстрее.

Потренируйтесь

Для каждого товара нужно вывести не последний, а два последних отзыва — и дополнительно средний рейтинг по всем его отзывам (не только последним двум). Как переписать TOP-N пример из статьи?

SELECT p.id, p.name, r.rating, r.created_at, avg_r.avg_rating
FROM products p
LEFT JOIN LATERAL (
    SELECT rating, created_at
    FROM reviews
    WHERE product_id = p.id
    ORDER BY created_at DESC
    LIMIT 2
) r ON true
LEFT JOIN LATERAL (
    SELECT AVG(rating) AS avg_rating
    FROM reviews
    WHERE product_id = p.id
) avg_r ON true;

Вопросы для самопроверки

1. Почему LATERAL-подзапрос не может ссылаться на таблицу, объявленную в FROM после него?
2. Зачем в LEFT JOIN LATERAL (...) ON true нужен ON true, если вся фильтрация уже внутри подзапроса?
3. В каком случае LATERAL окажется медленнее, чем ROW_NUMBER() OVER (PARTITION BY ...)?
4. Чем LATERAL отличается от обычного коррелированного подзапроса в SELECT-списке (SELECT u.name, (SELECT ...) FROM users u)?
5. Приведите пример задачи, где CROSS JOIN LATERAL generate_series(...) необходим, а обычный JOIN не подходит.

Итог: когда использовать LATERAL

LATERAL — мощный инструмент PostgreSQL. Используйте его когда подзапрос должен принимать значения из текущей строки и возвращать несколько строк или столбцов.

Отработайте LATERAL и другие продвинутые конструкции PostgreSQL в нашем тренажёре SQL.