CTE récursive (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;

Sert à parcourir des données hiérarchiques (arborescentes) : organigrammes, nomenclatures, etc. La méthode classique d'Oracle est CONNECT BY / PRIOR et la pseudo-colonne LEVEL ; START WITH définit la ligne racine. Depuis 11gR2, la CTE récursive standard est aussi prise en charge.

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 combine une requête d'ancrage et une partie récursive avec UNION ALL ; la partie récursive s'exécute de façon répétée jusqu'à ce que le résultat soit vide. Idéal pour les hiérarchies et le parcours de graphes. Prémunissez-vous contre les boucles infinies avec UNION (au lieu de ALL) ou une protection contre les cycles.

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 (et MariaDB 10.2+) prennent en charge la syntaxe WITH RECURSIVE conforme au standard. La profondeur de récursion est limitée par la variable système cte_max_recursion_depth (1000 par défaut). Les versions plus anciennes obligeaient à écrire une boucle dans une procédure stockée.

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

Dans SQL Server, le mot-clé RECURSIVE ne s'écrit pas pour une CTE récursive ; une CTE contenant UNION ALL qui se référence elle-même est automatiquement traitée comme récursive. La limite de récursion par défaut est 100 ; modifiez-la avec OPTION (MAXRECURSION n) (0 = illimité, à utiliser avec prudence).

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 n'a pas de CTE récursive ; l'étape d'agrégation $graphLookup sert au parcours hiérarchique. connectFromField/connectToField définissent les champs de liaison et maxDepth la limite de profondeur. Elle résout les relations d'arbre et de graphe en une seule requête.

Redis

# Not applicable: Redis has no recursive queries.

Redis n'offre pas de langage de requête. Le parcours de hiérarchie/graphe se fait dans le code applicatif ou côté serveur avec un script Lua ; dans Redis de base, le parcours d'arbre s'écrit côté client. (La prise en charge du module RedisGraph, autrefois utilisé, a pris fin 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 n'offre pas de requête récursive (aucun équivalent de WITH RECURSIVE / CONNECT BY). Les relations hiérarchiques sont modélisées avec le type de champ 'join' (parent/child) ou en stockant le chemin de l'arbre (materialized path) dans un champ ; le parcours multiniveau se fait généralement dans l'application.