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

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

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

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

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

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

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

Статус CAPTURED здесь из таблицы payments: у платежей своя шкала статусов, не та, что у заказов, где оплаченный заказ называется PAID.

живой пример

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.

На девяти строках этого не видно, на ста тысячах — сто тысяч прогонов: запрос, мгновенный на стенде, на боевых объёмах висит минуту. Ждать спасения от оптимизатора тут не стоит: подзапрос в списке выбора PostgreSQL в соединение не переписывает — в плане он так и остаётся отдельным шагом, который выполняется на каждой строке. Переписать он умеет другое: IN и EXISTS в WHERE превращаются в один проход по второй таблице. Посмотреть на это можно самому через EXPLAIN (индексы и скорость).

Отработать здесь: Покупатели с зависшей оплатой

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 в колонке подзапроса, и выдача пуста. Без ошибки — просто ноль строк. Запустите NOT IN на нашей песочнице — и он честно отработает: orders.customer_id там объявлен NOT NULL, пустым ему быть нечем. Это и есть самое неприятное: на учебных данных правило выглядит преувеличением, а захлопнется ловушка на первой же колонке, где 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 items_total
    FROM order_items
    GROUP BY order_id
),
mismatched AS (
    SELECT o.id, o.status, o.total_amount, i.items_total
    FROM orders o
    LEFT JOIN items i ON i.order_id = o.id
    WHERE i.items_total IS NULL OR i.items_total <> o.total_amount
)
SELECT * FROM mismatched ORDER BY id;

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

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

Две вещи про WITH, которые стоит знать заранее.

Первая — миф «CTE тормозит». Он родом из старых версий: до PostgreSQL 12 именованный шаг всегда выполнялся отдельно и складывался во временный набор, а условия снаружи внутрь не проталкивались — запрос с WITH и правда мог оказаться медленнее такого же с подзапросом. С версии 12 обычный шаг по умолчанию встраивается в запрос, и план получается тем же. Управлять этим можно явно: WITH items AS MATERIALIZED (…) заставляет посчитать шаг один раз — полезно, когда он дорогой и используется дважды; AS NOT MATERIALIZED наоборот требует встроить.

Вторая — WITH RECURSIVE: шаг, который ссылается сам на себя и потому умеет разворачивать цепочки. Так обходят деревья: категории с подкатегориями, руководитель и все его подчинённые, цепочка заказов-замен через parent_order_id. В этом цикле рекурсия не понадобится, но имя стоит узнавать: увидели WITH RECURSIVE — запрос идёт по иерархии, а не по таблице.

LATERAL: подзапросу нужна строка слева

Производная таблица в FROM считается сама по себе и строк рядом не видит. Поэтому вопрос «дай по три последних заказа каждого покупателя» через неё не пишется: подзапросу нужен customer_id из внешней строки, а взять его неоткуда. Ключевое слово LATERAL это разрешает — подзапрос выполняется для каждой строки слева и видит её колонки:

живой пример

SELECT c.id, c.last_name, o.id AS order_id, o.created_at
FROM customer c
LEFT JOIN LATERAL (
    SELECT id, created_at
    FROM orders
    WHERE customer_id = c.id
    ORDER BY created_at DESC
    LIMIT 3
) o ON true
ORDER BY c.id, o.created_at DESC;

Читается как обычное соединение, только справа не таблица, а запрос, который пересчитывается для каждого покупателя. ON true стоит потому, что условие связи уже записано внутри подзапроса. LEFT JOIN LATERAL сохраняет покупателей без заказов — cus-07 и cus-08 останутся в выдаче с пустыми колонками; обычный JOIN LATERAL их выбросит.

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

Когда подзапрос лучше заменить на 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 строк не добавляет никогда.

И ещё одно различие, которое решает чаще, чем читаемость, — дубли. Подзапросу в IN и EXISTS всё равно, сколько раз значение встречается внутри: он проверяет факт, и строка снаружи остаётся одна. Соединение выдаёт строку на каждую пару, поэтому покупатель с семью заказами после JOIN orders встретится семь раз, и любая сумма по его стороне вырастет всемеро. Короткое правило: «проверить наличие» — это IN и EXISTS, «взять данные» — соединение.

Глубже: UNION, UNION ALL и EXCEPT: сложить две выдачирасширенноеспросят на собеседовании

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

живой пример

SELECT customer_id FROM orders WHERE status = 'CANCELLED'
UNION
SELECT customer_id FROM orders WHERE status = 'REFUNDED';

UNION склеивает выдачи и убирает повторы, для этого он сортирует всё; UNION ALL просто дописывает вторую выдачу под первую и работает быстрее. Если повторы невозможны или не мешают, пишут ALL. Число и типы колонок в обоих запросах должны совпадать, имена берутся из первого. ORDER BY ставят один раз, в самом конце, он относится ко всему результату.

Две родственные операции. EXCEPT оставляет строки первой выдачи, которых нет во второй: покупатели, которые делали заказы, но ни разу не возвращали. INTERSECT оставляет общие. Обе часто заменяют NOT EXISTS и EXISTS из этой статьи, и выбор между ними дело вкуса, пока выдачи невелики: EXCEPT сравнивает целые строки, NOT EXISTS умеет условие любой формы.

Коротко

• Подзапрос отвечает на вопрос, зависящий от другого ответа: сначала среднее — потом сравнение.
• В WHERE подзапрос даёт список для IN (одна колонка) или одно значение для сравнения.
• Коррелированный подзапрос выполняется для каждой строки внешнего: сто тысяч строк — сто тысяч прогонов.
• EXISTS проверяет факт и останавливается на первой строке; NOT IN с подзапросом меняем на NOT EXISTS.
• Подзапрос в FROM — производная таблица; имя ей обязательно.
• WITH меняет не результат, а порядок чтения: именованные шаги сверху вниз.
• Две выдачи с одинаковыми колонками складывают UNION (без повторов) или UNION ALL (быстрее); EXCEPT вычитает, INTERSECT пересекает.
• LATERAL даёт подзапросу видеть строку слева: «по три последних заказа каждому» пишется соединением, а не окном.
• CTE с PostgreSQL 12 встраивается в запрос, AS MATERIALIZED возвращает старое поведение; WITH RECURSIVE разворачивает иерархии.

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

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