Рекурсивный CTE (WITH RECURSIVE)

Oracle

-- Hierarchy with CONNECT BY:
SELECT id, name, manager_id, LEVEL
FROM employee
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

-- Alternative (11gR2+): standard recursive WITH (the RECURSIVE keyword is not written)
WITH tree (id, name, manager_id) AS (
  SELECT id, name, manager_id FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree;

Используется для обхода иерархических (древовидных) данных: оргструктур, спецификаций изделий и т. п. Классический способ Oracle — CONNECT BY / PRIOR и псевдостолбец LEVEL; START WITH задаёт корневую строку. С 11gR2 поддерживается и стандартный рекурсивный CTE.

PostgreSQL

WITH RECURSIVE tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level;

WITH RECURSIVE объединяет якорный запрос и рекурсивную часть через UNION ALL; рекурсивная часть выполняется повторно, пока результат не станет пустым. Идеален для иерархий и обхода графов. Защищайтесь от бесконечных циклов через UNION (вместо ALL) или защиту от циклов.

MySQL

WITH RECURSIVE tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level;

MySQL 8.0 (и MariaDB 10.2+) поддерживают соответствующий стандарту синтаксис WITH RECURSIVE. Глубина рекурсии ограничена системной переменной cte_max_recursion_depth (по умолчанию 1000). В старых версиях приходилось писать цикл в хранимой процедуре.

SQL Server

WITH tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level
OPTION (MAXRECURSION 100);

В SQL Server ключевое слово RECURSIVE для рекурсивного CTE не пишется; CTE с UNION ALL, ссылающийся на себя, автоматически считается рекурсивным. Лимит рекурсии по умолчанию — 100; меняется через OPTION (MAXRECURSION n) (0 = без ограничений, используйте осторожно).

MongoDB

// Closest equivalent: the $graphLookup stage
db.employee.aggregate([
  { $match: { manager_id: null } },
  { $graphLookup: {
      from: "employee",
      startWith: "$id",
      connectFromField: "id",
      connectToField: "manager_id",
      as: "subordinates"
  } }
])

В MongoDB нет рекурсивного CTE; для иерархического обхода используется этап агрегации $graphLookup. connectFromField/connectToField задают поля связи, maxDepth — ограничение глубины. Он разрешает древовидные и графовые связи одним запросом.

Redis

# Not applicable: Redis has no recursive queries.

Redis не предлагает языка запросов. Обход иерархий/графов выполняется в коде приложения или на сервере Lua-скриптом; в базовом Redis обход дерева пишется на стороне клиента. (Поддержка ранее использовавшегося модуля RedisGraph прекращена в 2023.)

Elasticsearch

# Not applicable: recursive queries are not supported.
# Approach: model with parent-child (join field) or nested,
# deep hierarchies are traversed on the application side.

Elasticsearch не предлагает рекурсивных запросов (аналога WITH RECURSIVE / CONNECT BY нет). Иерархические связи моделируются типом поля 'join' (parent/child) или хранением пути в дереве (materialized path) в поле; многоуровневый обход обычно выполняется в приложении.