Подзапросы в SQL: виды, примеры, коррелированный подзапрос

Подзапрос — это SELECT внутри другого запроса. Стоять он может в WHERE, в FROM, в SELECT и в HAVING. Главное деление проходит не по месту, а по связи с внешним запросом: некоррелированный подзапрос считается один раз, коррелированный пересчитывается для каждой строки. Отсюда вся разница в скорости.

Дальше всё на одном наборе, запросы прогнаны на PostgreSQL 16. users:

orders:

Где в запросе может стоять подзапрос

Место диктует, что подзапросу разрешено вернуть.

Вторая классификация идёт по форме результата: одно значение даёт скалярный подзапрос, один столбец нужен для IN и EXISTS, целую таблицу возвращает производная. В FROM пройдёт только табличный. В SELECT подзапрос обязан свернуться к одному значению на строку: скалярный сворачивается сам, многострочный придётся обернуть в EXISTS, IN или ARRAY().

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

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

SELECT id, amount
FROM orders
WHERE amount > (SELECT avg(amount) FROM orders)
ORDER BY id;

Скобки обязательны, псевдоним подзапросу не нужен. Внутренний SELECT отработал один раз, вернул 1166.67, дальше запрос сравнивал каждую сумму с этим числом. Но заказ 13 отменён, а в выдачу попал: статус тут не фильтрует никто. Отменённые 1200 и планку подняли, и сами в отчёт прошли. Внешний WHERE внутрь подзапроса не проникает, поэтому, когда планка считается по оплаченным, условие пишут дважды:

SELECT id, amount
FROM orders
WHERE status = 'paid'
  AND amount > (SELECT avg(amount) FROM orders WHERE status = 'paid')
ORDER BY amount DESC, id;

Планка сдвинулась с 1166.67 на 1160.00 — отменённый заказ Бориса перестал участвовать в среднем. На шести строках сдвиг копеечный, на реальных данных именно доля отменённых определяет, насколько разъедутся два ответа.

Что будет, если подзапрос вернёт больше одной строки

Скалярному подзапросу разрешена ровно одна строка. Вернул две, запрос падает.

SELECT name FROM users
WHERE id = (SELECT user_id FROM orders WHERE status = 'paid');

ERROR:  more than one row returned by a subquery used as an expression

Оплаченные заказы есть у трёх клиентов, но подзапрос вернул пять строк, по одной на каждый оплаченный заказ: клиент 1 попал в список трижды, клиенты 2 и 4 по разу. Дубли он не убирает, DISTINCT тут никто не писал. Оператору = хватит ровно одной строки, поэтому лечится это сменой оператора на IN либо уточнением подзапроса до одной строки.

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

Подзапрос в WHERE: IN, EXISTS и сравнение

Форм три, и отвечают они на разные вопросы. IN проверяет, есть ли значение в списке:

SELECT u.name
FROM users u
WHERE u.id IN (SELECT o.user_id FROM orders o WHERE o.status = 'paid')
ORDER BY u.id;

Выдача: Аня, Борис, Глеб. Столбец в подзапросе должен быть один, два дадут subquery has too many columns. Сколько вернётся строк, неважно.

EXISTS проверяет, существует ли такая строка вообще:

SELECT u.name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid')
ORDER BY u.id;

Те же трое. Что написано после SELECT внутри EXISTS, роли не играет: 1, *, o.id дадут одно и то же, база смотрит только на факт наличия строки.

А вот связь o.user_id = u.id здесь несущая. Убери её, и подзапрос начнёт отвечать на вопрос «оплаченные заказы вообще существуют», что верно всегда. В выдачу попадут все четыре клиента, включая Веру без единого заказа.

Третья форма сравнивает с квантором. Условие amount > ALL (SELECT amount FROM orders WHERE user_id = 2) оставляет один заказ 11 на 2400, потому что > ALL означает «больше каждого значения из списка». > ANY означает «больше хотя бы одного», а = ANY в точности равен IN. Отдельная история у NOT IN: один NULL внутри подзапроса обнуляет результат целиком, разбор с безопасными заменами лежит в статье про LEFT JOIN.

Подзапрос в FROM: производная таблица

Здесь подзапрос возвращает не значение, а таблицу, и внешний запрос работает с ней как с обычной. Нужен он, когда данные надо сначала свернуть, а фильтровать уже свёрнутое.

