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

Dient zum Durchlaufen hierarchischer Daten (Baumstrukturen): Organigramme, Stücklisten und Ähnliches. Klassisch nutzt Oracle CONNECT BY / PRIOR und die Pseudospalte LEVEL; START WITH legt die Wurzelzeile fest. Seit 11gR2 wird auch die standardmäßige rekursive CTE unterstützt.

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 verbindet eine Anker-Abfrage und einen rekursiven Teil mit UNION ALL; der rekursive Teil läuft wiederholt, bis das Ergebnis leer ist. Ideal für Hierarchien und Graphtraversierung. Schütze dich mit UNION (statt ALL) oder Zyklenschutz vor Endlosschleifen.

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 (und MariaDB 10.2+) unterstützen standardkonforme WITH RECURSIVE-Syntax. Die Rekursionstiefe wird durch die Systemvariable cte_max_recursion_depth begrenzt (Standard 1000). Ältere Versionen erforderten eine Schleife in einer 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);

In SQL Server wird das Schlüsselwort RECURSIVE bei einer rekursiven CTE nicht geschrieben; eine CTE mit UNION ALL, die auf sich selbst verweist, gilt automatisch als rekursiv. Das Standard-Rekursionslimit ist 100; ändere es mit OPTION (MAXRECURSION n) (0 = unbegrenzt, mit Vorsicht verwenden).

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 hat keine rekursive CTE; für hierarchische Traversierung wird die Aggregation-Stage $graphLookup genutzt. connectFromField/connectToField legen die Verknüpfungsfelder fest, maxDepth das Tiefenlimit. Baum- und Graphbeziehungen werden so in einer einzigen Abfrage aufgelöst.

Redis

# Not applicable: Redis has no recursive queries.

Redis bietet keine Abfragesprache. Hierarchie-/Graphtraversierung erfolgt im Anwendungscode oder serverseitig mit einem Lua-Skript; in Core Redis wird der Baumdurchlauf clientseitig geschrieben. (Der Support für das früher genutzte Modul RedisGraph endete 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 bietet keine rekursive Abfrage (keine Entsprechung zu WITH RECURSIVE / CONNECT BY). Hierarchische Beziehungen werden mit dem Feldtyp 'join' (parent/child) modelliert oder indem der Baumpfad (Materialized Path) in einem Feld gespeichert wird; mehrstufige Traversierung erfolgt meist in der Anwendung.