Рекурсивные CTE в SQL: как обходить деревья, цепочки и графы одним запросом

Обычный CTE — это именованный подзапрос. Он помогает разложить большой SQL-запрос на понятные части: сначала посчитали одно, потом второе, потом собрали итог.

Рекурсивный CTE — инструмент мощнее. Он умеет ссылаться сам на себя и выполнять один и тот же шаг снова и снова, пока не закончатся новые строки.

Звучит немного необычно, но идея простая. Представьте, что у вас есть сотрудник, у него есть руководитель, у руководителя тоже есть руководитель, и так дальше. Или категория товара, у которой есть родительская категория. Или комментарий, который отвечает на другой комментарий.

В приложении такую задачу часто решают циклом:

1. Найти первого сотрудника.
2. Найти его подчинённых.
3. Потом подчинённых этих подчинённых.
4. Потом ещё глубже.
5. Остановиться, когда новых строк больше нет.

Рекурсивный CTE позволяет сделать такой обход прямо в SQL — одним запросом.

Разберём всё на понятной схеме:

employees(id, name, manager_id)
orders(id, created_at)
edges(from_id, to_id)

Таблица employees хранит сотрудников. В колонке manager_id лежит id руководителя. Если manager_id равен NULL, значит перед нами человек на самом верху иерархии.

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

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

• стартовый запрос — с него всё начинается;
• рекурсивный шаг — он ссылается на сам CTE и добавляет следующий слой данных.

Эти две части соединяются через UNION ALL.

Общий шаблон выглядит так:

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    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;

Разберём по-человечески.

Первая часть:

SELECT id, name, manager_id, 1 AS depth
FROM employees
WHERE manager_id IS NULL

Она выполняется один раз. Здесь мы берём сотрудников без руководителя — например, генерального директора или топ-менеджеров. Это стартовая точка обхода.

Вторая часть:

SELECT e.id, e.name, e.manager_id, c.depth + 1
FROM employees e
JOIN chain c ON e.manager_id = c.id

А вот она выполняется повторно. Она берёт строки, которые уже попали в chain, и ищет следующих сотрудников, для которых текущая строка является руководителем.

То есть логика такая:

1. Сначала нашли верхушку компании.
2. Потом нашли их прямых подчинённых.
3. Потом нашли подчинённых этих подчинённых.
4. Потом следующий уровень.
5. Как только новый уровень пустой — запрос останавливается.

Колонка depth показывает глубину. У руководителя верхнего уровня будет 1, у его подчинённых — 2, у подчинённых подчинённых — 3.

Ключевое слово RECURSIVE пишется один раз после WITH. Без него PostgreSQL не разрешит CTE ссылаться на самого себя.

Обход дерева вниз: все подчинённые менеджера

Самый частый пример рекурсивного CTE — найти всех сотрудников внутри ветки.

Например, у нас есть руководитель с id = 1, и мы хотим получить всех людей под ним: прямых подчинённых, подчинённых подчинённых и так далее.

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;

Здесь есть две полезные колонки.

depth показывает уровень вложенности. По нему удобно понять, насколько глубоко сотрудник находится в структуре.

path показывает полный путь от стартового руководителя до конкретного сотрудника. Например:

Alice
Alice > Bob
Alice > Bob > Carol

Такая колонка очень помогает не просто получить список, а увидеть структуру глазами.

Этот же приём подходит не только для сотрудников. Точно так же обходятся:

• категории товаров;
• разделы меню;
• комментарии с ответами;
• папки и подпапки;
• состав изделия;
• зависимости задач.

Главный признак такой модели — строка ссылается на другую строку в той же таблице.

Обход вверх: от сотрудника к руководителям

Иногда нужно идти не вниз, а вверх.

Например, есть сотрудник с id = 42, и мы хотим найти всю цепочку его руководителей: непосредственного менеджера, менеджера менеджера и так до самого верха.

Для этого меняется только условие соединения.

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;

