Подзапросы в SQL: когда нужен вложенный SELECT, CTE или JOIN

Три места, где может стоять подзапрос

Синтаксически подзапрос — это SELECT внутри другого запроса, но роль у него меняется в зависимости от места. В FROM он работает как временная таблица: возвращает набор строк, у которого есть своё зерно. В WHERE он участвует в условии — либо как список значений для IN, либо как проверка существования в EXISTS. В SELECT он возвращает одно значение на строку и называется скалярным.

От места зависит и цена ошибки. Подзапрос в FROM неправильного зерна испортит все последующие расчёты. Подзапрос в WHERE может незаметно отфильтровать строки через NULL. Скалярный подзапрос в SELECT ничего не сломает логически, но может выполняться для каждой строки результата.

Зерно решает, а не синтаксис

Классическая задача: найти товары с выручкой выше средней. В ней прячется вопрос — средней по чему? Если посчитать avg(amount) по строкам заказов, получится средний чек позиции. Если сначала свернуть выручку по товарам, а потом взять среднее — получится средняя выручка на товар. Это два разных числа, и они отвечают на разные вопросы.

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

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

-- средний чек позиции: одна строка — заказ
SELECT avg(amount) AS avg_order_line
FROM orders
WHERE status = 'paid';

-- средняя выручка на товар: одна строка — товар
WITH product_revenue AS (
  SELECT product_id, sum(amount) AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY product_id
)
SELECT avg(revenue) AS avg_product_revenue
FROM product_revenue;

Скалярный подзапрос: удобно писать, дорого выполнять

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

Почти всегда есть более дешёвая формулировка. Если нужно одно агрегированное значение на сущность — сверните таблицу заранее и присоедините результат. Если нужны атрибуты «последней» строки — оконная функция или LATERAL с LIMIT 1 выразят это точнее и выполнятся один раз.

Обратная сторона: не стоит переписывать всё подряд. Скалярный подзапрос по маленькому справочнику или одно значение на весь отчёт — совершенно нормальный код, который читается лучше лишнего CTE.

-- 1. коррелированный подзапрос: выполняется на каждой строке
SELECT u.user_id,
  (SELECT max(o.created_at) FROM orders o WHERE o.user_id = u.user_id) AS last_order_at
FROM users u;

-- 2. предварительная агрегация: один проход по orders
WITH last_order AS (
  SELECT user_id, max(created_at) AS last_order_at
  FROM orders GROUP BY user_id
)
SELECT u.user_id, l.last_order_at
FROM users u
LEFT JOIN last_order l ON l.user_id = u.user_id;

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

EXISTS, IN и JOIN: похожие запросы с разным результатом

Три конструкции часто считают взаимозаменяемыми, но у них разное поведение по строкам. EXISTS проверяет наличие хотя бы одной подходящей строки и не меняет количество строк результата. IN сравнивает значения и тоже не размножает результат, но ломается на NULL при отрицании. А JOIN соединяет строки — и если справа несколько совпадений, каждая левая строка вернётся несколько раз.

Последнее и есть самая частая ошибка в аналитике: «пользователи, которые что-то купили» через JOIN с таблицей заказов дают не количество пользователей, а количество заказов. Метрика вырастает в разы, и понять это по самому числу невозможно.

Практическое правило: если из второй таблицы не нужны поля, а нужен только факт наличия или отсутствия строки — это EXISTS или NOT EXISTS. JOIN берите тогда, когда атрибуты правой таблицы действительно попадают в результат, и заранее решите, что делать с несколькими совпадениями.

• Нужен факт наличия — EXISTS, количество строк не изменится.
• Нужно исключение по списку — NOT EXISTS, он устойчив к NULL.
• Нужны поля второй таблицы — JOIN и явное решение про кардинальность.
• Нужно одно значение на сущность — предварительная агрегация, а не JOIN «как есть».

CTE: именованные слои, а не ускорение

CTE через WITH — это в первую очередь читаемость. Каждый слой получает имя, которое можно проверить: orders_clean, product_revenue, ranked_products. Такой запрос читается сверху вниз как последовательность шагов, а не как матрёшка из скобок.

