LATERAL JOIN in PostgreSQL: Top-N per Group and Correlated FROM Subqueries

Обычный подзапрос в FROM живёт как отдельная таблица.

Он не видит колонки таблиц, которые находятся рядом с ним во внешнем запросе.

Но иногда это очень нужно.

Например:

• для каждого пользователя взять 2 самых дорогих заказа;
• для каждого товара взять последнюю цену;
• для каждой статьи развернуть её теги через UNNEST;
• для каждого заказа развернуть JSON-массив товаров;
• для каждого пользователя сгенерировать месяцы с даты регистрации;
• для каждой строки выполнить подзапрос, который зависит от этой строки.

Для таких задач в PostgreSQL есть LATERAL.

Коротко:

LATERAL разрешает подзапросу справа ссылаться на таблицы, которые перечислены левее в FROM.

Это превращает FROM в понятный цикл:

для каждой строки слева
  выполни правый подзапрос с её значениями

Проблема обычного подзапроса во FROM

Допустим, есть таблицы users и orders.

users:

id | email
---+----------------
1  | anna@mail.com
2  | bob@mail.com

orders:

id  | user_id | amount
----+---------+--------
101 | 1       | 1500
102 | 1       | 2300
201 | 2       | 700

Хотим для каждого пользователя получить его заказы.

Можно попробовать написать так:

SELECT
  u.id,
  u.email,
  o.amount
FROM users u,
     (
       SELECT amount
       FROM orders
       WHERE user_id = u.id
     ) o;

Но такой запрос не сработает.

Проблема здесь:

WHERE user_id = u.id

Подзапрос в FROM пытается обратиться к u.id, но обычный подзапрос не видит таблицу users снаружи.

Для PostgreSQL этот подзапрос как отдельная независимая таблица.

Ему неизвестно, что такое u.

Как LATERAL решает проблему

Добавим LATERAL:

SELECT
  u.id,
  u.email,
  o.amount
FROM users u
CROSS JOIN LATERAL (
  SELECT amount
  FROM orders
  WHERE user_id = u.id
) o;

Теперь подзапрос справа может видеть u.id.

PostgreSQL воспринимает это примерно так:

возьми пользователя 1
  выполни подзапрос orders WHERE user_id = 1

возьми пользователя 2
  выполни подзапрос orders WHERE user_id = 2

Результат:

id | email         | amount
---+---------------+--------
1  | anna@mail.com | 1500
1  | anna@mail.com | 2300
2  | bob@mail.com  | 700

LATERAL сделал правый подзапрос зависимым от текущей строки слева.

Главное правило LATERAL

LATERAL может ссылаться только на таблицы, которые стоят левее в FROM.

Например, так можно:

FROM users u
CROSS JOIN LATERAL (
  SELECT *
  FROM orders o
  WHERE o.user_id = u.id
) x

Подзапрос видит u, потому что users u стоит слева.

А так нельзя:

FROM LATERAL (
  SELECT *
  FROM orders o
  WHERE o.user_id = u.id
) x
CROSS JOIN users u

Здесь u объявлен правее, поэтому подзапрос его ещё не видит.

Можно запомнить так:

LATERAL смотрит влево.

CROSS JOIN LATERAL

Чаще всего LATERAL пишут так:

SELECT
  u.id,
  o.id AS order_id,
  o.amount
FROM users u
CROSS JOIN LATERAL (
  SELECT
    id,
    amount
  FROM orders
  WHERE user_id = u.id
) o;

CROSS JOIN LATERAL означает:

для каждой строки слева выполни подзапрос справа и присоедини все найденные строки.

Если подзапрос вернул 3 строки, внешняя строка размножится на 3 строки.

Если подзапрос вернул 0 строк, внешняя строка исчезнет из результата.

Это поведение похоже на обычный INNER JOIN.

Запятая во FROM и LATERAL

Можно встретить короткую запись через запятую:

SELECT
  u.id,
  o.amount
FROM users u,
     LATERAL (
       SELECT amount
       FROM orders
       WHERE user_id = u.id
     ) o;

Это по смыслу похоже на:

CROSS JOIN LATERAL

Но явная запись обычно лучше читается:

FROM users u
CROSS JOIN LATERAL (...) o

Особенно для новичков и на ревью.

Запятая во FROM часто делает запрос менее очевидным.

Top-N на группу: главный сценарий LATERAL

Самый популярный пример LATERAL — взять top-N строк на каждую группу.

