CTE vs подзапрос — что лучше и когда что использовать

Коротко

CTE (Common Table Expression) — именованный временный набор данных, определённый через WITH. Подзапрос — вложенный SELECT внутри основного запроса. Оба решают одни и те же задачи, но CTE выигрывает в читаемости, а подзапросы иногда — в производительности.

Как выглядит CTE

WITH active_users AS (
  SELECT user_id, COUNT(*) AS sessions
  FROM sessions
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, a.sessions
FROM users u
JOIN active_users a ON u.user_id = a.user_id
WHERE a.sessions > 10;

CTE определяется один раз и используется по имени — как виртуальная таблица.

Как выглядит подзапрос

SELECT u.name, a.sessions
FROM users u
JOIN (
  SELECT user_id, COUNT(*) AS sessions
  FROM sessions
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
) a ON u.user_id = a.user_id
WHERE a.sessions > 10;

Тот же результат, но подзапрос встроен прямо в JOIN.

Ключевые отличия

	CTE (WITH)	Подзапрос
Читаемость	Высокая (именованный, линейный)	Ниже (вложенный)
Переиспользование	Можно ссылаться несколько раз	Каждый раз писать заново
Рекурсия	Поддерживает (WITH RECURSIVE)	Не поддерживает
Производительность	Зависит от СУБД	Зависит от СУБД
Вложенность	Линейная цепочка	Может быть глубоко вложенным

Когда CTE лучше

1. Сложные запросы с несколькими шагами

WITH daily_revenue AS (
  SELECT DATE_TRUNC('day', created_at) AS day,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
),
weekly_avg AS (
  SELECT day, revenue,
    AVG(revenue) OVER (ORDER BY day ROWS 6 PRECEDING) AS rolling_avg
  FROM daily_revenue
)
SELECT * FROM weekly_avg WHERE revenue > rolling_avg * 1.5;

Каждый шаг — отдельный CTE с понятным именем. Читается сверху вниз.

2. Повторное использование

WITH user_stats AS (
  SELECT user_id, COUNT(*) AS orders, SUM(amount) AS total
  FROM orders GROUP BY user_id
)
SELECT 'high_value' AS segment, COUNT(*) FROM user_stats WHERE total > 10000
UNION ALL
SELECT 'low_value', COUNT(*) FROM user_stats WHERE total <= 10000;

user_stats используется дважды. С подзапросами пришлось бы дублировать код.

3. Рекурсия

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, t.level + 1
  FROM employees e JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree;

Рекурсивные CTE — единственный способ обходить деревья и графы в SQL.

Когда подзапрос лучше

Простые одноразовые вычисления:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Для таких простых случаев CTE — overkill.

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

В PostgreSQL CTE до версии 12 всегда материализовались (вычислялись отдельно, даже если это неэффективно). С PostgreSQL 12+ оптимизатор может инлайнить CTE как подзапрос.

Вопросы с собеседований

• «Когда вы используете CTE, а когда подзапрос?» — CTE для сложных многошаговых запросов и переиспользования. Подзапрос для простых одноразовых фильтров.
• «CTE создаёт временную таблицу?» — Нет, это не таблица. CTE существует только во время выполнения запроса.
• «Можно ли в CTE использовать INSERT/UPDATE?» — Да, через writable CTE (WITH ... AS (DELETE ... RETURNING *)).

FAQ

CTE замедляет запрос?

В современных СУБД (PostgreSQL 12+, MySQL 8+) — обычно нет. Оптимизатор инлайнит CTE при необходимости. В старых версиях CTE всегда материализуется, что может быть как плюсом, так и минусом.

Сколько CTE можно использовать в одном запросе?

Без ограничений. WITH a AS (...), b AS (...), c AS (...) SELECT ... — каждый следующий CTE может ссылаться на предыдущие.

CTE — это то же самое, что временная таблица?

Нет. Временная таблица (CREATE TEMP TABLE) хранится на диске и живёт до конца сессии. CTE существует только во время выполнения одного запроса.