CTE vs подзапрос: разница и что выбрать на собесе

CTE (Common Table Expression, WITH) и подзапросы решают одну задачу — выделить промежуточный результат. CTE именованы и могут использоваться несколько раз, подзапросы — вложены и одноразовы. На собесе оценивают, понимаете ли вы разницу в читаемости и плане выполнения.

Если коротко. CTE — именованный кусок логики в WITH, можно переиспользовать и рекурсировать. Подзапрос — одноразовый вложенный SELECT.

Проверь себя · 1/3разбор после ответа

В одном отчёте вы считаете несколько метрик по событиям: dau, wau и число покупок. У всех метрик один и тот же фильтр: только продакшн-трафик и только выбранный период. Какой подход лучше защищает от ситуации, когда в одной метрике забыли часть фильтра?

CTE vs Подзапрос: таблица различий

Параметр	CTE	Подзапрос
Именованный	Да	Нет
Переиспользование	Да, в рамках запроса	Нет, только инлайн
Рекурсия	Да (`WITH RECURSIVE`)	Нет
Читаемость	Высокая (видна декомпозиция)	Падает с вложенностью
План выполнения	Может матерализоваться (PG)	Обычно инлайнится

Когда использовать CTE

Логику нужно использовать несколько раз в запросе.
Запрос длинный и нужна читаемость — CTE декомпозирует.
Нужна рекурсия (WITH RECURSIVE).

Когда использовать Подзапрос

Логика короткая и используется один раз.
Это коррелированный подзапрос (зависит от внешнего ряда).
Хотите подзапрос как скалярное значение в SELECT/WHERE.

На примере

CTE — читаемая декомпозиция

WITH active AS (
  SELECT user_id FROM users WHERE status = 'active'
),
big AS (
  SELECT user_id FROM orders WHERE amount > 1000
)
SELECT * FROM active a JOIN big b USING (user_id);

Подзапрос — компактно

SELECT *
FROM (SELECT user_id FROM users WHERE status = 'active') AS active
JOIN (SELECT user_id FROM orders WHERE amount > 1000) AS big USING (user_id);

Ловушка на собесе

В Postgres до версии 12 CTE были «оптимизационным барьером» — оптимизатор не пушил предикаты внутрь. Стоит знать, если идёте на собес в команду с PG <12. С 12+ — поведение как у подзапросов.

Тренировать SQL в Telegram