Как выглядит синтаксис CTE-запроса?

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

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

CTE начинается с ключевого слова WITH, за которым следует имя CTE и необязательный список столбцов, а затем ключевое слово AS и подзапрос в скобках. После определения одного или нескольких CTE выполняется основной запрос, который может ссылаться на эти CTE как на обычные таблицы.

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

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

Особенно мощной возможностью CTE является рекурсия, которая позволяет обрабатывать иерархические или древовидные данные, например, структуру подчинённых в организации или дерево категорий. Рекурсивный CTE состоит из двух частей, объединённых оператором UNION ALL: якорный член (начальная точка) и рекурсивный член.

WITH RECURSIVE category_path AS (
    -- Якорный член: выбираем корневые категории
    SELECT id, name, parent_id, name as path
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    -- Рекурсивный член: присоединяем дочерние категории
    SELECT c.id, c.name, c.parent_id, CONCAT(cp.path, ' > ', c.name)
    FROM categories c
    INNER JOIN category_path cp ON c.parent_id = cp.id
)
SELECT * FROM category_path;

Применение и преимущества

CTE широко применяются для:

• Упрощения сложных запросов, заменяя вложенные подзапросы.
• Выполнения рекурсивных операций, которые сложно реализовать стандартным SQL.
• Организации кода, когда промежуточный результат используется несколько раз в одном запросе (хотя в некоторых СУБД CTE может материализоваться, что влияет на производительность).

Вывод: CTE стоит применять для повышения читаемости и поддержки сложных SQL-запросов, особенно при работе с иерархическими данными или когда необходимо логически разделить этапы обработки данных в одном запросе.