SELECT a.user_id, a.avg_amount
FROM (
  SELECT user_id, round(avg(amount), 2) AS avg_amount
  FROM orders
  GROUP BY user_id
) a
WHERE a.avg_amount > 1000
ORDER BY a.user_id;

Псевдоним a даёт имя, по которому внешний запрос обращается к колонкам свёртки. Конкретно эту задачу решает и GROUP BY user_id HAVING avg(amount) > 1000: короче, без вложенности, результат тот же. Производная таблица выигрывает, когда после свёртки нужно что-то ещё — соединить с другой таблицей, посчитать окно поверх агрегата, отсортировать по расчётной колонке.

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

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

Вопрос: какие заказы дороже среднего чека своего клиента. Коррелированный вариант:

SELECT o.id, o.user_id, o.amount
FROM orders o
WHERE o.amount > (SELECT avg(x.amount) FROM orders x WHERE x.user_id = o.user_id);

В ответе одна строка: заказ 11 Ани на 2400. Сравни с первым запросом статьи, где по общему среднему прошли три заказа. У Бориса оба заказа по 1200 при среднем 1200, строго больше нет ничего; у Глеба заказ единственный и равен собственному среднему.

Несущая часть здесь — x.user_id = o.user_id. Из-за неё планку нельзя посчитать заранее: у Ани она 1366.67, у Бориса 1200, у Глеба 500. Тот же ответ без корреляции считает средние один раз и соединяет их с заказами:

SELECT o.id, o.user_id, o.amount
FROM orders o
JOIN (
  SELECT user_id, avg(amount) AS avg_amount
  FROM orders
  GROUP BY user_id
) a ON a.user_id = o.user_id
WHERE o.amount > a.avg_amount;

Выдача совпала до строки, цена разная. Вот план коррелированного варианта из EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF):

Seq Scan on orders o (actual rows=1 loops=1)
  Filter: (amount > (SubPlan 1))
  Rows Removed by Filter: 5
  SubPlan 1
    ->  Aggregate (actual rows=1 loops=6)
          ->  Seq Scan on orders x (actual rows=2 loops=6)
                Filter: (user_id = o.user_id)
                Rows Removed by Filter: 4

loops=6 значит, что подзапрос выполнился шесть раз, по разу на каждую строку orders, а внутренний Filter: (user_id = o.user_id) — это и есть корреляция. У варианта с соединением подзапроса в плане нет вовсе: Hash Join поверх HashAggregate, обе таблицы читаются по разу, везде loops=1. А некоррелированный скалярный подзапрос из первого запроса статьи планировщик выносит в InitPlan 1 (returns $0) и считает тоже единожды.

На шести строках это неизмеримо, на миллионе строк без индекса по user_id коррелированный подзапрос превращается в миллион сканирований таблицы. PostgreSQL умеет разворачивать часть коррелированных подзапросов в соединение, но подзапрос с агрегатом внутри к таким не относится, и проверяется это только через EXPLAIN.

Почему подзапрос в SELECT замедляет отчёт

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

SELECT u.name,
       (SELECT count(*) FROM orders o WHERE o.user_id = u.id AND o.status = 'paid') AS paid_orders
FROM users u
ORDER BY u.id;

Читается прекрасно. План показывает цену:

Sort (actual rows=4 loops=1)
  Sort Key: u.id
  Sort Method: quicksort  Memory: 25kB
  ->  Seq Scan on users u (actual rows=4 loops=1)
        SubPlan 1
          ->  Aggregate (actual rows=1 loops=4)
                ->  Seq Scan on orders o (actual rows=1 loops=4)
                      Filter: ((user_id = u.id) AND (status = 'paid'::text))
                      Rows Removed by Filter: 5

Четыре клиента — четыре прохода по orders. Допиши в тот же отчёт сумму оплаченных и дату последнего заказа, станет три прохода на клиента: при 50 000 клиентов это 150 000 сканирований. Так отчёт, который «раньше открывался», перестаёт открываться. Ту же цифру даёт одно соединение, и orders в нём читается один раз:

SELECT u.name, count(o.id) AS paid_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid'
GROUP BY u.id, u.name
ORDER BY u.id;

