Что такое CTE в SQL?

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

Основные применения и синтаксис:

WITH department_stats 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 department_stats ds ON d.id = ds.department_id
WHERE ds.avg_salary > 100000;

Ключевые особенности и преимущества:

1. Повышение читаемости: Позволяет разбить сложный запрос на логические блоки, что упрощает его понимание и поддержку.
2. Рекурсивные запросы: CTE — это основной способ выполнения рекурсивных запросов в SQL (например, для работы с иерархическими данными).

   WITH RECURSIVE subordination_tree AS (
       -- Якорь рекурсии
       SELECT id, name, manager_id
       FROM employees
       WHERE id = 1 -- CEO
       UNION ALL
       -- Рекурсивный член
       SELECT e.id, e.name, e.manager_id
       FROM employees e
       INNER JOIN subordination_tree st ON e.manager_id = st.id
   )
   SELECT * FROM subordination_tree;

3. Замена представлений (VIEW) в рамках одного запроса: Не требует создания постоянного объекта в БД.
4. Возможность множественного использования: Один CTE можно сослаться несколько раз в основном запросе, хотя материализация (кэширование результата) зависит от оптимизатора СУБД.

Отличие от подзапроса: CTE делает запрос более структурированным и понятным, особенно при множественных вложенностях. Рекурсивные CTE невозможно реализовать обычным подзапросом.