Как написать рекурсивный CTE в SQL

Как написать рекурсивный CTE (Common Table Expression)?

Рекурсивный CTE — это выражение WITH, которое ссылается само на себя и позволяет обрабатывать иерархии, деревья и последовательности. Обычно оно состоит из базовой части и рекурсивной части, соединённых через UNION ALL.

Подробный ответ

Что такое CTE

CTE (Common Table Expression) — это временное именованное выражение, которое создаётся внутри запроса через WITH. Его можно использовать как промежуточный результат для более читаемого SQL.

Что такое рекурсивный CTE

Рекурсивный CTE используется, когда результат строится итеративно: базовый запрос задаёт старт, а рекурсивный запрос повторяет шаги, пока данные не закончатся.

Структура

Обычно рекурсивный CTE состоит из двух частей:

anchor member — базовый запрос;
recursive member — часть, которая ссылается на сам CTE.

Пример

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 подходит для деревьев, графов, оргструктур, зависимостей и генерации последовательностей.

Что важно помнить

Нужно следить за условием остановки, иначе можно получить бесконечную рекурсию. Также важно учитывать производительность и глубину дерева.

Как ответить на собеседовании

Рекурсивный CTE — это WITH RECURSIVE запрос, где есть базовая часть и рекурсивная часть, соединённые через UNION ALL. Он нужен для обхода иерархий, например категорий или сотрудников с начальниками.