Например:

для каждого пользователя получить 2 самых дорогих заказа.

Запрос:

SELECT
  u.id,
  u.email,
  top_orders.id AS order_id,
  top_orders.amount
FROM users u
JOIN LATERAL (
  SELECT
    o.id,
    o.amount
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.amount DESC
  LIMIT 2
) top_orders ON true;

Что здесь происходит:

WHERE o.user_id = u.id

привязывает подзапрос к текущему пользователю.

ORDER BY o.amount DESC

сортирует заказы этого пользователя от дорогих к дешёвым.

LIMIT 2

оставляет только два самых дорогих заказа.

То есть подзапрос выполняется отдельно для каждого пользователя.

Результат может быть таким:

id | email         | order_id | amount
---+---------------+----------+--------
1  | anna@mail.com | 102      | 2300
1  | anna@mail.com | 101      | 1500
2  | bob@mail.com  | 201      | 700

Зачем ON true

В примере выше есть странная часть:

ON true

Полный фрагмент:

JOIN LATERAL (...) top_orders ON true

Почему так?

Потому что синтаксис JOIN требует условие ON.

Но реальная связь уже находится внутри подзапроса:

WHERE o.user_id = u.id

Поэтому снаружи мы пишем:

ON true

То есть:

присоединяй всё, что вернул lateral-подзапрос.

Это нормальный и частый паттерн для JOIN LATERAL.

LEFT JOIN LATERAL: сохранить строки без результата

Есть важная ловушка.

Если использовать JOIN LATERAL или CROSS JOIN LATERAL, строки слева исчезнут, если подзапрос справа ничего не вернул.

Например, есть пользователь без заказов.

Запрос:

SELECT
  u.id,
  u.email,
  top_orders.amount
FROM users u
JOIN LATERAL (
  SELECT amount
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY amount DESC
  LIMIT 2
) top_orders ON true;

не покажет пользователя без заказов.

Почему?

Потому что для него lateral-подзапрос вернул 0 строк.

Если нужно сохранить всех пользователей, используйте LEFT JOIN LATERAL.

SELECT
  u.id,
  u.email,
  top_orders.amount
FROM users u
LEFT JOIN LATERAL (
  SELECT amount
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY amount DESC
  LIMIT 2
) top_orders ON true;

Теперь пользователь без заказов останется в результате, а поля из подзапроса будут NULL.

Результат:

id | email          | amount
---+----------------+--------
1  | anna@mail.com  | 2300
1  | anna@mail.com  | 1500
2  | bob@mail.com   | 700
3  | empty@mail.com | NULL

Можно запомнить так:

JOIN LATERAL      -- оставить только строки, где подзапрос что-то нашёл
LEFT JOIN LATERAL -- сохранить строки слева даже без результата справа

Почему ON true не делает LEFT JOIN бессмысленным

На первый взгляд кажется странным:

LEFT JOIN LATERAL (...) ON true

Если ON true всегда истинно, зачем тогда LEFT JOIN?

Смысл в том, что ON true применяется к строкам, которые подзапрос уже вернул.

Если подзапрос вернул строки, они присоединяются.

Если подзапрос вернул 0 строк, LEFT JOIN сохраняет строку слева и подставляет NULL.

То есть LEFT JOIN LATERAL ... ON true означает:

выполни подзапрос для текущей строки
если он что-то вернул — присоедини
если не вернул ничего — сохрани левую строку с NULL справа

Связь обычно находится внутри подзапроса:

WHERE o.user_id = u.id

А ON true просто говорит:

не добавляй дополнительных условий снаружи

Top-1 на группу: последний заказ пользователя

LATERAL хорошо подходит для top-1 на группу.

Например, нужно получить последний заказ каждого пользователя.

SELECT
  u.id,
  u.email,
  last_order.id AS last_order_id,
  last_order.amount,
  last_order.created_at
FROM users u
LEFT JOIN LATERAL (
  SELECT
    o.id,
    o.amount,
    o.created_at
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC, o.id DESC
  LIMIT 1
) last_order ON true;

Здесь для каждого пользователя выбирается один самый новый заказ.

Обратите внимание на сортировку:

ORDER BY o.created_at DESC, o.id DESC

id добавлен как тай-брейкер.

Если у пользователя два заказа в одну и ту же секунду, порядок всё равно будет стабильным.

Для top-N и top-1 всегда полезно делать сортировку детерминированной.