Результат совпадает построчно. Две детали в этом запросе не косметические: фильтр status = 'paid' стоит в ON, иначе Вера выпала бы из выдачи, и считается count(o.id), а не count(*), иначе у Веры была бы единица вместо нуля. Подзапрос в SELECT оправдан, когда нужно ровно одно значение, а соединение размножило бы строки. В остальных случаях собирай соединением.

EXISTS, IN или JOIN: что выбрать

Вопрос «у кого есть хотя бы один оплаченный заказ» решается всеми тремя способами. Через JOIN:

SELECT u.name
FROM users u
JOIN orders o ON o.user_id = u.id AND o.status = 'paid'
ORDER BY u.id;

Выдача: Аня, Аня, Аня, Борис, Глеб. Аня трижды: у неё три оплаченных заказа, а соединение строит пары строк. Нужен список клиентов, дописывай DISTINCT или GROUP BY. IN и EXISTS дублей не дают по устройству, они проверяют условие, а не присоединяют строки. Механика размножения разобрана в статье про виды JOIN.

По читаемости выбор простой: EXISTS для вопроса про факт наличия, IN для готового списка значений, JOIN когда из второй таблицы нужны колонки в выдаче. По скорости выбирать не из чего. В PostgreSQL между IN и EXISTS разницы обычно нет: планировщик приводит обе формы к одному плану, и на этом наборе EXPLAIN для обоих запросов выдаёт идентичный Hash Semi Join по u.id = o.user_id. Смотреть надо на индекс по колонке связи, а не на выбор ключевого слова.

Типичные ошибки

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

Забытая связь: подзапрос молча стал некоррелированным. Убери WHERE x.user_id = o.user_id из коррелированного запроса выше, и внутри останется (SELECT avg(x.amount) FROM orders x), тот самый общий средний чек из первого примера статьи. Вместо одного заказа 11 вернутся три: 11, 12 и 13. Ошибки нет, отчёт построен, цифры неверные. Планкой стал общий средний вместо среднего по клиенту, и находят такое через месяц, когда кто-то сверяет отчёт руками. Проверка занимает секунду: вырежи подзапрос и выполни отдельно. Отработал сам по себе, значит связи в нём нет.

Подзапрос в SELECT там, где нужен JOIN. Признак в плане однозначный: SubPlan, у которого loops равен числу строк выдачи. Пока строк сотни, разницы не видно; на десятках тысяч тот же отчёт открывается минутами.

Когда подзапрос пора переписать на CTE

Запрос с производной таблицей читается наизнанку: чтобы понять, что такое a, приходится спускаться в середину FROM. WITH разворачивает его в порядок чтения.

WITH avg_per_user AS (
  SELECT user_id, avg(amount) AS avg_amount
  FROM orders
  GROUP BY user_id
)
SELECT o.id, o.user_id, o.amount
FROM orders o
JOIN avg_per_user a ON a.user_id = o.user_id
WHERE o.amount > a.avg_amount;

Выдача не изменилась, тот же единственный заказ 11. Выигрыш в имени: avg_per_user объясняет содержимое, а (SELECT ...) a заставляет его вычитывать. Переписывать стоит в двух случаях: вложенность дошла до второго уровня либо один и тот же подзапрос встречается дважды. Синтаксис WITH и рекурсивный вариант разобраны в статье про CTE.

Частые вопросы

Чем коррелированный подзапрос отличается от обычного

Ссылкой на внешний запрос. Некоррелированный копируется в отдельное окно и выполняется как есть, а коррелированный сам по себе не запустится: PostgreSQL ответит missing FROM-clause entry for table "o". Отсюда и разница в цене: первый считается один раз, второй для каждой строки.

Что быстрее, подзапрос или JOIN

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

Можно ли вкладывать подзапросы друг в друга

Синтаксически да. Упираешься при этом не в предел СУБД, а в читаемость: конструкцию SELECT ... FROM (SELECT ... FROM (SELECT ...)) тяжело разобрать даже автору через неделю. Со второго уровня вложенности переходи на CTE.

Где потренироваться

В пути «SQL для аналитиков» на Koddo подзапросы разбираются в финальном паке «Подзапросы и оконные функции». Первая задача так и называется, «Выше среднего чека»: тот самый скалярный подзапрос в WHERE, только на полном наборе заказов магазина, где среднее руками не подставишь. Решение проверяется автоматически.

Проверьте понимание в задаче на вторую по величине зарплату: запрос должен найти второй уникальный уровень и вернуть NULL, если его нет.