Requêtes récursives SQL : maîtrisez WITH RECURSIVE

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.

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

Oracle propose une approche alternative avec CONNECT BY PRIOR, plus ancienne mais toujours performante pour les hiérarchies complexes.

Optimisation des performances

Les requêtes récursives peuvent rapidement devenir coûteuses. Plusieurs stratégies optimisent leur exécution selon la taille des données.

L'ajout d'index sur les colonnes de jointure améliore drastiquement les performances :

-- Index essentiel pour les requêtes récursives
CREATE INDEX idx_employes_manager ON employes(manager_id);
CREATE INDEX idx_employes_id ON employes(id);

La limitation de profondeur évite les récursions excessives :

WITH RECURSIVE 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
  JOIN hierarchy h ON e.parent_id = h.id
  WHERE h.niveau < 10 -- Limite à 10 niveaux
)
SELECT * FROM hierarchy;

Cette approche protège contre les structures cycliques et améliore la prévisibilité des temps d'exécution.

Détection de cycles dans les données

Les données hiérarchiques peuvent contenir des cycles involontaires. Une requête récursive adaptée détecte ces anomalies structurelles.

WITH RECURSIVE detection_cycle AS (
  SELECT id, nom, parent_id, ARRAY[id] as chemin
  FROM employes 
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT e.id, e.nom, e.parent_id, dc.chemin || e.id
  FROM employes e
  JOIN detection_cycle dc ON e.parent_id = dc.id
  WHERE NOT (e.id = ANY(dc.chemin)) -- Évite les cycles
)
SELECT * FROM detection_cycle
WHERE array_length(chemin, 1) > 20; -- Cycles suspects

Cette technique PostgreSQL utilise les tableaux pour tracer le parcours et identifier les boucles infinies potentielles.

Agrégations sur structures récursives

Les fonctions d'agrégation enrichissent l'analyse des hiérarchies. Calculer le nombre de subordonnés par manager illustre cette capacité.

WITH RECURSIVE equipe_complete AS (
  SELECT id, nom, manager_id, 0 as niveau
  FROM employes 
  WHERE manager_id IS NULL
  
  UNION ALL
  
  SELECT e.id, e.nom, e.manager_id, ec.niveau + 1
  FROM employes e
  JOIN equipe_complete ec ON e.manager_id = ec.id
),
comptage_equipe AS (
  SELECT manager_id, COUNT(*) as nb_subordonnes
  FROM equipe_complete 
  WHERE manager_id IS NOT NULL
  GROUP BY manager_id
)
SELECT e.nom as manager, 
       COALESCE(ce.nb_subordonnes, 0) as taille_equipe
FROM employes e
LEFT JOIN comptage_equipe ce ON e.id = ce.manager_id
WHERE e.id IN (SELECT DISTINCT manager_id FROM employes WHERE manager_id IS NOT NULL)
ORDER BY taille_equipe DESC;

Cette analyse croise données récursives et agrégations pour produire des métriques organisationnelles utiles.

Cas d'usage en analyse de données

Les data analysts utilisent fréquemment les requêtes récursives pour l'analyse de parcours client ou la segmentation comportementale. Ces techniques apparaissent régulièrement dans les entretiens SQL avancés.

La construction de séquences temporelles illustre cette application :

WITH RECURSIVE sequence_dates AS (
  SELECT DATE '2026-01-01' as date_courante
  
  UNION ALL
  
  SELECT date_courante + INTERVAL '1 day'
  FROM sequence_dates
  WHERE date_courante < DATE '2026-12-31'
)
SELECT date_courante, 
       EXTRACT(DOW FROM date_courante) as jour_semaine,
       CASE WHEN EXTRACT(DOW FROM date_courante) IN (0,6) 
            THEN 'Weekend' ELSE 'Semaine' END as type_jour
FROM sequence_dates
ORDER BY date_courante;

Cette approche génère automatiquement un calendrier complet pour les analyses temporelles.

Requêtes récursives et Big Data

Les environnements Big Data comme Spark SQL supportent partiellement les CTE récursives. L'adaptation nécessite souvent une approche itérative externe.

La stratégie recommandée décompose la récursion en étapes :

1. Extraction du niveau racine
2. Itérations successives par jointures
3. Union des résultats intermédiaires

Cette méthode contourne les limitations techniques tout en conservant la logique hiérarchique. Les fonctions fenêtre SQL complètent souvent cette approche pour les analyses avancées.

Questions fréquentes

Conclusion

Les requêtes récursives SQL avec WITH RECURSIVE transforment l'analyse des structures hiérarchiques en une tâche accessible et performante. Cette fonctionnalité avancée résout élégamment des problématiques complexes d'organigrammes, de classifications ou de parcours dans des graphes.

Maîtriser cette technique vous démarque lors des entretiens techniques et enrichit significativement vos capacités d'analyse de données. La compréhension des nuances entre SGBD et des stratégies d'optimisation constitue un atout précieux pour tout data analyst moderne.

Perfectionnez vos compétences SQL avec notre plateforme d'exercices pratiques et préparez-vous efficacement à vos prochains défis techniques.