Top-N через LATERAL или ROW_NUMBER

Top-N на группу можно сделать не только через LATERAL, но и через оконную функцию ROW_NUMBER.

Вариант через ROW_NUMBER:

WITH ranked_orders AS (
  SELECT
    o.id,
    o.user_id,
    o.amount,
    ROW_NUMBER() OVER (
      PARTITION BY o.user_id
      ORDER BY o.amount DESC, o.id
    ) AS rn
  FROM orders o
)
SELECT
  u.id,
  u.email,
  ro.id AS order_id,
  ro.amount
FROM users u
JOIN ranked_orders ro ON ro.user_id = u.id
WHERE ro.rn <= 2;

Вариант через LATERAL:

SELECT
  u.id,
  u.email,
  top_orders.id AS order_id,
  top_orders.amount
FROM users u
JOIN LATERAL (
  SELECT
    o.id,
    o.amount
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.amount DESC, o.id
  LIMIT 2
) top_orders ON true;

Оба варианта рабочие.

Разница в стиле и плане выполнения.

ROW_NUMBER сначала ранжирует заказы, а потом фильтрует rn <= 2.

LATERAL говорит:

для каждого пользователя возьми только первые 2 заказа

Если есть подходящий индекс, LATERAL может быть очень эффективным.

Индекс для top-N через LATERAL

Для запроса:

SELECT
  u.id,
  top_orders.id,
  top_orders.amount
FROM users u
JOIN LATERAL (
  SELECT
    o.id,
    o.amount
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.amount DESC, o.id
  LIMIT 2
) top_orders ON true;

полезен индекс:

CREATE INDEX orders_user_amount_idx
ON orders (user_id, amount DESC, id);

Почему?

Потому что подзапрос делает:

WHERE o.user_id = u.id
ORDER BY o.amount DESC, o.id
LIMIT 2

Индекс по (user_id, amount DESC, id) помогает PostgreSQL быстро найти заказы конкретного пользователя уже в нужном порядке и взять первые 2.

Без такого индекса база может быть вынуждена каждый раз искать и сортировать заказы пользователя.

Главное правило:

Для LATERAL с ORDER BY ... LIMIT индекс должен начинаться с ключа связи и продолжаться колонками сортировки.

LATERAL с агрегатами

LATERAL можно использовать не только для top-N.

Например, нужно рядом с каждым пользователем показать агрегаты по его заказам:

• количество заказов;
• сумму заказов;
• дату последнего заказа.

SELECT
  u.id,
  u.email,
  stats.orders_count,
  stats.total_amount,
  stats.last_order_at
FROM users u
LEFT JOIN LATERAL (
  SELECT
    COUNT(*) AS orders_count,
    SUM(o.amount) AS total_amount,
    MAX(o.created_at) AS last_order_at
  FROM orders o
  WHERE o.user_id = u.id
) stats ON true;

Но для простых агрегатов часто можно сделать обычный GROUP BY.

Например:

SELECT
  u.id,
  u.email,
  COUNT(o.id) AS orders_count,
  SUM(o.amount) AS total_amount,
  MAX(o.created_at) AS last_order_at
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

Когда тогда нужен LATERAL?

Он особенно полезен, если внутри подзапроса есть:

• ORDER BY;
• LIMIT;
• сложная логика на одну внешнюю строку;
• несколько зависимых вычислений;
• set-returning функции;
• выбор «первой/последней/top-N» строки.

LATERAL и функции, возвращающие строки

LATERAL часто используется с функциями, которые возвращают набор строк.

Например:

• UNNEST;
• jsonb_array_elements;
• generate_series.

Такие функции часто должны получить значение из соседней строки.

UNNEST с LATERAL

Допустим, у статьи есть массив тегов:

id | title              | tags
---+--------------------+-----------------------------
1  | PostgreSQL arrays  | {sql,postgresql,arrays}
2  | SQL joins          | {sql,joins}

Развернём теги в строки:

SELECT
  a.id,
  a.title,
  t.tag
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag);

Результат:

id | title             | tag
---+-------------------+------------
1  | PostgreSQL arrays | sql
1  | PostgreSQL arrays | postgresql
1  | PostgreSQL arrays | arrays
2  | SQL joins         | sql
2  | SQL joins         | joins

Здесь UNNEST(a.tags) использует колонку a.tags из таблицы слева.

