CTE recursiva (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;

Usada para percorrer dados hierárquicos (árvore): organogramas, listas de materiais e similares. A forma clássica do Oracle é CONNECT BY / PRIOR e a pseudocoluna LEVEL; START WITH define a linha raiz. Desde o 11gR2, a CTE recursiva padrão também é suportada.

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 combina uma consulta âncora e uma parte recursiva com UNION ALL; a parte recursiva é executada repetidamente até o resultado ficar vazio. Ideal para hierarquias e travessia de grafos. Proteja-se contra loops infinitos com UNION (em vez de ALL) ou proteção contra ciclos.

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;

O MySQL 8.0 (e o MariaDB 10.2+) suportam a sintaxe WITH RECURSIVE em conformidade com o padrão. A profundidade da recursão é limitada pela variável de sistema cte_max_recursion_depth (padrão 1000). Versões mais antigas exigiam escrever um loop em uma stored procedure.

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

No SQL Server, a palavra-chave RECURSIVE não é escrita para uma CTE recursiva; uma CTE com UNION ALL que referencia a si mesma é tratada automaticamente como recursiva. O limite padrão de recursão é 100; altere-o com OPTION (MAXRECURSION n) (0 = ilimitado, use com cuidado).

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"
  } }
])

O MongoDB não tem CTE recursiva; o estágio de agregação $graphLookup é usado para travessia hierárquica. connectFromField/connectToField definem os campos de ligação e maxDepth define o limite de profundidade. Resolve relações de árvore e grafo em uma única consulta.

Redis

# Not applicable: Redis has no recursive queries.

O Redis não oferece linguagem de consulta. A travessia de hierarquias/grafos é feita no código da aplicação ou no lado do servidor com um script Lua; no Redis core, o percurso da árvore é escrito no lado do cliente. (O suporte ao módulo RedisGraph, usado anteriormente, terminou em 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.

O Elasticsearch não oferece consulta recursiva (não há equivalente de WITH RECURSIVE / CONNECT BY). Relações hierárquicas são modeladas com o tipo de campo 'join' (parent/child) ou armazenando o caminho da árvore (materialized path) em um campo; a travessia em vários níveis geralmente é feita na aplicação.