WITH RECURSIVE — générer des séquences et parcourir des hiérarchies avec des requêtes récursives

Données utilisées dans cet article — la table employee

WITH RECURSIVE est une façon d'écrire un CTE qui s'appelle lui-même pour produire des lignes une à une.

Ce mouvement qui consiste à réinjecter le résultat précédent comme entrée suivante et à répéter s'appelle récursion.

Le matériel est la table employee (30 lignes ; la colonne manager_id pointe vers l'emp_id du manager, une structure auto-référencée).

Avant de commencer les exercices, regarde les définitions de colonnes et les données d'exemple de la table employee, ainsi que le SELECT minimal qu'on utilisera comme ancre d'un CTE récursif.

① Exécute SELECT 1; et confirme qu'il renvoie juste la valeur 1 sur une seule ligne (c'est le SELECT le plus simple qu'on utilisera comme ancre d'un CTE récursif).

② Exécute PRAGMA table_info(employee); pour vérifier les définitions de colonnes.

③ Exécute SELECT * FROM employee LIMIT 5; pour prévisualiser les 5 premières lignes. Remarque aussi comment NULL apparaît dans la colonne manager_id (les employés qui n'ont pas de manager).

Les 3 parties qui composent une requête récursive — ancre / terme récursif / UNION ALL

À l'aide du code ci-dessous, on va parcourir les 3 parties — ① l'ancre (SELECT 1) / ② le terme récursif (SELECT n + 1 FROM seq WHERE n < 5) / ③ `UNION ALL` — une à la fois.

-- Code de référence pour cette section. On couvrira les 3 parties ① / ② / ③ dans l'ordre
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- ① ancre
  UNION ALL                           -- ③ UNION ALL (la colle entre ancre et terme récursif)
  SELECT n + 1 FROM seq WHERE n < 5   -- ② terme récursif
)
SELECT n FROM seq;

-- Résultat (5 lignes) : n=1, 2, 3, 4, 5

① Ancre — la partie SELECT 1

L'ancre est le SELECT qui produit la première ligne.

SELECT 1 dans le code de référence est l'ancre — c'est juste un SELECT ordinaire, écrit une fois, qui ne se référence pas lui-même.

La valeur (1) renvoyée ici devient le premier contenu de seq(n), qui est ensuite passé au terme récursif comme valeur initiale de n.

② Terme récursif — la partie SELECT n + 1 FROM seq WHERE n < 5

-- Code de référence (répété). Cette section explique la ligne ② du terme récursif
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- ① ancre
  UNION ALL                           -- ③ UNION ALL (la colle)
  SELECT n + 1 FROM seq WHERE n < 5   -- ★ ② terme récursif ← ce qu'on explique ★
)
SELECT n FROM seq;

Dans WITH RECURSIVE seq(n), la partie seq(n) est la déclaration du nom du CTE (seq) et du nom de colonne (n). Le contenu commence par le `1` de l'ancre, et à partir de là il contient le résultat du terme récursif.

SELECT n + 1 signifie « la valeur précédente + 1 » à chaque passage.

Le n initial est 1, donc le n + 1 suivant est 2, puis 3, et ainsi de suite — cela continue jusqu'à ce que WHERE cesse de renvoyer des lignes.

③ UNION ALL — la partie `UNION ALL` du code

-- Code de référence (répété). Cette section explique la ligne ③ UNION ALL
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- ancre
  UNION ALL                           -- ★ ce qu'on explique ★ ressemble à une ligne, mais…
  SELECT n + 1 FROM seq WHERE n < 5   -- terme récursif
)
SELECT n FROM seq;

-- …à l'intérieur du SGBD c'est équivalent à « empiler ce que le terme récursif produit, un UNION ALL par itération » :