Для табличных функций PostgreSQL часто подразумевает lateral-поведение автоматически, но явное CROSS JOIN LATERAL делает запрос понятнее.

LEFT JOIN LATERAL с UNNEST

Если у статьи пустой массив тегов, обычный CROSS JOIN LATERAL уберёт статью из результата.

Чтобы сохранить статьи без тегов:

SELECT
  a.id,
  a.title,
  t.tag
FROM articles a
LEFT JOIN LATERAL UNNEST(a.tags) AS t(tag) ON true;

Тогда статья без тегов останется, а tag будет NULL.

Это тот же принцип, что и с заказами:

CROSS JOIN LATERAL -- нет элементов, строка исчезает
LEFT JOIN LATERAL  -- нет элементов, строка остаётся с NULL

JSONB-массивы и LATERAL

Допустим, в заказе есть JSONB-массив товаров:

{
  "items": [
    {"sku": "A-100", "qty": 2},
    {"sku": "B-200", "qty": 1}
  ]
}

Можно развернуть массив через jsonb_array_elements.

SELECT
  o.id AS order_id,
  item.value ->> 'sku' AS sku,
  (item.value ->> 'qty')::int AS qty
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.payload -> 'items') AS item(value);

Результат:

order_id | sku   | qty
---------+-------+----
101      | A-100 | 2
101      | B-200 | 1

Здесь:

jsonb_array_elements(o.payload -> 'items')

берёт JSONB-массив items из текущего заказа и превращает его в строки.

После этого с каждым товаром можно работать как с отдельной строкой.

LATERAL с generate_series

LATERAL полезен и с generate_series.

Например, нужно для каждого пользователя сгенерировать месяцы с даты регистрации до текущего месяца.

SELECT
  u.id,
  u.email,
  m.month
FROM users u
CROSS JOIN LATERAL generate_series(
  date_trunc('month', u.created_at),
  date_trunc('month', now()),
  interval '1 month'
) AS m(month);

Для каждого пользователя generate_series получает свою дату регистрации:

date_trunc('month', u.created_at)

и генерирует отдельный набор месяцев.

Пример результата:

id | email         | month
---+---------------+------------
1  | anna@mail.com | 2026-01-01
1  | anna@mail.com | 2026-02-01
1  | anna@mail.com | 2026-03-01
2  | bob@mail.com  | 2026-05-01
2  | bob@mail.com  | 2026-06-01

Так удобно строить календарные сетки, отчёты по периодам и заполнение пропущенных дат.

LATERAL и WHERE: где писать условия

Есть два места, куда можно положить условия:

• внутрь lateral-подзапроса;
• наружу во внешний WHERE.

Например:

SELECT
  u.id,
  o.amount
FROM users u
LEFT JOIN LATERAL (
  SELECT amount
  FROM orders o
  WHERE o.user_id = u.id
    AND o.status = 'paid'
  ORDER BY amount DESC
  LIMIT 2
) o ON true;

Здесь фильтр:

o.status = 'paid'

находится внутри подзапроса. Это значит:

для каждого пользователя найди top-2 только среди paid-заказов.

Если же после LEFT JOIN LATERAL написать:

WHERE o.status = 'paid'

можно случайно превратить результат в поведение, похожее на INNER JOIN, потому что строки с NULL справа будут отфильтрованы.

Поэтому для LEFT JOIN LATERAL условия на правую таблицу часто безопаснее держать внутри подзапроса.

Главное правило:

Если хотите сохранить строки слева, аккуратно обращайтесь с внешним WHERE по колонкам справа.

Когда LATERAL особенно полезен

LATERAL стоит рассмотреть, если вам нужно:

• top-N строк на каждую группу;
• top-1 строка на каждую строку слева;
• коррелированный подзапрос именно в FROM;
• ORDER BY ... LIMIT внутри зависимого подзапроса;
• развернуть массив из соседней колонки;
• развернуть JSONB-массив из соседней колонки;
• сгенерировать строки через функцию от значения текущей строки;
• сохранить строки слева через LEFT JOIN LATERAL.

Примеры:

-- top-2 orders per user
JOIN LATERAL (
  SELECT *
  FROM orders
  WHERE user_id = u.id
  ORDER BY amount DESC
  LIMIT 2
) o ON true

-- article tags
CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag)

-- order items from JSONB
CROSS JOIN LATERAL jsonb_array_elements(o.payload -> 'items') AS item(value)

-- months since signup
CROSS JOIN LATERAL generate_series(u.created_at, now(), interval '1 month') AS m(month)

