CTE в SQL: обобщённые табличные выражения простыми словами

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

MySQL 8.1
WITH имя_cte AS (
    SELECT ...
)
SELECT * FROM имя_cte;

По сути CTE — это подзапрос, которому дали имя и вынесли в начало запроса. Этот простой приём кардинально меняет читаемость: сложный запрос превращается в последовательность понятных шагов.

Подзапрос, у которого появилось имя

Найдём заказы дороже среднего чека в базе данных сервиса доставки. Через подзапрос это выглядит так:

MySQL 8.1
SELECT order_id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders)
ORDER BY total_amount DESC, order_id
LIMIT 5;

А теперь то же самое через CTE:

MySQL 8.1
WITH avg_check AS (
    SELECT AVG(total_amount) AS avg_amount FROM orders
)
SELECT order_id, total_amount
FROM orders
WHERE total_amount > (SELECT avg_amount FROM avg_check)
ORDER BY total_amount DESC, order_id
LIMIT 5;

На таком коротком запросе выигрыш ещё не очевиден. Он появляется, когда шагов становится больше одного.

Несколько CTE в одном запросе

CTE можно объявлять цепочкой через запятую, и каждый следующий вправе использовать предыдущие. Найдём товары, которые приносят выручки больше, чем в среднем товары их категории:

MySQL 8.1
WITH product_revenue AS (
    SELECT p.category, p.name, SUM(o.total_amount) AS revenue
    FROM orders o
    JOIN products p ON p.product_id = o.product_id
    GROUP BY p.category, p.name
),
category_avg AS (
    SELECT category, AVG(revenue) AS avg_revenue
    FROM product_revenue
    GROUP BY category
)
SELECT pr.name, pr.category, pr.revenue, ROUND(ca.avg_revenue, 2) AS category_avg
FROM product_revenue pr
JOIN category_avg ca ON ca.category = pr.category
WHERE pr.revenue > ca.avg_revenue
ORDER BY pr.category, pr.revenue DESC;

Запрос читается сверху вниз, как рецепт: сначала считаем выручку по товарам, затем среднюю по категориям, в конце сравниваем. Тот же запрос на вложенных подзапросах пришлось бы читать изнутри наружу — а выручку по товарам, которая используется дважды, написать два раза. Запрос можно менять прямо на странице: поверните сравнение в WHERE на < — и увидите товары, отстающие от среднего по своей категории.

CTE, подзапрос или временная таблица

Все три инструмента решают похожую задачу — «положить промежуточный результат куда-то». Разница в области жизни и возможностях:

Практическое правило: подзапрос — для одноразовой мелочи, CTE — для читаемости и повторного использования внутри запроса, временная таблица — когда результат нужен нескольким запросам подряд или на нём нужен индекс.

Чем CTE принципиально отличается от временной таблицы, созданной через CREATE TEMPORARY TABLE?

Рекурсивные CTE

Рекурсивный CTE — единственный способ в стандартном SQL обойти структуру неизвестной заранее глубины: оргструктуру, дерево категорий, цепочку комментариев. Это классика собеседований уровня middle.

Начнём с минимального примера — сгенерируем числа от 1 до 5:

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

Внутри рекурсивного CTE всегда три части:

• Якорь — SELECT 1 AS n: стартовая строка, выполняется один раз.
• Рекурсивная часть — SELECT n + 1 FROM numbers: обращается к самому CTE и выполняется снова и снова, каждый раз получая на вход строки предыдущего шага.
• Условие остановки — WHERE n < 5: как только рекурсивная часть вернула пустой результат, обход заканчивается. Без этого условия запрос зациклится.

Что произойдёт, если из рекурсивного CTE убрать условие остановки?

Обход иерархии: оргструктура

Теперь реальная задача. Есть таблица сотрудников, где каждый ссылается на своего руководителя:

MySQL 8.1
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

Вопрос «выведи всех сотрудников с уровнем подчинения» обычным JOIN не решается: мы не знаем, сколько уровней в иерархии. Рекурсивный CTE решает его за один запрос:

MySQL 8.1
WITH RECURSIVE hierarchy AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.employee_id
)
SELECT employee_id, name, level
FROM hierarchy
ORDER BY level, employee_id;

Якорь нашёл директора (manager_id IS NULL), первый шаг рекурсии — его прямых подчинённых, второй — подчинённых подчинённых, и так до конца. Остановка здесь происходит сама: когда у очередного уровня нет подчинённых, JOIN возвращает пустой результат.

Тот же шаблон работает для категорий товаров, вложенных меню, веток комментариев — любых таблиц, где строка ссылается на родителя.

Когда CTE может замедлить запрос

CTE — про читаемость, а не про скорость. В большинстве случаев оптимизатор разворачивает CTE так же, как подзапрос, и разницы в производительности нет. Но есть нюансы, о которых стоит знать:

• Материализация. СУБД может вычислить CTE целиком во временную область и только потом применять внешние фильтры. PostgreSQL до версии 12 делал так всегда: условие WHERE снаружи CTE не «проваливалось» внутрь, и CTE на миллионы строк вычислялся полностью. Начиная с PostgreSQL 12 однократно используемые CTE встраиваются в запрос, а поведением можно управлять словами MATERIALIZED / NOT MATERIALIZED.
• Повторное использование. Если CTE используется в запросе несколько раз, СУБД может посчитать его один раз и переиспользовать — это плюс. Но может и вычислить дважды: проверяйте план запроса через EXPLAIN.
• Рекурсия без ограничений. Ошибка в условии остановки или цикл в данных (сотрудник — руководитель сам себе) приводит к бесконечной рекурсии. MySQL по умолчанию прервёт её на 1000 итераций (cte_max_recursion_depth), PostgreSQL будет работать, пока не упрётся в лимиты, поэтому в ненадёжных данных стоит добавлять страховку вида WHERE level < 100.

Что дальше

• базовый синтаксис WITH с упражнениями — в уроке Оператор WITH;
• как устроены подзапросы, с которыми мы сравнивали CTE, — в уроке Вложенные SQL запросы;
• потренировать CTE на реальных задачах можно в тренажёре SQL.