Подзапросы (subqueries): вложенные SELECT и их применение

Введение

Подзапрос — SELECT-запрос, вложенный внутрь другого запроса (в WHERE, SELECT, FROM, или HAVING), позволяющий выразить условия или вычисления, опирающиеся на результат отдельного, промежуточного запроса. В статье разберём основные виды применения подзапросов и их альтернативу — JOIN (статья 398), сравнивая, когда какой подход уместнее.

Концепция

Скалярный подзапрос возвращает единственное значение (одна строка, один столбец) и может использоваться везде, где ожидается обычное значение (в SELECT, в сравнении WHERE). Подзапрос в WHERE ... IN (...) возвращает набор значений для проверки принадлежности. Коррелированный подзапрос ссылается на столбцы внешнего запроса внутри себя, выполняясь логически (хотя не обязательно физически, в зависимости от оптимизации СУБД) отдельно для каждой строки внешнего запроса, что делает его потенциально значительно медленнее обычного, некоррелированного подзапроса.

Пример кода: Подзапросы (subqueries)

-- Скалярный подзапрос в SELECT — добавление вычисляемого столбца на основе отдельного запроса
SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- Подзапрос в WHERE с IN — отбор клиентов, у которых есть заказы дороже 5000
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total_amount > 5000);

-- Коррелированный подзапрос — ссылается на c.id из внешнего запроса
SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total_amount > 5000
);
-- EXISTS часто эффективнее IN для коррелированных подзапросов — СУБД может остановиться
-- на первом найденном совпадении, не вычисляя полный список значений

-- Подзапрос в FROM (производная таблица) — промежуточный результат используется как таблица
SELECT customer_summary.customer_id, customer_summary.total_spent
FROM (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS customer_summary
WHERE customer_summary.total_spent > 10000;

Пояснения к коду

Скалярный подзапрос (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) — коррелированный (ссылается на c.id из внешнего запроса по клиентам), выполняемый логически отдельно для каждого клиента, что для большого количества клиентов может быть значительно медленнее эквивалентного запроса через LEFT JOIN с GROUP BY (статьи 398-399), хотя современные оптимизаторы запросов часто способны преобразовать такой подзапрос во внутренне эквивалентный, более эффективный JOIN автоматически. EXISTS вместо IN для коррелированного подзапроса — практичная оптимизация: EXISTS логически проверяет лишь «есть хотя бы одна подходящая строка» и может прерваться сразу при нахождении первой, тогда как IN в некоторых случаях (особенно для коррелированных подзапросов в более старых версиях некоторых СУБД) требовал вычисления полного списка значений перед проверкой принадлежности.

Подводные камни

Использование коррелированного подзапроса там, где эквивалентный JOIN дал бы тот же результат значительно эффективнее — хотя современные оптимизаторы запросов часто умеют автоматически преобразовывать одно в другое, это не гарантировано для всех случаев и всех СУБД, и для критичных по производительности запросов стоит явно сравнить производительность обоих вариантов через EXPLAIN (статья 416), а не полагаться исключительно на способность оптимизатора находить оптимальное преобразование самостоятельно.
Использование IN с подзапросом, который теоретически может вернуть NULL значения — если подзапрос для IN может вернуть NULL среди прочих значений (например, из-за внешнего ключа, допускающего NULL), поведение NOT IN с таким набором значений становится неожиданным: NOT IN с любым NULL в списке сравнения возвращает NULL (не TRUE) для каждой строки, что эффективно исключает абсолютно все строки из результата, даже те, что логически должны были пройти проверку — это известная, коварная особенность стандарта SQL, требующая явной фильтрации NULL из подзапроса (WHERE customer_id IS NOT NULL внутри подзапроса) при использовании NOT IN.
Чрезмерное усложнение запроса множеством вложенных подзапросов вместо использования более читаемых CTE (Common Table Expressions, статья 405) для логически разделяемых, поэтапных вычислений — глубоко вложенные подзапросы (подзапрос внутри подзапроса внутри подзапроса) значительно труднее читать и поддерживать, чем последовательность явно поименованных CTE-блоков, выполняющих логически те же вычисления поэтапно.
Предположение, что подзапрос в FROM (производная таблица) всегда материализуется СУБД как промежуточный временный результат перед использованием во внешнем запросе — на практике многие современные оптимизаторы способны «встроить» (inline) логику подзапроса непосредственно в план выполнения внешнего запроса без создания реального промежуточного результата, и реальное поведение конкретного запроса в конкретной СУБД стоит проверять через EXPLAIN, а не предполагать заранее на основе синтаксической структуры запроса.