Une CTE récursive est une expression de table commune qui se référence elle-même pour répéter un traitement jusqu’à ce qu’une condition d’arrêt soit satisfaite. Elle se déclare avec WITH RECURSIVE sous MySQL et PostgreSQL, ou simplement WITH sous SQL Server qui détecte automatiquement l’auto-référencement. Sa structure repose sur deux blocs obligatoires assemblés par UNION ALL : un membre ancre (anchor member) qui initialise la récursion, et un membre récursif (recursive member) qui s’appuie sur le résultat de l’itération précédente. Ce mécanisme est la seule solution native en SQL pour traverser des structures hiérarchiques de profondeur variable comme des arborescences de catégories, des organigrammes ou des nomenclatures produit. Sans condition d’arrêt explicite dans le membre récursif, le moteur SQL s’exécute indéfiniment jusqu’à atteindre le plafond de récursion qu’il impose.
Table des matières
Comment fonctionne une CTE récursive ?
Le moteur SQL exécute le membre ancre une unique fois pour produire l’ensemble de départ de la récursion. Il alimente ensuite le membre récursif avec ce résultat initial, puis répète l’opération en utilisant le résultat de chaque itération comme entrée de la suivante. La boucle s’arrête naturellement lorsque le membre récursif ne retourne plus aucune ligne, ce qui constitue la condition d’arrêt implicite. Chaque résultat intermédiaire est accumulé et assemblé par UNION ALL pour constituer le jeu de données final complet. Tester le membre ancre seul avant d’ajouter le membre récursif est la méthode recommandée pour déboguer une CTE récursive efficacement.
On illustre ici le parcours d’une table categories modélisant une arborescence via une colonne id_parent auto-référencée.
WITH RECURSIVE arborescence AS (
-- Membre ancre : sélectionne les nœuds racines
SELECT id_categorie, nom, id_parent, 0 AS niveau
FROM categories
WHERE id_parent IS NULL
UNION ALL
-- Membre récursif : descend d'un niveau à chaque itération
SELECT c.id_categorie, c.nom, c.id_parent, a.niveau + 1
FROM categories c
INNER JOIN arborescence a ON c.id_parent = a.id_categorie
)
SELECT id_categorie, nom, niveau
FROM arborescence
ORDER BY niveau, nom;Le membre ancre extrait les catégories racines dont id_parent est NULL, constituant le premier niveau de l’arbre. Le membre récursif joint la table categories avec la CTE elle-même pour descendre d’un niveau supplémentaire à chaque passage. La colonne niveau s’incrémente à chaque itération et permet d’identifier la profondeur de chaque nœud dans la hiérarchie.
Quelle est la différence entre une CTE et une CTE récursive ?
Une CTE standard produit un résultat déterminé en une seule exécution de son bloc SELECT interne, sans jamais se référencer elle-même. Elle traite des données plates ou pré-agrégées : filtrage, calcul de totaux, préparation d’un résultat intermédiaire pour une requête principale. Une CTE récursive s’exécute en boucle contrôlée grâce à son auto-référencement et nécessite obligatoirement deux blocs distincts séparés par UNION ALL. La condition d’arrêt dans le membre récursif est syntaxiquement facultative mais fonctionnellement obligatoire pour éviter une boucle infinie. C’est uniquement cette capacité d’auto-référencement qui la rend adaptée aux structures de données hiérarchiques de profondeur inconnue.
| Critère | CTE standard | CTE récursive |
|---|---|---|
| Auto-référencement | Non | Oui |
Blocs SELECT requis | Un seul | Deux (UNION ALL obligatoire) |
| Condition d’arrêt | Non applicable | Fonctionnellement obligatoire |
| Cas d’usage principal | Calcul ou filtrage simple | Données hiérarchiques variables |
| Risque de boucle infinie | Non | Oui si mal bornée |
| Syntaxe MySQL/PostgreSQL | WITH | WITH RECURSIVE |
Quelle est la différence entre une jointure interne et une CTE récursive ?
Un INNER JOIN assemble deux tables en une seule passe selon une condition de correspondance entre colonnes définies à l’écriture de la requête. Il ne peut traverser qu’un nombre fixe de niveaux hiérarchiques car chaque niveau supplémentaire exige une jointure supplémentaire écrite manuellement dans l’instruction. Sur une hiérarchie à trois niveaux, trois jointures distinctes sont nécessaires ; l’apparition d’un quatrième niveau impose une réécriture complète. La CTE récursive s’affranchit de cette contrainte en adaptant automatiquement son parcours à la profondeur réelle de la structure au moment de l’exécution. Cette adaptabilité dynamique est son avantage fondamental face au INNER JOIN sur des hiérarchies dont la profondeur peut évoluer avec les données.
On confronte ici les deux approches sur une même arborescence pour mettre en évidence concrètement leurs différences structurelles.
-- INNER JOIN : profondeur figée à 3 niveaux, non adaptable
SELECT c1.nom AS niveau_1, c2.nom AS niveau_2, c3.nom AS niveau_3
FROM categories c1
INNER JOIN categories c2 ON c2.id_parent = c1.id_categorie
INNER JOIN categories c3 ON c3.id_parent = c2.id_categorie
WHERE c1.id_parent IS NULL;
-- CTE récursive : tous les niveaux parcourus dynamiquement
WITH RECURSIVE arborescence AS (
SELECT id_categorie, nom, id_parent, 0 AS niveau
FROM categories
WHERE id_parent IS NULL
UNION ALL
SELECT c.id_categorie, c.nom, c.id_parent, a.niveau + 1
FROM categories c
INNER JOIN arborescence a ON c.id_parent = a.id_categorie
)
SELECT nom, niveau FROM arborescence ORDER BY niveau, nom;La version INNER JOIN retourne uniquement les nœuds situés exactement au troisième niveau et ignore tous les autres niveaux de l’arbre. La CTE récursive parcourt l’intégralité de l’arborescence sans modification du code, quelle que soit l’évolution de la structure en base. Si un cinquième niveau de catégorie est ajouté en base, la CTE récursive l’intègre automatiquement sans intervention du développeur.
Quand utiliser une CTE récursive ?
La CTE récursive s’impose dans quatre cas d’usage documentés par les SGBD officiels : arborescences de catégories, organigrammes de collaborateurs, nomenclatures produit et graphes de dépendances entre objets. Elle est aussi employée pour générer des séquences numériques ou des séries de dates consécutives sans table de référence préexistante dans la base. Évitez de l’utiliser sur des hiérarchies fixes à deux niveaux qu’un simple INNER JOIN traite plus directement et plus efficacement. Réservez la recursive CTE aux structures dont la profondeur est variable, inconnue à l’avance ou susceptible d’évoluer avec les données. Elle devient également pertinente quand vous avez besoin d’inclure la colonne niveau dans le résultat pour représenter la position d’un nœud dans la hiérarchie.
On génère ici une série de dates consécutives sur un mois entier sans aucune table de référence dans la base de données.
WITH RECURSIVE serie_dates AS (
SELECT CAST('2024-01-01' AS DATE) AS date_courante
UNION ALL
SELECT DATE_ADD(date_courante, INTERVAL 1 DAY)
FROM serie_dates
WHERE date_courante < '2024-01-31'
)
SELECT date_courante FROM serie_dates;Le membre ancre initialise la série au 1er janvier 2024 et le membre récursif incrémente la date d’un jour à chaque itération. La clause WHERE date_courante < '2024-01-31' constitue la condition d’arrêt explicite qui stoppe la boucle au dernier jour du mois. Ce type de génération de série est utile pour produire des rapports couvrant toutes les dates d’une période, même si aucune donnée n’a été enregistrée ce jour-là.
Quelles sont les limites d’une CTE récursive ?
L’absence ou l’erreur dans la condition d’arrêt du membre récursif est le risque le plus critique : elle déclenche une boucle infinie immédiate. SQL Server impose par défaut un plafond de 100 niveaux de récursion, ajustable via l’option MAXRECURSION placée dans la clause OPTION en fin d’instruction. MySQL et PostgreSQL appliquent leurs propres limites configurables pour protéger les ressources du serveur contre les récursions non bornées. Une recursive CTE ne pouvant pas être indexée directement, les performances se dégradent significativement sur des hiérarchies comportant plusieurs milliers de nœuds. Pour des arborescences très volumineuses, une table de fermeture (closure table) ou un modèle de jeu d’intervalles (nested set) constituent des alternatives structurellement plus performantes.
Sources: MySQL , PostgreSQL, SqlServer