Подзапросы и CTE: запрос внутри запроса

← назад к разделу

Есть вопросы, на которые одним проходом по таблице не ответить. «Покажи заказы дороже среднего» — чтобы сравнить, надо сначала узнать это среднее. «Кто ещё ничего не заказывал» — сначала выяснить, у кого заказы есть. Ответ зависит от другого ответа, а в WHERE пишут условие, а не расчёт.

Так появляется подзапрос — обычный SELECT внутри другого запроса. Дальше: три места, куда его ставят, цена, которую за него платят, и WITH.

Подзапрос в WHERE: список и одно значение

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

Первый случай — список:

живой пример

SELECT id, status, total_amount
FROM orders
WHERE id IN (SELECT order_id FROM payments WHERE status = 'CAPTURED')
ORDER BY id;

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

Второй случай — одно значение; тогда подзапрос ставят прямо в сравнение:

живой пример

SELECT id, customer_id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders)
ORDER BY total_amount DESC;

Такой подзапрос называют скалярным: он обязан вернуть одну строку и колонку. Вернёт две — запрос упадёт с ошибкой «больше одной строки»; обычно так проявляется забытый WHERE или лишняя колонка.

Коррелированный подзапрос: «для каждой строки»

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

живой пример

SELECT c.id, c.last_name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders_count
FROM customer c
ORDER BY c.id;

Внутренний запрос ссылается на c.id — колонку внешнего. Заранее его не вычислить: у каждой строки ответ свой. Такой подзапрос называют коррелированным, и в этом вся цена: девять покупателей — девять прогонов по orders.

На девяти строках этого не видно, на ста тысячах — сто тысяч прогонов: запрос, мгновенный на стенде, на боевых объёмах висит минуту. Оптимизатор иногда переписывает такое в соединение сам, но полагаться на это нельзя (индексы и скорость).

EXISTS и NOT EXISTS: нужен факт, а не значение

Иногда значение не нужно вовсе: «есть ли у покупателя хоть один заказ» — это ответ «да» или «нет», а COUNT(*) ради этого досчитает все заказы до конца. EXISTS останавливается на первой найденной строке, а что стоит в его списке выбора, роли не играет — поэтому пишут SELECT 1.

Чаще нужна обратная проверка — строки, для которых пары нет:

живой пример

SELECT c.id, c.email
FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
ORDER BY c.id;

Двое без единого заказа: cus-07 и cus-08. Тот же вопрос напрашивается написать через NOT IN (SELECT customer_id FROM orders) — и вот это ловушка: один NULL в колонке подзапроса, и выдача пуста. Без ошибки — просто ноль строк. Разбор — в статье про NULL и типы; правило короткое: IN с подзапросом терпим, NOT IN меняем на NOT EXISTS.

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

Бывает, что сравнивать надо не с колонкой, а с результатом группировки. Классическая сверка: сумма позиций против суммы в самом заказе. Группировка даёт таблицу, которой в базе нет, — её и делают подзапросом в FROM:

живой пример

SELECT o.id, o.total_amount, s.items_total
FROM orders o
LEFT JOIN (SELECT order_id, SUM(quantity * unit_price) AS items_total
           FROM order_items GROUP BY order_id) s ON s.order_id = o.id
WHERE s.items_total IS NULL OR s.items_total <> o.total_amount
ORDER BY o.id;

Подзапрос в скобках — производная таблица: живёт только внутри запроса, и имя (здесь s) ей обязательно. Нашлись три заказа: ord-21, ord-22 и ord-23 числятся на 990, 1200 и 1500 рублей, а позиций у них нет вовсе. LEFT JOIN здесь не случаен — при обычном соединении заказы без позиций выпали бы из отчёта, то есть именно те, что мы искали.

WITH: тот же запрос, но сверху вниз

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

живой пример

WITH items AS (
    SELECT order_id, SUM(quantity * unit_price) AS amount
    FROM order_items
    GROUP BY order_id
),
mismatched AS (
    SELECT o.id, o.status, o.total_amount, i.amount AS items_amount
    FROM orders o
    LEFT JOIN items i ON i.order_id = o.id
    WHERE i.amount IS NULL OR i.amount <> o.total_amount
)
SELECT * FROM mismatched ORDER BY id;

Именованный шаг называют CTE — общим табличным выражением. Читается сверху вниз: items — сколько стоят позиции каждого заказа, mismatched — заказы, где сумма не сошлась с total_amount, последняя строка — что показать. Шаг видит предыдущие, поэтому «посчитали, отфильтровали, отсортировали» пишется в том порядке, в каком о нём думают.

Результат тот же, что у производной таблицы; меняется читаемость — и отладка: поставьте вместо хвоста SELECT * FROM items и увидите, что дал первый шаг.

Когда подзапрос лучше заменить на JOIN

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

живой пример

SELECT c.id, c.last_name, COUNT(o.id) AS orders_count
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.last_name
ORDER BY c.id;

Числа те же, включая нули у cus-07 и cus-08: их сохраняет LEFT JOIN. Правило простое: подзапрос, который проверяет (IN, EXISTS), оставляем; подзапрос, который тянет колонки в список выбора по одной, переписываем соединением. Обратная замена тоже нужна: соединение со стороны «многих» задваивает строки, и сумма после него врёт, а EXISTS строк не добавляет никогда.

Коротко

• Подзапрос отвечает на вопрос, зависящий от другого ответа: сначала среднее — потом сравнение.
• В WHERE подзапрос даёт список для IN (одна колонка) или одно значение для сравнения.
• Коррелированный подзапрос выполняется для каждой строки внешнего: сто тысяч строк — сто тысяч прогонов.
• EXISTS проверяет факт и останавливается на первой строке; NOT IN с подзапросом меняем на NOT EXISTS.
• Подзапрос в FROM — производная таблица; имя ей обязательно.
• WITH меняет не результат, а порядок чтения: именованные шаги сверху вниз.

Что почитать дальше

• JOIN: собрать данные из нескольких таблиц — чем соединение отличается от подзапроса.
• Агрегаты: COUNT, GROUP BY и HAVING — чаще всего внутри подзапроса стоит именно это.
• NULL и типы данных — полный разбор ловушки NOT IN.
• Почему запрос медленный: индексы на пальцах — во что обходятся лишние проходы.

Что решать

Запросы пишутся в песочнице маркетплейса, не уходя со статьи.