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;

Se usa para recorrer datos jerárquicos (árboles): organigramas, listas de materiales y similares. La forma clásica de Oracle es CONNECT BY / PRIOR y la pseudocolumna LEVEL; START WITH establece la fila raíz. Desde 11gR2 también se admite la CTE recursiva estándar.

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 una consulta ancla y una parte recursiva con UNION ALL; la parte recursiva se ejecuta repetidamente hasta que el resultado queda vacío. Ideal para jerarquías y recorridos de grafos. Protéjase de los bucles infinitos con UNION (en lugar de ALL) o con protección de 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;

MySQL 8.0 (y MariaDB 10.2+) admiten la sintaxis WITH RECURSIVE conforme al estándar. La profundidad de recursión está limitada por la variable de sistema cte_max_recursion_depth (por defecto 1000). Las versiones anteriores requerían escribir un bucle en un procedimiento almacenado.

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

En SQL Server no se escribe la palabra clave RECURSIVE para una CTE recursiva; una CTE con UNION ALL que se referencia a sí misma se trata automáticamente como recursiva. El límite de recursión por defecto es 100; cámbielo con OPTION (MAXRECURSION n) (0 = ilimitado, úselo con 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"
  } }
])

MongoDB no tiene CTE recursiva; para recorridos jerárquicos se usa la etapa de agregación $graphLookup. connectFromField/connectToField establecen los campos de enlace y maxDepth el límite de profundidad. Resuelve relaciones de árbol y de grafo en una sola consulta.

Redis

# Not applicable: Redis has no recursive queries.

Redis no ofrece un lenguaje de consulta. El recorrido de jerarquías/grafos se hace en el código de la aplicación o en el servidor con un script Lua; en Redis básico, el recorrido del árbol se escribe en el lado del cliente. (El soporte del módulo RedisGraph, usado anteriormente, terminó en 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 no ofrece consultas recursivas (no hay equivalente de WITH RECURSIVE / CONNECT BY). Las relaciones jerárquicas se modelan con el tipo de campo 'join' (parent/child) o almacenando la ruta del árbol (materialized path) en un campo; el recorrido de varios niveles suele hacerse en la aplicación.