Рекурсивні 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