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

Синтаксис CTE

WITH cte_name (optional_columns) AS (
\-- подзапрос
SELECT ...
)
SELECT ...
FROM cte_name;

Можно использовать несколько CTE в одном запросе, разделяя их запятыми:

WITH cte1 AS (...),
cte2 AS (...)
SELECT ...
FROM cte2
JOIN cte1 ON ...;

Простой пример

Пусть есть таблица sales:

id | employee | amount

-----------------------

1 | Alice | 100

2 | Bob | 200

3 | Alice | 300

Если вы хотите сначала найти сумму по каждому сотруднику, а затем выбрать только тех, кто продал больше 150, можно использовать CTE:

WITH total_sales AS (
SELECT employee, SUM(amount) AS total
FROM sales
GROUP BY employee
)
SELECT \*
FROM total_sales
WHERE total > 150;

Без CTE это пришлось бы писать как вложенный подзапрос, что менее читаемо:

SELECT \*
FROM (
SELECT employee, SUM(amount) AS total
FROM sales
GROUP BY employee
) AS total_sales
WHERE total > 150;

Зачем нужен CTE

1. Повышает читаемость
   Логика запроса разбивается на "блоки", каждый из которых — как отдельная подзадача.

2. Избегает дублирования кода
   Один и тот же подзапрос можно использовать многократно без его копирования.

3. Упрощает отладку
   Можно проверить корректность CTE отдельно, потом добавлять остальные слои запроса.

4. Поддерживает рекурсию
   CTE можно использовать для построения рекурсивных запросов — например, обход дерева или иерархии.

Пример с несколькими CTE

Пусть нужно:

• посчитать среднюю сумму продаж по всем сотрудникам,

• найти сотрудников, чьи продажи выше среднего,

• отобразить их имена и суммы.

WITH employee_totals AS (
SELECT employee, SUM(amount) AS total
FROM sales
GROUP BY employee
),
average_total AS (
SELECT AVG(total) AS avg_total
FROM employee_totals
)
SELECT e.employee, e.total
FROM employee_totals e
JOIN average_total a
ON e.total > a.avg_total;

Здесь employee_totals и average_total определяются отдельно, а затем соединяются в основном запросе. Это делает код более модульным и наглядным.

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

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

Пример: таблица employees(id, name, manager_id)

WITH RECURSIVE org_chart 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, c.level + 1
FROM employees e
JOIN org_chart c ON e.manager_id = c.id
)
SELECT \* FROM org_chart;

Это позволяет получить структуру сотрудников по уровням.

Ограничения и нюансы

• CTE — это логическая конструкция, а не физическое сохранение данных, как временная таблица.

• Производительность может отличаться от подзапросов, особенно при множественном использовании. В некоторых СУБД (например, PostgreSQL) CTE раньше оптимизировались хуже, чем подзапросы, но это меняется.

• В некоторых движках (например, SQL Server) CTE не может быть индексирован — в отличие от временной таблицы.

Разница между CTE, подзапросом и временной таблицей

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

• Когда запрос становится слишком сложным из-за вложенных подзапросов.

• Когда нужен рекурсивный обход дерева/графа.

• Когда один и тот же подзапрос используется несколько раз.

• Когда нужно логически разбить большой запрос на шаги для отладки или тестирования.

CTE — мощный инструмент для написания структурированных, модульных и читаемых SQL-запросов.