Рекурсивные CTE: деревья и иерархии в SQL

Коротко: Рекурсивное CTE («common table expression») вызывает само себя в SQL и так обходит деревья и иерархии, например категории, оргструктуры или ответы в ветках форума.

Подробнее: Обычные запросы знают фиксированное число соединений. У иерархий глубина неизвестна. Рекурсивное CTE повторяет шаг, пока не перестанет добавляться что-то новое.

Подробности

• Структура: Начальная часть (якорь), UNION ALL и рекурсивная часть, ссылающаяся на само CTE.

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM category WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM category c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth;

• Ход: Якорь даёт корни, рекурсивная часть каждый раз берёт детей строк, найденных последними.
• Защита от бесконечных циклов: При циклах (A → B → A) нужно запоминать посещённые узлы или ограничивать глубину.
• Прочие применения: Генерация числовых рядов, поиск путей в графах, разворачивание спецификаций.
• Альтернативы: Столбец с путём («materialized path»), вложенные множества (nested sets), ltree в PostgreSQL.

См. также: оконные функции SQL