Подзапросы и CTE в SQL: как писать читаемые запросы

Короткий ответ

• Подзапрос — запрос внутри другого запроса: в WHERE, SELECT или FROM. Хорош, когда логика умещается в одну строку.
• CTE (Common Table Expression, WITH) — именованный промежуточный результат перед основным запросом. Хорош, когда шагов несколько и их нужно читать по порядку.
• Рекурсивный CTE (WITH RECURSIVE) — для деревьев и графов: категории, оргструктура, цепочки рефералов.

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

Виды подзапросов

-- Скалярный: одно значение
SELECT name, price, price - (SELECT AVG(price) FROM products) AS diff
FROM products;

-- В условии: EXISTS / IN
SELECT c.*
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Производная таблица в FROM
SELECT city, avg_total
FROM (SELECT city, AVG(total) AS avg_total FROM orders GROUP BY city) t
WHERE avg_total > 500;

Коррелированный подзапрос ссылается на внешний запрос (как o.customer_id = c.id выше) и логически выполняется для каждой строки. Планировщик часто превращает его в соединение, но не всегда.

Тот же отчёт через CTE

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

С вложенными подзапросами:

SELECT customer_id, spent
FROM (SELECT customer_id, SUM(total) AS spent
      FROM orders WHERE created_at >= '2026-01-01'
      GROUP BY customer_id) s
WHERE spent > (SELECT AVG(spent) FROM
      (SELECT SUM(total) AS spent FROM orders
       WHERE created_at >= '2026-01-01' GROUP BY customer_id) x);

Через CTE:

WITH spending AS (
  SELECT customer_id, SUM(total) AS spent
  FROM orders
  WHERE created_at >= '2026-01-01'
  GROUP BY customer_id
),
avg_spending AS (
  SELECT AVG(spent) AS avg_spent FROM spending
)
SELECT s.customer_id, s.spent
FROM spending s, avg_spending a
WHERE s.spent > a.avg_spent;

Логика описана один раз, шаги названы, запрос читается сверху вниз. Каждый CTE можно отладить отдельно, запустив его как обычный SELECT.

Рекурсивный CTE: иерархия

Таблица categories(id, parent_id, name). Нужно получить категорию «Электроника» со всеми вложенными подкатегориями и уровнем вложенности:

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM categories
  WHERE id = 10                      -- корень
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id  -- дети найденных
  WHERE t.depth < 20                 -- защита от зацикливания
)
SELECT * FROM tree ORDER BY depth, name;

Как это работает: первая часть (якорь) находит корень, вторая (рекурсивная) на каждом шаге добавляет детей уже найденных строк, пока новые строки не перестанут появляться. Так же строится цепочка «сотрудник — руководитель — его руководитель».

Если в данных возможен цикл (A ссылается на B, B на A), без ограничения глубины запрос не завершится. В MySQL дополнительно есть лимит cte_max_recursion_depth.

Производительность

• В PostgreSQL 12+ обычный CTE, который используется один раз, встраивается в основной запрос и оптимизируется так же, как подзапрос. Управлять этим можно явно: WITH x AS MATERIALIZED (...) или NOT MATERIALIZED.
• В старых версиях PostgreSQL CTE всегда материализовался — это мешало использовать индексы снаружи.
• Материализация полезна, если тяжёлый CTE используется в запросе несколько раз.
• NOT IN с подзапросом опасен: если подзапрос вернёт хотя бы один NULL, результат будет пустым. Используйте NOT EXISTS.
• Коррелированный подзапрос в SELECT по большой таблице может выполняться построчно — проверьте план через EXPLAIN.

Как выбрать