Les requêtes récursives SQL permettent de traiter des structures hiérarchiques complexes comme les organigrammes d'entreprise ou les catégories de produits. Grâce à l'instruction WITH RECURSIVE, vous pouvez naviguer dans des relations parent-enfant de manière élégante et performante.
Cette fonctionnalité avancée transforme des problématiques complexes en solutions simples. Que vous analysiez une structure organisationnelle ou calculiez des chemins dans un graphe, les CTE récursives offrent une approche puissante pour résoudre ces défis techniques fréquents en entretien.
📌 Ce qu'il faut retenir
- WITH RECURSIVE permet de traiter les structures hiérarchiques
- Composée d'un terme d'ancrage et d'un terme récursif
- Supportée par PostgreSQL, SQL Server, MySQL 8.0+
- Idéale pour les organigrammes et classifications
Principe des requêtes récursives SQL
Une requête récursive SQL fonctionne en deux étapes distinctes. Le terme d'ancrage définit le point de départ, tandis que le terme récursif explore progressivement la hiérarchie.
WITH RECURSIVE hierarchy AS (
-- Terme d'ancrage : point de départ
SELECT id, nom, parent_id, 1 as niveau
FROM employes
WHERE parent_id IS NULL
UNION ALL
-- Terme récursif : exploration
SELECT e.id, e.nom, e.parent_id, h.niveau + 1
FROM employes e
JOIN hierarchy h ON e.parent_id = h.id
)
SELECT * FROM hierarchy ORDER BY niveau, nom;
Cette structure permet de parcourir l'ensemble de l'arbre hiérarchique en une seule requête. Le moteur SQL répète automatiquement le terme récursif jusqu'à ce qu'aucune nouvelle ligne ne soit trouvée.
Syntaxe détaillée de WITH RECURSIVE
La syntaxe varie légèrement selon les SGBD. PostgreSQL et MySQL utilisent WITH RECURSIVE, tandis que SQL Server emploie une approche similaire sans le mot-clé RECURSIVE.
PostgreSQL offre la syntaxe la plus claire :
WITH RECURSIVE nom_cte (colonnes) AS (
-- Requête d'ancrage (non récursive)
SELECT ...
UNION [ALL]
-- Requête récursive
SELECT ... FROM nom_cte ...
)
SELECT * FROM nom_cte;
SQL Server utilise une approche équivalente mais plus concise :
WITH hierarchy AS (
SELECT id, nom, parent_id, 0 as niveau
FROM employes
WHERE parent_id IS NULL
UNION ALL
SELECT e.id, e.nom, e.parent_id, h.niveau + 1
FROM employes e
INNER JOIN hierarchy h ON e.parent_id = h.id
)
SELECT * FROM hierarchy;
Cas d'usage pratiques en entreprise
Les requêtes récursives brillent dans plusieurs contextes métier. L'analyse d'organigrammes représente l'usage le plus fréquent en entretien technique.
Imaginez une table employes avec les colonnes id, nom, manager_id. Pour afficher la hiérarchie complète sous un directeur :
WITH RECURSIVE equipe AS (
-- Manager principal
SELECT id, nom, manager_id, nom as chemin, 0 as profondeur
FROM employes
WHERE id = 1001 -- ID du directeur
UNION ALL
-- Équipe sous sa responsabilité
SELECT e.id, e.nom, e.manager_id,
eq.chemin || ' > ' || e.nom,
eq.profondeur + 1
FROM employes e
JOIN equipe eq ON e.manager_id = eq.id
)
SELECT nom, chemin, profondeur
FROM equipe
ORDER BY profondeur, nom;
Cette approche génère automatiquement le chemin hiérarchique et calcule la profondeur organisationnelle.
Gestion des catégories de produits
L'e-commerce utilise massivement les hiérarchies de catégories. Une requête récursive permet d'extraire toutes les sous-catégories d'une famille produit.
WITH RECURSIVE categories_arbre AS (
-- Catégorie racine : Électronique
SELECT id, nom, parent_id, 1 as niveau, nom as chemin_complet
FROM categories
WHERE nom = 'Électronique' AND parent_id IS NULL
UNION ALL
-- Toutes les sous-catégories
SELECT c.id, c.nom, c.parent_id,
ca.niveau + 1,
ca.chemin_complet || ' / ' || c.nom
FROM categories c
JOIN categories_arbre ca ON c.parent_id = ca.id
)
SELECT id, nom, niveau, chemin_complet
FROM categories_arbre
WHERE niveau <= 4 -- Limiter la profondeur
ORDER BY niveau, nom;
Cette requête génère l'arborescence complète avec le chemin de navigation pour chaque catégorie.
Différences entre SGBD
| SGBD | Syntaxe | Limite de récursion | Optimisations |
|---|---|---|---|
| PostgreSQL | WITH RECURSIVE | Configurable | Très bonnes |
| SQL Server | WITH (sans RECURSIVE) | 100 niveaux par défaut | Excellentes |
| MySQL 8.0+ | WITH RECURSIVE | 1000 par défaut | Bonnes |
| Oracle | CONNECT BY | Pas de limite | Spécialisées |