Что такое Common Table Expression (CTE) в SQL?

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

Основные цели и преимущества:

1. Улучшение читаемости: Разбивает сложные запросы на логические блоки.
2. Исключение дублирования: CTE можно ссылаться несколько раз в основном запросе.
3. Рекурсия: Позволяет выполнять рекурсивные запросы (с RECURSIVE).

Базовый синтаксис:

WITH cte_name (column1, column2) AS (
    -- Определение CTE (подзапрос)
    SELECT ...
)
-- Основной запрос, использующий CTE
SELECT * FROM cte_name;

Пример: Агрегация данных по отделам

WITH DepartmentSummary AS (
    SELECT 
        department_id, 
        COUNT(*) AS emp_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT 
    d.name,
    ds.emp_count,
    ds.avg_salary
FROM departments d
JOIN DepartmentSummary ds ON d.id = ds.department_id
WHERE ds.emp_count > 10;

Рекурсивный CTE (для иерархических данных):

WITH RECURSIVE SubordinateTree AS (
    -- Якорь рекурсии: начальный менеджер
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE id = 1 -- ID начальника

    UNION ALL

    -- Рекурсивный шаг
    SELECT e.id, e.name, e.manager_id, st.level + 1
    FROM employees e
    INNER JOIN SubordinateTree st ON e.manager_id = st.id
)
SELECT * FROM SubordinateTree;

CTE особенно полезны в PostgreSQL, MS SQL Server, Oracle и SQLite (с версии 3.8.3).