Niveau 20 : Requêtes récursives : WITH RECURSIVE

Leçon

Une CTE récursive est une CTE qui se lit elle-même. Elle sert à deux choses que les requêtes ordinaires ne savent pas faire : générer une série de valeurs qui n'existe dans aucune table, et parcourir une hiérarchie dont on ne connaît pas la profondeur.

Elle a deux parties reliées par UNION ALL. Le cas de départ est un SELECT ordinaire qui produit les premières lignes. L'étape récursive est un SELECT qui lit la CTE elle-même et fabrique de nouvelles lignes à partir de celles produites au tour précédent.

SQL déroule le calcul tour par tour. Dans l'exemple, le départ produit x = 1. Au tour 1, l'étape lit 1 et produit 2 ; au tour 2, elle lit 2 et produit 3, et ainsi de suite jusqu'à 5. Au tour suivant, la condition x < 5 est fausse pour 5 : l'étape ne produit plus rien et le calcul s'arrête. Le résultat réunit toutes les lignes produites : 1, 2, 3, 4, 5. Le WHERE de l'étape récursive est donc la condition d'arrêt.

Usage 1, les séries. Avec date(day, '+1 day') ou strftime('%Y-%m', month || '-01', '+1 month'), vus au niveau 6, on génère tous les jours ou tous les mois d'une période. Un LEFT JOIN y rattache ensuite les données : les mois sans activité apparaissent avec 0.

Usage 2, les hiérarchies. Le départ sélectionne la racine, par exemple l'employée sans manager (Alice, profondeur 0). Chaque tour joint staff à la CTE pour trouver les subordonnés des personnes trouvées au tour précédent : Bob, Claire et David au tour 1, leurs équipes au tour 2. Le calcul s'arrête quand plus personne n'a de subordonné.

Si les données contiennent un cycle (A manager de B, B manager de A), la requête tourne sans fin. Deux protections existent. La plus sûre : une limite de profondeur dans l'étape récursive, par exemple WHERE depth < 20. L'autre : relier le départ et l'étape par UNION au lieu de UNION ALL ; une ligne déjà produite n'est alors pas reproduite, et la boucle s'arrête quand le cycle revient sur une ligne identique. Attention : si la ligne contient un compteur comme depth, elle est toujours nouvelle et UNION ne suffit plus.

Comme toute requête, une CTE récursive ne garantit pas l'ordre des lignes : ajoute ORDER BY dans la requête finale dès que l'ordre compte.

Syntaxe

WITH RECURSIVE t(x) AS (
  SELECT départ
  UNION ALL
  SELECT x + 1
  FROM t
  WHERE x < fin
)
SELECT x
FROM t;

Exemple commenté

WITH RECURSIVE n(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1
  FROM n
  WHERE x < 5
)
SELECT x
FROM n;

Départ : 1. Chaque tour ajoute x + 1 tant que x < 5. Résultat : 1, 2, 3, 4, 5.

Résultat de l’exemple

À retenir

• Départ UNION ALL étape récursive.
• L'étape récursive lit la CTE elle-même.
• Le WHERE de l'étape récursive arrête la boucle.

Pièges fréquents

• Oublier la condition d'arrêt : la requête tourne sans fin.
• Le nombre de colonnes du départ et de l'étape récursive doit être le même.

Les 5 exercices du niveau

1. Guidé · Affiche les nombres de 1 à 10 dans une colonne x. (Table : )
2. Entraînement · Affiche tous les jours du 1er au 7 juillet 2025 dans une colonne day. (Table : )
3. Entraînement · Affiche le nom de toutes les personnes qui travaillent sous la responsabilité de Bob (id 2), directement ou indirectement. (Table : staff)
4. Entraînement · Affiche chaque employé avec sa profondeur dans l'organigramme (depth) : 0 pour la personne sans manager, 1 pour ses subordonnés directs, etc. (Table : staff)
5. Défi · Affiche le nombre de prêts de chaque mois de janvier à juin 2025 (format AAAA-MM), y compris les mois sans aucun prêt, dans l'ordre chronologique. (Table : loans)

Dans SpeedQL, chaque requête est corrigée tout de suite : le résultat est comparé à celui attendu, puis la requête est relancée sur une base de contrôle cachée. Chaque exercice a ses indices écrits, à afficher seulement si tu bloques.

Voir aussi : la fiche de l’aide-mémoire · les exercices SQL corrigés sur cette notion