Une CTE récursive s'appelle elle-même, pour parcourir une hiérarchie sur un nombre de niveaux inconnu à l'avance — impossible avec une simple jointure, qui exige de connaître le nombre de niveaux pour enchaîner les JOIN.

Voici une table categories, avec une hiérarchie parent/enfant :

categories
id | nom                    | parent_id
1  | Électronique           | (NULL)
2  | Ordinateurs            | 1
3  | Ordinateurs portables  | 2
4  | Téléphones             | 1
5  | Vêtements              | (NULL)
6  | Vêtements homme        | 5

WITH RECURSIVE hierarchie AS (
  SELECT id, nom, parent_id, 0 AS niveau
  FROM categories WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.nom, c.parent_id, h.niveau + 1
  FROM categories c
  JOIN hierarchie h ON c.parent_id = h.id
)
SELECT * FROM hierarchie;

Une CTE récursive a toujours deux parties, reliées par UNION ALL : le terme d'ancrage (SELECT ... WHERE parent_id IS NULL), qui démarre la récursion sur les catégories racines (niveau 0) ; et le terme récursif (SELECT ... JOIN hierarchie ...), qui référence la CTE elle-même pour ajouter, à chaque tour, les enfants directs des lignes trouvées au tour précédent.

Résultat : les 6 catégories, chacune avec son niveau — 1 et 5 à 0, 2, 4 et 6 à 1, et 3 (enfant de 2) à 2.

À retenir :

• Une CTE récursive combine un terme d'ancrage (le point de départ) et un terme récursif (qui référence la CTE elle-même), reliés par UNION ALL.
• UNION ALL, et non UNION, est la norme pour une CTE récursive dans la plupart des moteurs.
• Le terme récursif ne voit, à chaque tour, que les lignes ajoutées au tour précédent — pas l'ensemble du résultat déjà accumulé.

À vous de jouer : le terme d'ancrage seul (avant toute récursion) inclut-il la catégorie 'Ordinateurs portables' (id 3) ?