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

Common Table Expressions (CTE) в SQL

Common Table Expression (CTE) — это именованный временный результирующий набор, определяемый в запросе WITH для использования в основной команде SELECT, INSERT, UPDATE или DELETE.

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

WITH cte_name AS (
    SELECT column1, column2
    FROM table1
    WHERE condition
)
SELECT *
FROM cte_name
WHERE another_condition;

CTE существует только в контексте этого запроса, потом удаляется.

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

-- Найти среднюю зарплату и сотрудников выше средней
WITH avg_salary AS (
    SELECT AVG(salary) as avg_sal
    FROM employees
)
SELECT name, salary
FROM employees
WHERE salary > (SELECT avg_sal FROM avg_salary);

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

Множественные CTE

Можно определить несколько CTE, которые ссылаются друг на друга:

WITH monthly_sales AS (
    SELECT product_id, SUM(amount) as total
    FROM orders
    GROUP BY product_id
),
top_products AS (
    SELECT product_id, total
    FROM monthly_sales
    WHERE total > 10000
    ORDER BY total DESC
)
SELECT p.name, tp.total
FROM top_products tp
JOIN products p ON tp.product_id = p.id;

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

Самое мощное использование — рекурсивные CTE для древовидных или графовых структур:

-- Получить все подкатегории рекурсивно
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 as level
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
    WHERE ct.level < 10
)
SELECT id, name, REPEAT('  ', level - 1) || name as tree
FROM category_tree
ORDER BY id;

Выигрыши от CTE

Читаемость — большие запросы разбиваются на понятные части.

Переиспользование — можно применить CTE несколько раз в одном запросе.

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

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

1. Рекурсивные операции — единственный способ для деревьев
2. Сложная логика — разбить на этапы
3. Переиспользование — одна CTE в нескольких местах запроса
4. Читаемость — вместо глубоких подзапросов
5. Отладка — можно легко проверить отдельную часть

CTE — это стандарт SQL и поддерживается всеми современными БД (PostgreSQL, MySQL 8.0+, SQLite 3.8.3+, SQL Server).