CTE и WITH в SQL: как работают и когда нужны

CTE (Common Table Expression, «обобщённое табличное выражение») — это именованный временный результат запроса, который объявляется через ключевое слово WITH перед основным запросом и используется дальше как обычная таблица. В отличие от подзапроса в FROM, CTE объявляется один раз с понятным именем и может использоваться в основном запросе многократно, а сложный запрос читается сверху вниз, шаг за шагом — а не как гнездо вложенных скобок, которое нужно разбирать от центра наружу.

WITH big_orders AS (
SELECT user_id, amount
FROM orders
WHERE amount > 10000
)
SELECT user_id, COUNT(*) AS orders_count
FROM big_orders
GROUP BY user_id;

big_orders — это CTE: результат внутреннего запроса получает имя и дальше используется в SELECT так, будто это обычная таблица. Тот же результат можно получить подзапросом в FROM, но с CTE запрос читается линейно: сначала видно, что такое big_orders, потом — что с ним делают. Чем длиннее цепочка преобразований, тем заметнее разница в читаемости.

WITH orders_2026 AS (
SELECT * FROM orders WHERE created_at >= '2026-01-01'
),
big_orders_2026 AS (
SELECT * FROM orders_2026 WHERE amount > 10000
)
SELECT user_id, SUM(amount) AS total
FROM big_orders_2026
GROUP BY user_id;

После одного WITH можно объявить несколько CTE через запятую, и каждый следующий CTE может ссылаться на предыдущие. Это разбивает сложную логику на именованные шаги — сначала отфильтровали заказы за 2026 год, потом среди них выбрали крупные — вместо одного длинного запроса с несколькими уровнями вложенности.

WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers;

Рекурсивный CTE (WITH RECURSIVE) — это CTE, который в своей рекурсивной части обращается к самому себе и повторяет вычисление, пока не выполнится условие остановки. Он устроен из двух частей, соединённых UNION ALL: базовый случай (SELECT 1 AS n) — стартовая точка, и рекурсивная часть (SELECT n + 1 FROM numbers WHERE n < 5) — она ссылается на сам CTE и повторяется, пока условие WHERE n < 5 не перестанет давать новые строки. Без условия остановки в рекурсивной части запрос уйдёт в бесконечный цикл — это главная ошибка новичков с WITH RECURSIVE.
Практический пример WITH RECURSIVE — построение иерархии сотрудников по цепочке руководителей:
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE id = 1

UNION ALL

SELECT e.id, e.name, e.manager_id, s.level + 1
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates
ORDER BY level;

Базовый случай берёт конкретного сотрудника (id = 1) с уровнем 1. Рекурсивная часть на каждом шаге находит сотрудников, чей manager_id совпадает с id уже найденных, и увеличивает уровень на 1 — так строится вся цепочка подчинённых вниз по иерархии, сколько бы уровней вложенности в ней ни было.

Главное отличие — не производительность, а структура запроса.
— Именование. Подзапрос в FROM анонимный, существует только в одном месте запроса. CTE именуется через WITH, имя можно использовать повторно.
— Повторное использование в одном запросе. С подзапросом логику приходится повторять или выносить отдельно. С CTE можно сослаться на одно и то же имя несколько раз.
— Рекурсия. Через обычный подзапрос невозможна. Через CTE поддерживается с помощью WITH RECURSIVE.
— Читаемость при длинной цепочке преобразований. Подзапросы — это вложенные скобки, которые читаются от центра наружу. CTE — линейная последовательность именованных шагов.

Как расшифровывается CTE? Common Table Expression — «обобщённое табличное выражение». В русскоязычных материалах чаще встречается просто «CTE» или «конструкция WITH».
CTE — это то же самое, что временная таблица (temporary table)? Нет. Временная таблица создаётся отдельной командой, физически существует в рамках сессии и может использоваться в нескольких запросах подряд. CTE существует только в рамках одного запроса, в котором объявлен через WITH, и не сохраняется после его выполнения.
Все СУБД поддерживают CTE? Нет, не все и не всегда поддерживали. PostgreSQL, MySQL начиная с версии 8.0, SQL Server и Oracle поддерживают и обычные, и рекурсивные CTE. В MySQL до версии 8.0 конструкции WITH не было — там логику приходилось выражать через подзапросы или временные таблицы.
Что будет, если в WITH RECURSIVE забыть условие остановки? Запрос уйдёт в бесконечный цикл (или упрётся в лимит рекурсии, если СУБД его задаёт) — база будет добавлять всё новые строки, пока не закончится память или не сработает защитный лимит. Рекурсивная часть обязательно должна на каком-то шаге перестать давать новые строки.

Читать про WITH — одно, а самостоятельно написать рекурсивный CTE, который не уходит в бесконечный цикл, — совсем другое. Потренироваться на реальных задачах, от простого именованного подзапроса до многоуровневой рекурсии по иерархии, можно на онлайн-тренажёре SQL Arena от Quality Academy — запросы пишутся прямо в браузере, AI-ментор подсказывает, если условие остановки рекурсии забыто, а диалект переключается между PostgreSQL, MySQL и ClickHouse, так что разницу в поддержке CTE между версиями MySQL, о которой шла речь выше, можно проверить не только в теории. Больше 800 задач, значительная часть — бесплатно.

-------

Сайт Quality Academy:
https://quality-academy.ru/main
Telegram-канал:
https://t.me/quality_academy
YouTube-канал:
https://www.youtube.com/@quality_academy
ВКонтакте:
https://vk.com/quality_academy
Канал отзывов учеников (58+ отзывов):
https://t.me/quality_academy_reviews
Задать вопрос менеджеру:
https://t.me/quality_academy_bot
Тест «Подойдёт ли вам тестирование»:
https://quiz.quality-academy.ru
5000+ вопросов с собесов тестировщика — бот-тренажёр:
https://t.me/quality_academy_interview_bot
3 практические задачи и дорожная карта:
https://t.me/quality_academy_tasks_bot
Бесплатные тренажёры — SQL Arena, Playwright Arena, Python Arena:
SQL Arena · Playwright Arena · Python Arena