Вокруг производительности CTE ходит много мифов. В PostgreSQL до 12-й версии CTE всегда материализовался и работал как оптимизационный барьер: планировщик не мог протолкнуть условие внутрь. Начиная с 12-й версии, обычный CTE по умолчанию встраивается в основной запрос, если он используется один раз, а управлять этим можно явно через MATERIALIZED и NOT MATERIALIZED. В других СУБД поведение своё, поэтому «CTE быстрее подзапроса» — утверждение, которое проверяется планом, а не верой.

Материализация иногда действительно нужна: когда тяжёлый промежуточный результат используется несколько раз, разумно посчитать его один раз явно. Но выбирать между CTE и подзапросом стоит по читаемости, а к плану выполнения обращаться уже при реальной проблеме со скоростью.

Как разложить длинный запрос

Рабочая схема из четырёх слоёв закрывает большинство аналитических задач. Первый слой — чистая база: период, исключение тестовых записей и отменённых заказов. Второй — нормализация зерна: свернуть до нужного уровня, убрать дубли. Третий — расчёт метрик. Четвёртый — представление: сортировка, ранги, ограничение выдачи.

Ниже такой запрос целиком. Обратите внимание на комментарии с зерном: они стоят дешевле любой документации и остаются вместе с кодом.

WITH orders_clean AS (            -- зерно: одна строка = позиция заказа
  SELECT order_id, product_id, user_id, amount, created_at
  FROM orders
  WHERE status = 'paid'
    AND created_at >= date '2026-07-01'
    AND created_at <  date '2026-08-01'
    AND is_test IS NOT TRUE
),
product_revenue AS (             -- зерно: одна строка = товар
  SELECT product_id,
         sum(amount)            AS revenue,
         count(DISTINCT order_id) AS orders_count,
         count(DISTINCT user_id)  AS buyers
  FROM orders_clean
  GROUP BY product_id
),
benchmark AS (                   -- зерно: одна строка на весь отчёт
  SELECT avg(revenue) AS avg_revenue FROM product_revenue
)
SELECT p.product_id, p.revenue, p.orders_count, p.buyers,
       round(p.revenue / b.avg_revenue, 2) AS vs_average
FROM product_revenue p
CROSS JOIN benchmark b
WHERE p.revenue > b.avg_revenue
ORDER BY p.revenue DESC;

Проверка слоёв вместо проверки результата

Готовый отчёт проверить трудно: в нём уже нет промежуточных чисел. Зато каждый слой проверяется в одну строку. Сколько позиций осталось после очистки и сходится ли это с ожиданием по периоду? Совпадает ли число строк в product_revenue с числом уникальных товаров? Не потерялись ли товары с нулевой выручкой, если они должны быть в отчёте?

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

Когда подзапрос лишний

Обратная крайность тоже встречается: запрос из шести CTE, где половина слоёв просто переименовывает колонки. Подзапрос оправдан, когда он меняет зерно, изолирует сложное вычисление или используется повторно. Если ни одно из трёх не выполняется, обычный фильтр читается яснее.

Хороший ориентир — можно ли назвать слой существительным с понятным смыслом. active_users_july — можно. step_3 — нельзя, и это признак, что слой появился не из логики задачи, а из желания разбить запрос на части.

• Слой меняет зерно данных — оставить.
• Результат используется несколько раз — оставить.
• Слой изолирует сложное выражение с понятным именем — оставить.
• Слой только переименовывает колонки — убрать.

Чеклист перед тем, как отдать запрос в отчёт

Сложный запрос живёт дольше, чем задача, ради которой он написан: его скопируют в дашборд, потом в соседний отчёт, потом в чужую витрину. Пять минут проверки экономят недели споров о том, чьё число правильное.

• Для каждого слоя сформулировано зерно одной фразой?
• Период и исключения (тестовые записи, отмены, возвраты) стоят в самом первом слое, а не размазаны по запросу?
• Там, где нужен только факт наличия строки, используется EXISTS, а не JOIN?
• Проверено, что число строк после JOIN не выросло относительно левой таблицы?
• Коррелированные подзапросы в SELECT заменены агрегацией, оконной функцией или LATERAL там, где данных много?
• Рядом с запросом остались ожидаемые контрольные числа для следующей проверки?