Recursive CTEs in SQL: WITH RECURSIVE for Trees, Graphs and Number Series

Обычный CTE — это просто именованный подзапрос. Рекурсивный CTE — другой зверь: он умеет ссылаться сам на себя и выполняться по кругу, пока не закончатся данные. Именно так в SQL обходят иерархии: дерево подчинённых, цепочку категорий, граф друзей или зависимостей. Если вы когда-то писали приложение, которое в цикле дёргает базу «а кто начальник у начальника?», — рекурсивный CTE делает это одним запросом.

Разберём механику на схеме employees(id, name, manager_id) и orders, а в конце поговорим про самое опасное — бесконечные циклы.

Как устроен WITH RECURSIVE

Рекурсивный CTE всегда состоит из двух частей, соединённых через UNION ALL:

• Якорь (anchor) — стартовый запрос, который выполняется один раз. Это «корень» рекурсии.
• Рекурсивный шаг — запрос, который ссылается на имя самого CTE. Он повторяется снова и снова, на каждой итерации видя строки, добавленные предыдущей итерацией.

WITH RECURSIVE chain AS (
  -- якорь: выполняется один раз
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL          -- топ-менеджеры

  UNION ALL

  -- рекурсивный шаг: ссылается на chain
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain ORDER BY depth, id;

Движок работает так: сначала отрабатывает якорь и кладёт результат в «рабочую таблицу». Затем гоняет рекурсивный шаг, передавая ему текущую рабочую таблицу под именем chain. Новые строки становятся следующей рабочей таблицей, и шаг повторяется. Как только итерация не вернула ни одной строки — рекурсия останавливается, и всё накопленное отдаётся наружу.

Ключевое слово RECURSIVE ставится один раз после WITH, даже если рекурсивных CTE несколько. Без него Postgres не разрешит CTE ссылаться на себя.

Обход оргструктуры (дерево вниз и вверх)

Самый частый кейс — «покажи всех под этим менеджером». Колонка depth даёт уровень вложенности, а собранный path — полную цепочку до сотрудника.

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id,
         1 AS depth,
         name::text AS path
  FROM employees
  WHERE id = 1                       -- стартуем с конкретного босса

  UNION ALL

  SELECT e.id, e.name, e.manager_id,
         s.depth + 1,
         s.path || ' > ' || e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT id, name, depth, path
FROM subordinates
ORDER BY path;

Чтобы пойти вверх (от сотрудника к его руководителям), достаточно поменять местами условие соединения: в рекурсивном шаге ищем не «чей менеджер — текущая строка», а «кто менеджер текущей строки».

WITH RECURSIVE managers AS (
  SELECT id, name, manager_id
  FROM employees WHERE id = 42

  UNION ALL

  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN managers m ON m.manager_id = e.id   -- поднимаемся к боссу
)
SELECT * FROM managers;

Категории товаров, комментарии-треды, BOM (состав изделия) — всё это та же модель «строка ссылается на родителя», и обходится тем же шаблоном.

Числовые ряды и календари

Рекурсия не требует таблицы-источника. Можно сгенерировать ряд чисел или дат на лету — удобно, когда нужно «дозаполнить» дни без заказов.

WITH RECURSIVE days AS (
  SELECT DATE '2026-01-01' AS d
  UNION ALL
  SELECT d + 1 FROM days
  WHERE d < DATE '2026-01-31'        -- условие остановки!
)
SELECT d.d, COUNT(o.id) AS orders
FROM days d
LEFT JOIN orders o ON o.created_at::date = d.d
GROUP BY d.d
ORDER BY d.d;

В PostgreSQL для рядов обычно проще generate_series('2026-01-01', '2026-01-31', INTERVAL '1 day') — но рекурсия универсальна и работает там, где generate_series нет (например, в более простых движках). Этот пример важен ещё и тем, что показывает: остановку придумываете вы сами. Без WHERE d < ... запрос крутился бы вечно.

Графы и защита от циклов

Дерево безопасно: у каждого узла один родитель, циклов нет. Граф — нет. Если в manager_id случайно образуется петля (A → B → A) или вы обходите граф друзей/зависимостей, рекурсия зациклится и съест память.

Postgres 14+ даёт встроенную защиту — CYCLE:

WITH RECURSIVE reachable AS (
  SELECT from_id, to_id
  FROM edges WHERE from_id = 1

  UNION ALL

  SELECT e.from_id, e.to_id
  FROM edges e
  JOIN reachable r ON e.from_id = r.to_id
)
CYCLE to_id SET is_cycle USING path_arr
SELECT DISTINCT to_id FROM reachable WHERE NOT is_cycle;

CYCLE to_id велит Postgres отслеживать уже посещённые to_id: при повторе он помечает строку is_cycle = true и не идёт дальше. На старых версиях тот же приём делают руками — таскают массив посещённых узлов и отсекают повторы:

-- вместо CYCLE: ручной массив пути
SELECT e.from_id, e.to_id, r.path || e.to_id
FROM edges e
JOIN reachable r ON e.from_id = r.to_id
WHERE e.to_id <> ALL(r.path)        -- не заходим в посещённое

Подводный камень: UNION против UNION ALL. UNION дедуплицирует строки и сам по себе обрезает часть повторов, но это не спасает от циклов с разными path/depth и работает медленнее. Опирайтесь на явную защиту (CYCLE или массив), а не на побочный эффект UNION.

Полезные предохранители на практике:

• Всегда держите явное условие остановки в рекурсивном шаге (WHERE depth < 100).
• Для графов используйте CYCLE или массив посещённых узлов.
• В MySQL 8 синтаксис тот же (WITH RECURSIVE), но глубина ограничена переменной cte_max_recursion_depth (по умолчанию 1000) — упрётесь в неё, получите ошибку, а не зависание.
• В ClickHouse рекурсивные CTE появились поздно и местами ограничены; для иерархий там чаще берут специальные движки и функции вроде dictGetHierarchy. Проверяйте версию.

Рекурсивный CTE — это for-цикл внутри декларативного SQL. Освойте пару якорь + UNION ALL, держите в голове условие остановки, и обход деревьев и графов перестанет быть поводом тащить логику в приложение.