CTE récursifs : c'est quoi et pourquoi on en a besoin

Aujourd'hui, on va faire un pas de plus et plonger dans la magie de la récursivité. Si t'as déjà codé dans un langage qui gère la récursivité (genre Python), tu vois un peu le délire. Mais t'inquiète, même si ça te paraît chelou, on va tout décortiquer tranquille.

Les CTE récursifs, c'est l'outil ultime pour gérer des structures de données hiérarchiques ou en arbre, comme les organigrammes d'entreprise, les arbres généalogiques ou les arborescences de fichiers.

En gros, ce sont des expressions qui peuvent "s'appeler elles-mêmes" pour parcourir et traiter tous les niveaux de données petit à petit.

Points clés des CTE récursifs :

1. Ils utilisent le mot-clé WITH RECURSIVE.
2. Un CTE récursif est composé de deux parties :
   • La requête de base : elle définit le point de départ (ou la "racine") de la récursivité.
   • La requête récursive : elle traite le reste des données en utilisant le résultat de l'étape précédente.

L'algo d'un CTE récursif, c'est comme monter un escalier :

• D'abord tu poses le pied sur la première marche (c'est la requête de base).
• Puis tu montes à la deuxième marche en utilisant le résultat de la première (la requête récursive).
• Et tu répètes jusqu'à ce qu'il n'y ait plus de marches (quand la condition d'arrêt est atteinte).

Syntaxe d'un CTE récursif

Regarde direct un exemple type :

WITH RECURSIVE cte_name AS (
    -- Requête de base
    SELECT column1, column2
    FROM table_name
    WHERE condition_pour_le_cas_de_base

    UNION ALL

    -- Requête récursive
    SELECT column1, column2
    FROM table_name
    JOIN cte_name ON some_condition
    WHERE condition_arret
)
SELECT * FROM cte_name;

Le rôle de UNION et UNION ALL dans les CTE récursifs

Chaque CTE récursif doit utiliser les opérateurs UNION ou UNION ALL entre la partie de base et la partie récursive.

Quel opérateur choisir : UNION ou UNION ALL ?

Si tu sais pas trop lequel prendre — prends quasi toujours UNION ALL. Pourquoi ? Parce qu'il est plus rapide : il fusionne juste les résultats sans checker les doublons. Donc moins de calculs, moins de ressources, résultat plus vite.

C'est super important dans les CTE récursifs. Quand tu construis des hiérarchies — genre un arbre de commentaires ou la structure des subordonnés dans une boîte — UNION ALL est quasi toujours le bon choix. Si tu utilises juste UNION, la base peut croire que certains steps sont déjà faits et "couper" une partie du résultat. Et là, ça casse toute la logique du parcours.

Utilise UNION seulement si tu sais que les doublons sont vraiment gênants et qu'il faut les virer. Mais souviens-toi : c'est toujours un compromis entre propreté et rapidité.

Exemple des deux approches

-- UNION : les doublons sont exclus
SELECT 'A'
UNION
SELECT 'A';     -- Résultat : une seule ligne 'A'

-- UNION ALL : les doublons sont gardés
SELECT 'A'
UNION ALL
SELECT 'A';     -- Résultat : deux lignes 'A'

Dans les requêtes récursives, c'est plus safe d'utiliser toujours UNION ALL pour ne pas perdre des étapes importantes dans le parcours de la structure.

Regarde un cas classique : on a une table d'employés avec les colonnes employee_id, manager_id et name. On veut construire la hiérarchie en partant du directeur — la personne sans chef (manager_id = NULL).

Imaginons qu'on a la table des employés : employees

On veut savoir qui dépend de qui, et connaître le niveau de chaque employé dans la structure. C'est pratique si tu veux afficher l'arbre des employés dans une interface ou préparer un rapport sur la structure d'équipe.

WITH RECURSIVE employee_hierarchy AS (
    -- On commence par ceux qui n'ont pas de chef
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- On ajoute les subordonnés et on augmente le niveau
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

Le résultat sera comme ça :

Cette requête montre bien comment on peut "parcourir" la hiérarchie des employés — du boss jusqu'aux plus juniors. Le champ level est super utile pour formater ou visualiser l'arbre.

Exemple : catégories de produits

Maintenant imagine qu'on bosse avec une table de catégories de produits, où chaque catégorie peut avoir des sous-catégories, et elles-mêmes leurs propres sous-catégories. Comment on construit l'arbre des catégories ?

Table categories

Requête récursive :

WITH RECURSIVE category_tree AS (
    -- Cas de base : trouver les catégories racines
    SELECT
        category_id,
        name,
        parent_id,
        1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Partie récursive : trouver les sous-catégories des catégories actuelles
    SELECT
        c.category_id,
        c.name,
        c.parent_id,
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;

Résultat :

Maintenant on voit bien l'arbre des catégories avec les niveaux d'imbrication.

Pourquoi les CTE récursifs, c'est trop cool ?

Les CTE récursifs, c'est un des outils les plus expressifs et puissants de SQL. Au lieu de te prendre la tête avec de la logique imbriquée, tu dis juste par où commencer (cas de base) et comment avancer (partie récursive) — et PostgreSQL fait tout le reste.

Le plus souvent, on utilise ce genre de requêtes pour parcourir des hiérarchies : employés, catégories de produits, dossiers sur un disque, graphes dans les réseaux sociaux. C'est facile à étendre : si tu ajoutes des données dans la table, la requête les prend en compte direct. Pratique et scalable.

Mais y'a des pièges. Faut toujours faire gaffe aux conditions d'arrêt — sinon ta requête peut partir en boucle infinie. N'oublie pas les index : sur de grosses tables, les requêtes récursives sans index peuvent ramer. Et UNION ALL — c'est quasi toujours le meilleur choix, surtout pour les hiérarchies, sinon tu risques de perdre des étapes à cause de la suppression des doublons.

Un CTE récursif bien fait te permet d'exprimer une logique métier complexe en quelques lignes — sans procédures, boucles ou code en plus. C'est le genre de moment où SQL est à la fois efficace et élégant.

Erreurs classiques avec les CTE récursifs

• Récursivité infinie : si tu mets pas une bonne condition d'arrêt (WHERE), ta requête peut tourner en boucle.
• Données en trop : mal utiliser UNION ALL peut rajouter des doublons.
• Performance : les requêtes récursives peuvent être lourdes sur de gros volumes de données. Mets des index sur les colonnes clés (genre manager_id) pour accélérer l'exécution.

Quand on ne peut pas se passer des requêtes récursives

Parfois, on croit que les requêtes récursives, c'est juste de la théorie, mais en vrai, on en croise souvent dans le dev de tous les jours. Par exemple :

• pour faire des rapports sur la structure d'une boîte ou la classification des produits ;
• pour parcourir un arbre de dossiers et lister toutes les sous-répertoires ;
• pour analyser des graphes — liens sociaux, itinéraires, dépendances entre tâches ;
• pour juste présenter des relations complexes entre objets de façon lisible.

Si tu dois parcourir une structure où un truc dépend d'un autre, — y'a de grandes chances que WITH RECURSIVE te sauve la vie.