0Pricing
SQL Academy · Leçon

Fonctionnement des CTE récursives

Un cas de base suivi d’une étape récursive.

Fonctionnement des CTE récursives est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 1 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce qu'une CTE récursive ?

Une CTE récursive est une expression de table commune qui se référence elle-même. Elle vous permet d'écrire des requêtes qui répètent une étape jusqu'à ce qu'une condition soit remplie — comme une boucle, mais exprimée en SQL pur.

Les CTE récursives sont définies avec le mot-clé WITH RECURSIVE et sont idéales pour parcourir des données hiérarchiques ou semblables à des graphes, comme des organigrammes, des arborescences de dossiers et des structures de nomenclature.

Structure en deux parties

Chaque CTE récursive comporte exactement deux parties séparées par UNION ALL :

1. Cas de base — un SELECT non récursif qui renvoie les lignes de départ.

2. Étape récursive — un SELECT qui effectue une jointure entre la CTE et elle-même afin de produire le niveau de lignes suivant.

Le moteur exécute l'étape récursive en continu et accumule les résultats jusqu'à ce qu'aucune nouvelle ligne ne soit produite.

WITH RECURSIVE cte_name AS (
  -- Base case
  SELECT ...
  UNION ALL
  -- Recursive step (references cte_name)
  SELECT ... FROM source JOIN cte_name ON ...
)
SELECT * FROM cte_name;

Compter de 1 à 5

La CTE récursive la plus simple sert à compter des nombres. Le cas de base initialise la valeur 1. L'étape récursive ajoute 1 à chaque itération. La clause WHERE de l'étape récursive joue le rôle de condition d'arrêt : sans elle, la requête s'exécuterait indéfiniment.

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

Exécution étape par étape

Voici comment le moteur traite la CTE de compteur, itération par itération :

Itération 0 (cas de base) : renvoie {1}.

Itération 1 : applique l'étape récursive à {1}, puis renvoie {2}.

Itération 2 : applique l'étape récursive à {2}, puis renvoie {3}.

Itérations 3 et 4 : renvoient successivement {4}, puis {5}.

Itération 5 : WHERE n < 5 est faux pour n=5, donc aucune ligne n'est renvoyée. La requête se termine.

Toutes les lignes accumulées — 1, 2, 3, 4, 5 — constituent le résultat final.

Préparer une table hiérarchique

Les CTE récursives sont particulièrement adaptées aux tables autoréférentes. Créons une table employees dans laquelle chaque employé possède un manager_id facultatif qui renvoie vers la même table.

CREATE TABLE employees (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR(50),
  manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
  (1, 'Alice',   NULL),
  (2, 'Bob',     1),
  (3, 'Carol',   1),
  (4, 'Dave',    2),
  (5, 'Eve',     2),
  (6, 'Frank',   3);

Parcourir la hiérarchie

Nous pouvons maintenant parcourir toute la chaîne hiérarchique en partant du CEO (Alice, id=1). Le cas de base sélectionne Alice ; l'étape récursive trouve tous les employés dont le manager_id correspond à un identifiant déjà présent dans la CTE.

Le résultat inclut chaque employé atteignable depuis Alice, quelle que soit la profondeur de l'arbre.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT depth, name FROM org_tree ORDER BY depth, name;

Suivre le chemin

Une amélioration courante consiste à créer une chaîne de chemin qui montre la chaîne complète de la racine à chaque nœud. Nous concaténons les noms en les séparant par ' -> ' à mesure que la récursion descend.

Cela facilite l'affichage d'une navigation de type fil d'Ariane ou le diagnostic des hiérarchies profondes.

WITH RECURSIVE org_tree AS (
  SELECT id, name, name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.path || ' -> ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;

Limiter la profondeur de récursion

Des données représentant une hiérarchie profonde ou circulaire peuvent faire fonctionner une CTE récursive très longtemps. Voici deux pratiques sûres :

1. Suivez la profondeur et ajoutez une clause WHERE — WHERE depth < 10 garantit que vous ne dépassez jamais 10 niveaux.

2. Utilisez une colonne de détection des cycles — certaines bases de données (PostgreSQL 14+) proposent la syntaxe CYCLE pour détecter automatiquement les visites répétées d'un même nœud.

WITH RECURSIVE org_tree AS (
  SELECT id, name, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 10
)
SELECT depth, name FROM org_tree;

UNION ou UNION ALL dans les CTE récursives

L'étape récursive utilise presque toujours UNION ALL, et non UNION. Voici pourquoi :

UNION déduplique les lignes après chaque itération en comparant l'ensemble complet des résultats — cette opération est extrêmement coûteuse et peut modifier la sémantique des graphes dans lesquels le même nœud est légitimement atteint par plusieurs chemins.

UNION ALL conserve toutes les lignes sans déduplication, ce qui est à la fois plus rapide et correct pour parcourir un arbre. Utilisez UNION uniquement lorsque vous devez éliminer les doublons et que vous comprenez le coût en performances.

Générer une série de dates

Les CTE récursives sont également pratiques pour générer des suites de dates. Cet exemple produit chaque jour d'une semaine donnée — un modèle souvent utilisé pour créer des rapports calendaires ou combler les lacunes dans les données de séries temporelles.

WITH RECURSIVE date_series AS (
  SELECT DATE '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL '1 day'
  FROM date_series
  WHERE day < DATE '2024-01-07'
)
SELECT day FROM date_series;

Trouver tous les subordonnés d'un responsable

Vous pouvez initialiser le cas de base avec n'importe quel nœud précis — pas uniquement la racine. Ici, nous commençons par Bob (id=2) et trouvons toutes les personnes qui lui sont directement ou indirectement rattachées.

Ce modèle est utile pour les vérifications des autorisations, les agrégations de sous-arbres ou la limitation du périmètre des tableaux de bord à un seul service.

WITH RECURSIVE subordinates AS (
  SELECT id, name
  FROM employees
  WHERE id = 2
  UNION ALL
  SELECT e.id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT name FROM subordinates;

Vérification rapide

Vérifiez votre compréhension du fonctionnement des CTE récursives.

Récapitulatif de la leçon

Dans cette leçon, vous avez découvert le fonctionnement des CTE récursives :

Structure : chaque CTE récursive comporte un cas de base (les lignes de départ) relié à une étape récursive (un SELECT autoréférent) avec UNION ALL.

Arrêt : le moteur répète l'étape récursive et accumule les résultats jusqu'à ce que l'étape renvoie zéro ligne.

Utilisations courantes : parcourir des organigrammes et des arborescences de dossiers, générer des suites de nombres ou de dates, calculer des chemins et trouver tous les nœuds d'un sous-arbre.

Conseils de sécurité : incluez toujours une condition d'arrêt (limite de profondeur ou garde-fou contre les cycles) et préférez UNION ALL à UNION pour de meilleures performances.

Questions Fréquemment Posées

La leçon « Fonctionnement des CTE récursives » est-elle gratuite ?

Oui — le texte complet de « Fonctionnement des CTE récursives » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Fonctionnement des CTE récursives » ?

Un cas de base suivi d’une étape récursive. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Academy ?

Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 1 sur 4.

Combien de temps prend la leçon « Fonctionnement des CTE récursives » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?

Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. Fonctionnement des CTE récursives
  2. Parcourir un arbre de catégories
  3. Générer des séries et des séquences
  4. Éviter les boucles infinies
← Retour à SQL Academy