Это часть 6 из 10 учебника «SQL с нуля для аналитика». Содержание серии в конце поста. ← Часть 5
TL;DR: Подзапрос — это SELECT внутри другого SELECT. Может быть во FROM (подзапрос-таблица), WHERE (фильтр-условие), SELECT (значение колонки). CTE (WITH name AS (SELECT ...)) — именованный подзапрос, читается сверху вниз. Для сложных запросов CTE предпочтительнее.
В этой части:
- 3 типа подзапросов (FROM, WHERE, SELECT)
- Что такое CTE и зачем он
- Подзапрос vs CTE — что выбрать
- Производительность: ловушки и оптимизация
- Когда нужна рекурсивная CTE
Что такое подзапрос?
Подзапрос — SELECT внутри другого SELECT, в скобках.
Простой пример: «юзеры с выручкой > среднего»:
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > (
SELECT AVG(user_total)
FROM (
SELECT user_id, SUM(amount) AS user_total
FROM orders
GROUP BY user_id
) sub
);Подзапрос вычисляет среднюю выручку на юзера. Главный запрос фильтрует юзеров выше неё.
Где может быть подзапрос — 3 места?
| Место | Пример | Что делает |
|---|---|---|
| FROM | FROM (SELECT ... ) sub | Подзапрос как таблица |
| WHERE | WHERE col IN (SELECT ... ) | Фильтр-условие |
| SELECT | SELECT (SELECT COUNT(*) ...) | Значение колонки |
Подзапрос в FROM
-- Сначала агрегируем заказы, потом JOIN с юзерами
SELECT u.email, sub.total
FROM users u
LEFT JOIN (
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
) sub ON u.id = sub.user_id;Подзапрос в WHERE
-- Юзеры, у которых были заказы в 2026
SELECT * FROM users
WHERE id IN (
SELECT DISTINCT user_id
FROM orders
WHERE created_at >= '2026-01-01'
);Подзапрос в SELECT (correlated)
-- Для каждого юзера — его кол-во заказов
SELECT
u.email,
(SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS orders_count
FROM users u;Correlated subquery считается для каждой строки — медленно на больших данных. Лучше JOIN.
Что такое CTE и зачем он?
CTE = Common Table Expression. Это именованный подзапрос в виде WITH name AS (SELECT ...). Читается сверху вниз, не вложено.
!CTE flow: каждая WITH — отдельный шаг сверху вниз, финальный SELECT собирает результат
Тот же запрос «юзеры с выручкой > среднего» через CTE:
WITH user_totals AS (
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
),
avg_total AS (
SELECT AVG(total) AS avg_t FROM user_totals
)
SELECT t.user_id, t.total
FROM user_totals t
CROSS JOIN avg_total a
WHERE t.total > a.avg_t;В 10 раз читаемее чем nested subquery. По шагам сверху вниз.
Типичный случай первого CTE: ad-hoc запрос вырос до 80 строк с тремя уровнями вложенности. Переписали как 4 шага через WITH — стал читаемым, ревью прошло за 5 минут (вместо 30).
Какие 4 причины брать CTE вместо подзапросов?
- Читаемость — сверху вниз, как программа
- Повторное использование — одну CTE можно ссылаться несколько раз
- Отладка — можно SELECT \* FROM cte_name чтобы посмотреть результат
- Recursive CTE — обход иерархий (см. ниже)
Минусы CTE:
- В PostgreSQL до версии 12 CTE был optimization fence (медленнее). С PG 12+ — equivalent подзапросу.
- В MySQL CTE появилось только с 8.0
- В очень больших dbt-моделях CTE может «съесть» план оптимизатора. Тогда — materialized view.
Можно ли использовать одну CTE несколько раз?
Да:
WITH paid_orders AS (
SELECT * FROM orders WHERE status = 'paid'
)
SELECT
(SELECT COUNT(*) FROM paid_orders) AS total_count,
(SELECT SUM(amount) FROM paid_orders) AS total_amount,
(SELECT AVG(amount) FROM paid_orders) AS avg_check;Полезно для dry: не повторять условие WHERE status = 'paid' три раза.
Что такое recursive CTE?
Recursive CTE — CTE, которая ссылается на саму себя. Используется для обхода иерархий (организационная структура, BOM, граф ссылок).
-- Найти всех подчинённых руководителя id=1 (включая через несколько уровней)
WITH RECURSIVE subordinates AS (
-- Anchor: прямые подчинённые
SELECT id, manager_id, name
FROM employees
WHERE manager_id = 1
UNION ALL
-- Recursive: подчинённые подчинённых
SELECT e.id, e.manager_id, e.name
FROM employees e
INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;Подробнее — в CTE рекурсивные SQL.
Какие 4 типичные ошибки с подзапросами и CTE?
- Ошибка 1: Correlated subquery в SELECT для больших таблиц. Медленно. Перепиши через JOIN.
- Ошибка 2: NOT IN с подзапросом, который вернёт NULL. Главный запрос вернёт пусто. Используй
NOT EXISTSилиWHERE x NOT IN (SELECT ... WHERE col IS NOT NULL). - Ошибка 3: CTE без алиаса колонки → колонки наследуют имена из подзапроса. Иногда плохо читается.
- Ошибка 4: Очень глубокая вложенность подзапросов. 5+ уровней — переделай через CTE.
Типичный случай:WHERE x NOT IN (SELECT y FROM ...)возвращает пусто — потому что в подзапросе попался NULL. Переписали наWHERE NOT EXISTS (SELECT 1 WHERE ...)— заработало.
Частые вопросы про подзапросы и CTE
CTE медленнее подзапроса?
В современных PG (12+) — нет. В MySQL до 8.0 не было CTE вообще. В ClickHouse CTE есть с 23.x.
EXISTS или IN — что быстрее?
EXISTS обычно быстрее на больших таблицах (стопает на первой найденной строке). IN загружает все значения в memory.
Можно ли в CTE INSERT/UPDATE?
В PG — да, через WITH x AS (DELETE FROM ... RETURNING *). Аналитик такое почти не пишет.
Сколько CTE можно в одном запросе?
Сколько угодно. Норма для сложного аналитического запроса — 5-15 CTE.
dbt — это про CTE?
Во многом да. Каждая dbt-модель = один SELECT с многими CTE сверху. См. dbt macros.
Что дальше?
В Части 7 — NULL, типы данных, COALESCE, CAST, CASE WHEN. NULL — главный источник багов в аналитике, разберём все правила.
В Pro — безлимит мок-собесов на AI-интервью + 491 SQL-задача + 612 тестовых заданий + 50+ блог-постов.
Навигация по учебнику
← Часть 5 | Часть 6: Подзапросы и CTE | Часть 7 →
Содержание серии: 1 · 2 · 3 · 4 · 5 · 6 · 7 · 8 · 9 · 10
См. также CTE рекурсивные запросы SQL для углубления.
Источники
- PostgreSQL Docs: «WITH Queries» (postgresql.org/docs/current/queries-with.html)
- PostgreSQL Docs: «Subqueries» (postgresql.org/docs/current/functions-subquery.html)