Ответ.

В SQL существует несколько способов реализовать итеративные/рекурсивные вычисления:

Рекурсивные CTE (WITH RECURSIVE) — работают для задач вроде обхода иерархий (деревья, графы, цепочки ссылок).
Курсоры (CURSOR) — позволяют обрабатывать данные построчно, однако менее производительны для больших объемов, чем set-based операции.
Обычные set-based выражения — предпочтительнее для большинства случаев (оперируют множествами строк без итераций).

Когда использовать рекурсивный CTE:

Для иерархических структур (продуктовые категории, оргструктуры).
Для поиска путей/длины цепочек (например, genealogy, dependency graphs).

Пример:

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS level
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.level + 1
  FROM categories c
    JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

Если рекурсивный CTE не решает задачу либо данные слишком объемны — применяют другие механизмы (например, внешнюю обработку, временные таблицы или специализированные алгоритмы вне SQL).

Вопрос с подвохом.

Может ли рекурсивный CTE привести к бесконечному выполнению? Как это предотвратить?

Ответ: Да, если в графе есть циклы (например, parent_id снова ссылается на потомка), рекурсивный CTE может зациклиться. Большинство СУБД поддерживают ограничение максимального числа уровней рекурсии (MAXRECURSION для MS SQL или аналогичная опция). Хорошая практика — всегда ограничивать глубину рекурсии.

-- MS SQL Server
OPTION (MAXRECURSION 100)

Примеры реальных ошибок из-за незнания тонкостей темы.

История

В финансовом проекте рассчитывали итоговый баланс по цепочкам связанных счетов (parent-child) через рекурсивный CTE. Один из пользователей создал цикл в структуре (родитель стал своим потомком). Запрос начал выполняться бесконечно, грузив сервер. Добавили проверку циклов и MAXRECURSION, проблема решилась.

История

В системе управления задачами статус зависимых задач вычисляли курсовой обработкой. После миграции на рекурсивный CTE забыли предусмотреть оптимальные индексы по parent_id — производительность упала в 5 раз, так как на каждом шаге происходили costly join операций.

История

Запрос обхода дерева категорий с помощью курсора работал корректно, но занимал по 7 минут на 100 000 строк. Переписали на рекурсивный CTE, считавшийся "медленным", — получили тот же результат за 5 секунд благодаря оптимизации движком работы с множествами.