В запросе вниз мы искали строки, у которых manager_id равен текущему id.

А в запросе вверх наоборот: берём текущую строку и ищем сотрудника, чей id равен её manager_id.

То есть одна и та же таблица, один и тот же рекурсивный CTE, но направление обхода другое.

Почему обычно используют UNION ALL

В рекурсивных CTE чаще всего пишут UNION ALL, а не UNION.

UNION ALL просто добавляет новые строки в результат. Он не пытается удалять дубликаты, поэтому работает быстрее и предсказуемее.

UNION сначала объединяет строки, а потом убирает повторы. Иногда это может случайно помочь против некоторых повторов, но полагаться на это опасно.

Например, если в результате есть колонка depth или path, одна и та же вершина может выглядеть как разные строки:

id = 5, depth = 2
id = 5, depth = 4

Для UNION это уже разные строки, потому что отличается depth. Значит, от настоящего цикла такой способ не спасёт.

Поэтому правило простое: используйте UNION ALL, а защиту от циклов делайте явно.

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

Рекурсивный CTE не обязан обходить таблицу. Он может сам генерировать данные.

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

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;

Что здесь происходит:

1. Стартуем с даты 2026-01-01.
2. На каждом шаге прибавляем один день.
3. Продолжаем, пока дата меньше 2026-01-31.
4. Получаем список всех дней месяца.
5. Через LEFT JOIN присоединяем заказы.

Зачем нужен именно LEFT JOIN? Чтобы дни без заказов тоже остались в результате. Для отчётов это очень важно: если в какой-то день было ноль заказов, строка всё равно должна быть видна.

В PostgreSQL для таких задач часто проще использовать generate_series:

SELECT d::date
FROM generate_series(
    DATE '2026-01-01',
    DATE '2026-01-31',
    INTERVAL '1 day'
) AS d;

Но пример с рекурсией полезен сам по себе. Он показывает главный принцип: условие остановки вы задаёте вручную.

В нашем случае это строка:

WHERE d < DATE '2026-01-31'

Без неё запрос продолжал бы порождать новые даты бесконечно.

Самая важная опасность: бесконечные циклы

Рекурсивный CTE похож на цикл в обычном программировании. А у любого цикла есть риск: если не продумать остановку, он может выполняться очень долго или вообще не закончиться.

С деревьями обычно спокойнее. Если данные аккуратные, у каждого узла есть один родитель, а циклов нет.

Но в реальной базе бывают ошибки.

Например:

A -> B
B -> A

Или длиннее:

A -> B
B -> C
C -> A

В такой ситуации рекурсивный запрос может ходить по кругу: из A в B, из B в C, из C снова в A.

То же самое бывает в графах друзей, зависимостей, маршрутов, связанных задач и любых данных, где один объект может вести к другому.

Защита от циклов в PostgreSQL 14+

В PostgreSQL 14 появилась удобная конструкция CYCLE. Она позволяет явно сказать базе: отслеживай посещённые значения и помечай строки, которые образуют цикл.

Допустим, у нас есть таблица связей:

edges(from_id, to_id)

Запрос может выглядеть так:

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 говорит PostgreSQL следить за повторными значениями to_id.

Если значение уже встречалось в текущем пути, база помечает строку как цикл через is_cycle. А в финальном запросе мы оставляем только строки без цикла:

WHERE NOT is_cycle

Так запрос не будет бесконечно ходить по одним и тем же вершинам.

Защита от циклов вручную

Если CYCLE недоступен, можно хранить путь вручную — например, в массиве.

Идея такая:

1. В стартовой части создаём массив посещённых узлов.
2. На каждом шаге добавляем новый узел в массив.
3. Перед переходом проверяем, что нового узла ещё нет в пути.

Пример фрагмента:

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);

Условие:

WHERE e.to_id <> ALL(r.path)

означает: переходить можно только в тот узел, которого ещё нет в текущем пути.