SELECT 1            -- ancre (s'exécute une seule fois)
UNION ALL           -- ← 1er empilage : on empile la sortie du passage 1
SELECT 2
UNION ALL           -- ← 2e empilage
SELECT 3
UNION ALL           -- ← 3e empilage
SELECT 4
UNION ALL           -- ← 4e empilage
SELECT 5;

-- Le résultat est [1, 2, 3, 4, 5] dans les deux cas
-- Autrement dit, le seul « UNION ALL » à l'intérieur de WITH RECURSIVE ≒ UNION ALL répété pour chaque itération

UNION ALL est un opérateur qui empile verticalement les résultats de deux SELECTs ou plus.

On utilise UNION ALL (qui garde toutes les lignes) au lieu de UNION (qui supprime les doublons) parce qu'on veut que chaque ligne produite pendant la récursion arrive dans le résultat final.

Exemple 1 — Générer une séquence (1 à 5)

Besoin : « Je veux générer à la volée une séquence de 1 à 10 sous forme de lignes, sans table de séquence existante. » Même sans table maître de séquence, tu peux la construire avec un CTE récursif. (Si tu l'exécutes correctement, l'explication apparaîtra.)

① Définis un CTE pour la séquence avec WITH RECURSIVE. L'ancre doit être un SELECT qui renvoie la valeur initiale 1.

② Dans le terme récursif, référence le CTE lui-même et renvoie la valeur précédente plus 1. La condition de terminaison doit être « continuer tant que la valeur actuelle est inférieure à 10 ».

③ Dans la requête principale, renvoie la séquence générée en ordre croissant.

Exemple 2 — Projection de croissance salariale (quel sera le salaire dans N ans avec 5 % par an ?)

L'exemple 1 était une séquence numérique abstraite, mais ici on va couvrir une récursion pratique qui démarre depuis de vraies données de la table `employee`.

À partir du salaire actuel de Sam (emp_id=19), on va projeter ce qu'il serait dans 5 ans s'il augmente de 5 % chaque année.

Il suffit de changer le terme récursif en projected * 1.05 et tu obtiens une série qui croît de façon composée.

-- Projection de croissance salariale : si le salaire de Sam (emp_id=19) augmente de 5 % par an, que vaut-il N ans plus tard ?
WITH RECURSIVE salary_growth(year, projected) AS (
  SELECT 0, salary                                     -- ancre : récupérer le salaire actuel depuis employee
  FROM employee WHERE emp_id = 19
  UNION ALL
  SELECT year + 1, projected * 1.05                    -- terme récursif : augmentation de 5 % par an
  FROM salary_growth
  WHERE year < 5
)
SELECT year, ROUND(projected) AS projected_salary
FROM salary_growth;

-- Résultat (6 lignes) : projection sur 5 ans à partir du salaire actuel de Sam 4100000
--  year | projected_salary
--  -----+-----------------
--    0  | 4100000   ← ancre (employee.salary tel quel)
--    1  | 4305000   ← terme récursif (4100000 × 1.05)
--    2  | 4520250   ← terme récursif (4305000 × 1.05)
--    3  | 4746263   ← terme récursif (4520250 × 1.05, ROUND appliqué)
--    4  | 4983576   ← terme récursif (4746262.5 × 1.05, ROUND appliqué)
--    5  | 5232754   ← terme récursif. Ensuite, year=5 rend WHERE year<5 faux → arrêt

Il y a deux différences par rapport à l'exemple de séquence.

① L'ancre récupère une valeur dans la table employee (SELECT 0, salary FROM employee WHERE emp_id = 19) — tu peux démarrer à partir de vraies données.

② C'est un CTE à 2 colonnes (year et projected), et le terme récursif met à jour les deux colonnes en même temps avec year + 1 et projected * 1.05.

Une fois que tu sais récupérer une valeur dans une vraie table dans l'ancre, tu peux écrire des simulations qui démarrent à partir de cette valeur (intérêts composés, projections démographiques, calculs d'étapes jusqu'à un objectif, et ainsi de suite).

Besoin : « À partir du salaire actuel de Bob (emp_id=2), je veux projeter ce qu'il serait dans 5 ans s'il augmente de 10 % par an. » (Si tu l'exécutes correctement, l'explication apparaîtra.)

① Définis le nom du CTE comme growth et les colonnes comme year et projected dans WITH RECURSIVE.

② L'ancre doit récupérer salary depuis employee pour emp_id = 2, avec year qui démarre à 0.

③ Le terme récursif doit renvoyer year + 1 et projected * 1.10, avec la condition de terminaison year < 5.

④ Dans la requête principale, renvoie year et ROUND(projected) AS projected_salary en ordre croissant.

Exemple 3 — Utilisation avec JOIN (parcourir la chaîne manager_id étape par étape)

Ensuite on va couvrir l'autre usage canonique des CTE récursifs, parcourir une hiérarchie auto-référencée.

employee.manager_id contient « l'emp_id de ton manager », donc en le suivant pas à pas tu peux remonter dans l'organigramme : employé → manager → manager du manager, et ainsi de suite.

Dans l'exemple de séquence on utilisait n+1 pour produire la valeur suivante ; ici on récupère la ligne suivante (le manager) avec un JOIN pour avancer dans la chaîne.

-- Parcours de la hiérarchie : depuis un employé de départ (emp_id=19, Sam) vers le haut à travers les managers
WITH RECURSIVE chain AS (
  SELECT emp_id, name, manager_id    -- ① ancre : la ligne de départ
  FROM employee WHERE emp_id = 19
  UNION ALL                          -- ③ UNION ALL
  SELECT e.emp_id, e.name, e.manager_id
  FROM employee e
  JOIN chain c ON e.emp_id = c.manager_id    -- ② terme récursif : récupérer la ligne jointe
)
SELECT emp_id, name, manager_id FROM chain;

-- Résultat (2 lignes) :
--  emp_id | name | manager_id
--  -------+------+-----------
--    19   | Sam  |    2        ← ancre (point de départ)
--     2   | Bob  |  NULL       ← ligne produite par le terme récursif ; manager_id est NULL donc le prochain passage s'arrête

① L'ancre (SELECT ... FROM employee WHERE emp_id = 19) récupère la ligne de départ.

Ici elle met la ligne d'emp_id=19 (Sam) dans chain comme premier contenu.

Note que WITH RECURSIVE chain AS (...) n'a pas de déclaration de nom de colonne comme chain(...). C'est parce que le SELECT de l'ancre donne déjà à chain des noms de colonnes naturels (emp_id / name / manager_id) à partir des colonnes de employee. Quand c'est déjà lisible, on peut l'omettre.

② Le terme récursif (JOIN chain c ON e.emp_id = c.manager_id) joint employee avec le chain précédent et récupère la ligne jointe dans chain.

Dès que le manager_id de la ligne jointe devient NULL, l'itération suivante ne trouve pas de partenaire de jointure, le résultat est 0 ligne, et la récursion s'arrête naturellement.

(Pas besoin d'une condition de terminaison explicite comme le WHERE n < 5 de l'exemple de séquence — la fin de la hiérarchie est la condition de terminaison.)

Besoin : « À partir de emp_id = 19 (Sam), je veux remonter la chaîne manager_id — le manager de Sam, puis le manager de cette personne, et ainsi de suite — pour lister la chaîne des niveaux. »

① Définis un CTE pour la hiérarchie avec WITH RECURSIVE. L'ancre doit récupérer une ligne (emp_id, name, manager_id) depuis employee pour emp_id = 19.

② Dans le terme récursif, joins employee avec le CTE sur « employee.emp_id = le manager_id du CTE » et récupère la ligne du manager (emp_id, name, manager_id) une étape à la fois.

③ Dans la requête principale, renvoie emp_id, name, manager_id.

La pratique 3 remontait du bas vers le haut à travers les managers.

Maintenant on va dans l'autre sens : « À partir de emp_id = 1 (Alice), je veux descendre la chaîne manager_id à travers les collaborateurs d'Alice, puis leurs collaborateurs, et ainsi de suite — pour lister tous les membres de l'équipe d'Alice ».

① Définis un CTE pour les collaborateurs avec WITH RECURSIVE. L'ancre doit récupérer une ligne (emp_id, name, manager_id) depuis employee pour emp_id = 1.

② Dans le terme récursif, joins employee avec le CTE sur « employee.manager_id = l'emp_id du CTE » et récupère les collaborateurs de la ligne actuelle (emp_id, name, manager_id) une étape à la fois.

③ Dans la requête principale, renvoie emp_id, name, manager_id en ordre croissant de emp_id.

Exemple 4 — Récursion qui produit plusieurs lignes à la fois (tout l'organigramme)

L'exemple 3 parcourait une ligne à la fois (Sam → Bob → arrêt).

Ici on va couvrir le cas où l'ancre ou le terme récursif produit plusieurs lignes à la fois.

Le scénario : « Partir des 5 cadres dirigeants (employés sans manager) et récupérer tout l'organigramme de 30 personnes d'un coup. » Tu verras la requête récursive parcourir plusieurs branches en parallèle de façon en largeur.

-- Partir de tous les cadres dirigeants (employés sans manager) et récupérer récursivement toute l'organisation
WITH RECURSIVE org_tree AS (
  -- ① ancre : 5 employés avec manager_id = NULL (renvoie plusieurs lignes à la fois)
  SELECT emp_id, name, manager_id
  FROM employee
  WHERE manager_id IS NULL
  UNION ALL
  -- ② terme récursif : récupérer les collaborateurs directs des lignes précédentes (aussi plusieurs lignes)
  SELECT e.emp_id, e.name, e.manager_id
  FROM employee e
  JOIN org_tree t ON e.manager_id = t.emp_id
)
SELECT emp_id, name, manager_id FROM org_tree
ORDER BY emp_id;

-- Résultat (30 lignes = toute l'entreprise) :
--   Ancre (5 lignes) : emp_id=1 Alice, 2 Bob, 3 Carol, 9 Ivan, 28 Brian
--   Terme récursif passage 1 (25 lignes) : 9 collaborateurs d'Alice + 8 de Bob + 8 de Carol
--   Terme récursif passage 2 (0 ligne) : les collaborateurs n'ont pas de collaborateurs → arrêt
--   Total : 5 + 25 = 30 lignes

Il y a deux différences par rapport à l'exemple 3.

① L'ancre renvoie plusieurs lignes (5) à la fois — chaque ligne correspondant à WHERE manager_id IS NULL est utilisée pour initialiser le CTE.

② Le terme récursif parcourt plusieurs branches en parallèle — avec 5 personnes dans org_tree, un passage du terme récursif récupère les collaborateurs de chaque personne en même temps dans la même itération (9 d'Alice + 8 de Bob + 8 de Carol = 25 lignes).

Autrement dit, les requêtes récursives peuvent faire plus que « plonger plus profond une ligne à la fois » — elles peuvent aussi développer plusieurs branches en parallèle en largeur.

Besoin : « À partir de Bob (emp_id=2) et Carol (emp_id=3) en même temps, je veux récupérer leurs collaborateurs en une seule requête. » (Si tu l'exécutes correctement, l'explication apparaîtra.)

① Le nom du CTE doit être team dans WITH RECURSIVE ; omets la déclaration de nom de colonne (utilise les noms de colonnes naturels d'employee).

② L'ancre doit récupérer deux lignes depuis employee où emp_id est 2 ou 3.

③ Le terme récursif doit joindre employee avec le CTE sur « employee.manager_id = l'emp_id du CTE » et récupérer les collaborateurs.

④ Dans la requête principale, renvoie emp_id, name, manager_id en ordre croissant de emp_id.

Astuces — Pourquoi utiliser WITH RECURSIVE ?

Trois situations où WITH RECURSIVE brille

Les requêtes récursives sont pour les situations où le SQL ordinaire soit ne peut pas le faire, soit le rend vraiment pénible. Les principaux cas d'usage sont au nombre de trois :

① Parcourir une hiérarchie — organigrammes, dossiers, nomenclatures — structures auto-référencées dont tu ne connais pas la profondeur. Au lieu d'empiler plusieurs JOIN à la main, tu peux l'écrire dans une forme qui s'arrête naturellement à la fin des données.

② Générer beaucoup de lignes — séquences, dates consécutives, calendriers — au lieu d'écrire UNION ALL cent fois à la main, une seule ligne avec WHERE fait l'affaire.

③ Pousser une boucle côté application vers SQL — écrire une boucle dans l'application pour parcourir un ID parent rencontre le problème de requête N+1, mais une requête récursive le gère en une seule requête.

Si un JOIN ou un GROUP BY simple suffit, tu n'as pas besoin de récursion. Mais quand tu es dans une des trois situations ci-dessus, c'est le premier outil à atteindre.