Что такое рекурсивные CTE (Common Table Expressions) и для чего они используются?

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

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

1. Базовая (якорная) часть (Anchor Member): Начальный запрос, который выполняется один раз и формирует стартовый набор данных. Это условие выхода из рекурсии.
2. Рекурсивная часть (Recursive Member): Запрос, который ссылается на CTE и выполняется многократно, на каждой итерации добавляя новые строки к результату предыдущей, пока не перестанет возвращать новые данные.

Эти две части объединяются с помощью UNION ALL.

Основное применение — работа с иерархиями: организационные структуры, деревья категорий, связи в социальных сетях, маршруты на карте.

Пример: Получение дерева категорий

WITH RECURSIVE CategoryTree AS (
    -- 1. Базовая часть: находим корневые категории (у которых нет родителя)
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 2. Рекурсивная часть: присоединяем дочерние категории к уже найденным
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN CategoryTree ct ON c.parent_id = ct.id
)
SELECT * FROM CategoryTree;

Ключевые моменты:

• В PostgreSQL и некоторых других СУБД обязательно ключевое слово RECURSIVE.
• Использование UNION ALL предпочтительнее UNION, так как UNION удаляет дубликаты на каждом шаге, что сильно снижает производительность.
• Важно спроектировать запрос так, чтобы рекурсия гарантированно завершилась, иначе можно получить бесконечный цикл.
• СУБД имеют встроенную защиту от бесконечной рекурсии (например, max_recursion_depth в PostgreSQL).