Когда LATERAL не нужен

LATERAL не стоит использовать везде подряд.

Если задача решается обычным JOIN, пишите обычный JOIN.

Например:

SELECT
  u.id,
  o.id AS order_id
FROM users u
JOIN orders o ON o.user_id = u.id;

Здесь LATERAL не нужен.

Если нужен простой агрегат по группе, часто достаточно GROUP BY.

SELECT
  u.id,
  COUNT(o.id) AS orders_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

Если нужно ранжирование всех строк, часто удобнее оконные функции.

ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY amount DESC
)

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

Производительность LATERAL

LATERAL можно представить как выполнение подзапроса для каждой строки слева.

Это удобно, но может быть дорого.

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

Для top-N на группу почти всегда нужен подходящий индекс.

Например:

CREATE INDEX orders_user_amount_idx
ON orders (user_id, amount DESC, id);

Для последнего заказа:

CREATE INDEX orders_user_created_idx
ON orders (user_id, created_at DESC, id DESC);

И обязательно проверяйте план:

EXPLAIN ANALYZE
SELECT
  u.id,
  top_orders.id,
  top_orders.amount
FROM users u
JOIN LATERAL (
  SELECT
    o.id,
    o.amount
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.amount DESC, o.id
  LIMIT 2
) top_orders ON true;

В хорошем плане PostgreSQL может использовать индекс для быстрого поиска top-N по каждому пользователю.

Но если индекс не подходит, база может делать много повторных сканирований.

LATERAL и порядок выполнения

Важно не воспринимать SQL буквально как цикл в приложении.

PostgreSQL всё равно использует оптимизатор и выбирает план выполнения.

Но логически LATERAL означает, что правая часть зависит от левой.

Поэтому у оптимизатора меньше свободы переставлять такие части местами.

Это ещё одна причина не использовать LATERAL без необходимости.

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

MySQL

В MySQL 8.0.14+ появились lateral derived tables.

Синтаксис может отличаться в деталях, но идея похожая: подзапрос в FROM может ссылаться на таблицы слева.

Однако для top-N на группу в MySQL 8 часто используют оконные функции:

WITH ranked_orders AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY amount DESC, id
    ) AS rn
  FROM orders o
)
SELECT *
FROM ranked_orders
WHERE rn <= 2;

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

Если проект на MySQL, всегда проверяйте конкретную версию и поддерживаемый синтаксис.

ClickHouse

В ClickHouse нет полного аналога PostgreSQL LATERAL в общем виде.

Но для некоторых задач есть свои инструменты.

Например, top-N на группу можно сделать через LIMIT BY.

SELECT
  user_id,
  id,
  amount
FROM orders
ORDER BY user_id, amount DESC
LIMIT 2 BY user_id;

Это означает:

после сортировки оставить максимум 2 строки на каждого user_id.

Для массивов в ClickHouse часто используют arrayJoin.

SELECT
  id,
  arrayJoin(tags) AS tag
FROM articles;

То есть задачи, которые в PostgreSQL часто решаются через LATERAL, в ClickHouse обычно пишутся другими средствами.

Практические шаблоны

Top-2 заказа на каждого пользователя

SELECT
  u.id,
  u.email,
  o.id AS order_id,
  o.amount
FROM users u
JOIN LATERAL (
  SELECT
    id,
    amount
  FROM orders
  WHERE user_id = u.id
  ORDER BY amount DESC, id
  LIMIT 2
) o ON true;

Top-2 с сохранением пользователей без заказов

SELECT
  u.id,
  u.email,
  o.id AS order_id,
  o.amount
FROM users u
LEFT JOIN LATERAL (
  SELECT
    id,
    amount
  FROM orders
  WHERE user_id = u.id
  ORDER BY amount DESC, id
  LIMIT 2
) o ON true;

Последний заказ пользователя

SELECT
  u.id,
  u.email,
  o.id AS last_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, id DESC
  LIMIT 1
) o ON true;

Развернуть теги статьи

SELECT
  a.id,
  a.title,
  t.tag
FROM articles a
CROSS JOIN LATERAL UNNEST(a.tags) AS t(tag);

Развернуть теги, сохранив статьи без тегов

SELECT
  a.id,
  a.title,
  t.tag
FROM articles a
LEFT JOIN LATERAL UNNEST(a.tags) AS t(tag) ON true;

Развернуть JSONB-массив товаров

