Как и для чего используются обобщенные табличные выражения (CTE) в SQL

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

Они объявляются с помощью ключевого слова WITH.

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

1. Читаемость: CTE позволяют разбить один монолитный и сложный запрос на несколько логических, последовательных шагов. Каждый шаг имеет свое имя, что делает логику запроса понятной.
2. Многократное использование: На CTE можно ссылаться несколько раз в рамках основного запроса, избегая дублирования кода.
3. Рекурсия: CTE — единственный стандартный способ выполнения рекурсивных запросов в SQL, что необходимо для работы с иерархическими данными (например, структура организации, дерево категорий).

Пример простого CTE:

-- Цель: Найти отделы, в которых работает более 10 сотрудников

-- Шаг 1: Определяем CTE для подсчета сотрудников в каждом отделе
WITH department_employee_count AS (
    SELECT
        department_id,
        COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
)
-- Шаг 2: Используем CTE для соединения с таблицей отделов и фильтрации
SELECT
    d.department_name,
    dec.employee_count
FROM departments d
JOIN department_employee_count dec ON d.id = dec.department_id
WHERE dec.employee_count > 10;

Пример рекурсивного CTE:

-- Цель: Построить иерархию сотрудников, начиная с CEO

WITH RECURSIVE employee_hierarchy AS (
    -- 1. Базовый случай: находим сотрудника верхнего уровня (CEO)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 2. Рекурсивный шаг: присоединяем подчиненных к их менеджерам
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
-- Выводим всю построенную иерархию
SELECT * FROM employee_hierarchy;