Parcourir une hiérarchie

Fait partie de la section Au-delà des bases du Journey SQL de Coddy — leçon 21 sur 27.

La véritable raison d'être des CTE récursives : parcourir une relation parent/enfant stockée dans une seule table. Supposons que employees possède une colonne manager_id qui pointe vers la même table :

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7              -- ancrage : l'employé de départ
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id      -- récursif : passer au manager
)
SELECT * FROM chain

Chaque étape joint la CTE à employees, remontant d'un niveau dans l'arborescence. La récursion s'arrête naturellement lorsqu'une ligne n'a pas de manager (JOIN ne correspond à rien).

Défi

Moyen

Tables et colonnes disponibles :
categories : id, name, parent_id

Chaque catégorie a un parent_id optionnel pointant vers une autre catégorie. Retournez toutes les catégories qui sont des descendantes de la catégorie id = 1 (ses enfants, petits-enfants, …) mais pas la catégorie 1 elle-même. Retournez id et name, triés par id.

Aide-mémoire

Utilisez les CTE récursives pour parcourir les relations parent/enfant stockées dans une seule table (par exemple, une colonne manager_id ou parent_id pointant vers la même table) :

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7          -- ancrage : ligne de départ
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id  -- récursif : saut vers le parent
)
SELECT * FROM chain

La récursion s'arrête naturellement lorsque le JOIN ne trouve aucune ligne correspondante (par exemple, une ligne sans manager).

Essayez vous-même

WITH RECURSIVE descendants AS (
    -- ancre : enfants directs de l'id 1
    -- récursif : leurs enfants, etc.
)
SELECT id, name FROM descendants ORDER BY id