SELECT
  o.id AS order_id,
  item.value ->> 'sku' AS sku,
  (item.value ->> 'qty')::int AS qty
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.payload -> 'items') AS item(value);

Сгенерировать месяцы для каждого пользователя

SELECT
  u.id,
  m.month
FROM users u
CROSS JOIN LATERAL generate_series(
  date_trunc('month', u.created_at),
  date_trunc('month', now()),
  interval '1 month'
) AS m(month);

Индекс для top-2 заказов по сумме

CREATE INDEX orders_user_amount_idx
ON orders (user_id, amount DESC, id);

Индекс для последнего заказа

CREATE INDEX orders_user_created_idx
ON orders (user_id, created_at DESC, id DESC);

Альтернатива через ROW_NUMBER

WITH ranked_orders AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY amount DESC, id
    ) AS rn
  FROM orders o
)
SELECT
  *
FROM ranked_orders
WHERE rn <= 2;

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

Ожидают, что обычный подзапрос увидит внешнюю таблицу

Не сработает:

SELECT
  u.id,
  o.amount
FROM users u,
     (
       SELECT amount
       FROM orders
       WHERE user_id = u.id
     ) o;

Нужно:

SELECT
  u.id,
  o.amount
FROM users u
CROSS JOIN LATERAL (
  SELECT amount
  FROM orders
  WHERE user_id = u.id
) o;

Используют JOIN LATERAL и теряют строки без результата

Если пользователи без заказов должны остаться, плохо:

JOIN LATERAL (...) o ON true

Лучше:

LEFT JOIN LATERAL (...) o ON true

Пишут фильтр справа во внешнем WHERE после LEFT JOIN

Опасно:

SELECT
  u.id,
  o.amount
FROM users u
LEFT JOIN LATERAL (
  SELECT *
  FROM orders
  WHERE user_id = u.id
) o ON true
WHERE o.status = 'paid';

Так можно потерять пользователей без заказов.

Часто лучше положить условие внутрь подзапроса:

LEFT JOIN LATERAL (
  SELECT *
  FROM orders
  WHERE user_id = u.id
    AND status = 'paid'
) o ON true

Не добавляют тай-брейкер в ORDER BY

Нестабильно:

ORDER BY amount DESC

Лучше:

ORDER BY amount DESC, id

Особенно если нужен top-N.

Используют LATERAL без индекса

Для top-N на группу без индекса запрос может быть очень дорогим.

Если подзапрос делает:

WHERE user_id = u.id
ORDER BY amount DESC
LIMIT 2

индекс должен помогать именно этому паттерну:

CREATE INDEX orders_user_amount_idx
ON orders (user_id, amount DESC, id);

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

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

Пример:

SELECT
  u.id,
  o.amount
FROM users u
CROSS JOIN LATERAL (
  SELECT amount
  FROM orders
  WHERE user_id = u.id
) o;

Главные правила:

• обычный подзапрос в FROM не видит внешние таблицы;
• LATERAL позволяет подзапросу справа видеть строки слева;
• ссылаться можно только на таблицы, которые идут левее;
• CROSS JOIN LATERAL убирает строки слева, если справа нет результата;
• LEFT JOIN LATERAL ... ON true сохраняет строки слева;
• ON true — нормальный паттерн, если связь уже внутри подзапроса;
• LATERAL удобен для top-N на группу;
• LATERAL полезен с UNNEST, jsonb_array_elements, generate_series;
• для ORDER BY ... LIMIT внутри lateral-подзапроса нужен подходящий индекс;
• если задачу решает обычный JOIN или GROUP BY, LATERAL может быть лишним.

Короткий вывод

LATERAL нужен, когда правая часть FROM должна зависеть от строки слева.

Самый понятный пример:

SELECT
  u.id,
  o.id AS order_id,
  o.amount
FROM users u
JOIN LATERAL (
  SELECT
    id,
    amount
  FROM orders
  WHERE user_id = u.id
  ORDER BY amount DESC
  LIMIT 2
) o ON true;

Этот запрос означает:

для каждого пользователя
найди его заказы
отсортируй по сумме
возьми первые 2

Главная мысль:

LATERAL превращает подзапрос во FROM в зависимый подзапрос, который видит текущую строку слева.

Используйте его для top-N на группу, разворота массивов и JSONB, генерации строк от соседней колонки и других задач, где обычный подзапрос во FROM слишком изолирован.