Это очень важный приём для графов. Особенно если данные приходят от пользователей, импортируются из внешних систем или могут быть неидеальными.

Дополнительный предохранитель: ограничение глубины

Даже если вы уверены в данных, полезно добавлять ограничение глубины.

Например:

WITH RECURSIVE subordinates AS (
    SELECT
        id,
        name,
        manager_id,
        1 AS depth
    FROM employees
    WHERE id = 1

    UNION ALL

    SELECT
        e.id,
        e.name,
        e.manager_id,
        s.depth + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
    WHERE s.depth < 100
)
SELECT *
FROM subordinates;

Условие:

WHERE s.depth < 100

не даёт запросу уйти глубже сотого уровня.

В нормальной оргструктуре сто уровней почти никогда не нужны. Если запрос дошёл так глубоко, скорее всего, в данных ошибка или вы обходите не то, что хотели.

Рекурсивные CTE в разных базах данных

В PostgreSQL рекурсивные CTE пишутся через WITH RECURSIVE. Это один из самых удобных и понятных вариантов.

В MySQL 8 синтаксис тоже похожий:

WITH RECURSIVE ...

Но есть ограничение глубины рекурсии через переменную cte_max_recursion_depth. По умолчанию значение равно 1000. Если запрос превысит этот предел, MySQL остановит выполнение с ошибкой.

Это не заменяет нормальную защиту от циклов, но помогает не уронить базу бесконечной рекурсией.

В ClickHouse рекурсивные CTE появились позднее и могут иметь ограничения в зависимости от версии и конкретного сценария. Для иерархий там часто используют другие инструменты, например словари и функции вроде dictGetHierarchy.

Поэтому перед переносом рекурсивного запроса между СУБД всегда проверяйте документацию именно вашей версии.

Как читать рекурсивный CTE без страха

Когда видите WITH RECURSIVE, не пытайтесь сразу понять весь запрос целиком. Разбирайте его по шагам.

Сначала найдите стартовую часть. Она идёт до UNION ALL. Ответьте себе на вопрос: с каких строк начинается обход?

Потом найдите рекурсивную часть. Она идёт после UNION ALL. Посмотрите, как она соединяется с самим CTE.

Затем найдите условие остановки. Это может быть:

• отсутствие новых строк;
• ограничение по дате;
• ограничение по depth;
• проверка массива посещённых узлов;
• конструкция CYCLE.

После этого запрос становится гораздо понятнее. Рекурсивный CTE — не магия, а аккуратный повтор одного и того же SQL-шаблона.

Практический образ

Можно представить рекурсивный CTE как снежный ком.

Сначала у вас есть маленькое ядро — стартовые строки.

Потом запрос находит строки, связанные с этим ядром.

Потом строки, связанные с найденными строками.

Потом следующий слой.

И так до тех пор, пока новый слой не окажется пустым.

В дереве сотрудников это выглядит как спуск по уровням компании. В календаре — как добавление следующего дня. В графе — как движение от одной вершины к соседним.

Главное, что нужно запомнить

Рекурсивный CTE нужен, когда данные устроены цепочкой, деревом или графом.

Базовый шаблон состоит из стартового запроса, UNION ALL и рекурсивного шага.

Стартовая часть выполняется один раз. Рекурсивная часть выполняется снова и снова, пока очередная итерация не перестанет возвращать строки.

Для иерархий сотрудников, категорий, папок и комментариев рекурсивный CTE позволяет получить всю вложенность одним запросом.

Для календарей и числовых рядов рекурсия может генерировать данные без отдельной таблицы.

Главная опасность — бесконечные циклы. Поэтому для графов используйте CYCLE, массив посещённых узлов или хотя бы ограничение по depth.

Рекурсивный CTE — это аккуратный цикл внутри SQL. Когда вы понимаете пару: стартовые строки плюс следующий шаг, деревья и цепочки перестают быть чем-то страшным и больше не требуют переносить всю логику в приложение.