0Pricing
SQL Interview Prep · Leçon

Membres d’ancrage et membres récursifs

La structure en deux parties d’un CTE récursif et le fonctionnement de l’arrêt

Membres d’ancrage et membres récursifs est une leçon SQL Interview Prep 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 Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Interview Prep comprend 4 leçons au total.

Pourquoi les CTE récursifs sont abordés

Lorsqu'un intervieweur vous présente un organigramme, une nomenclature ou une arborescence de catégories et vous demande tous les descendants, il vérifie si vous pensez à utiliser un CTE récursif. Les jointures simples ne peuvent parcourir qu'un nombre fixe de niveaux ; la récursivité permet de parcourir une profondeur quelconque.

L'expression révélatrice d'une question est « jusqu'à n'importe quelle profondeur » ou « jusqu'en bas ». C'est le signal qu'il vous faut. Dans cette leçon, vous apprendrez la structure en deux parties que partagent tous les CTE récursifs : l'ancre et le membre récursif.

La structure en deux parties

Un CTE récursif contient toujours le mot-clé WITH RECURSIVE (PostgreSQL, SQLite, MySQL 8+ ; SQL Server omet RECURSIVE) et un corps constitué de deux requêtes reliées par UNION ALL :

  • Membre d'ancrage — les lignes de départ, exécuté une seule fois.
  • Membre récursif — fait référence au nom du CTE lui-même et s'exécute de manière répétée.

Mémorisez cette structure ; les intervieweurs aiment vous demander de l'écrire de zéro.

WITH RECURSIVE cte AS (
    -- anchor member
    SELECT ...
    UNION ALL
    -- recursive member
    SELECT ... FROM cte JOIN ...
)
SELECT * FROM cte;

Rôle de l'ancre

Le membre d'ancrage est une requête ordinaire qui ne fait pas référence au CTE. Il produit les lignes initiales, c'est-à-dire le point de départ du niveau zéro. Pour un organigramme, il s'agit généralement du CEO (la ligne dont le responsable est NULL) ; pour une suite de nombres, il s'agit du premier nombre.

L'ancre s'exécute exactement une fois. Sa sortie devient le premier lot de lignes transmis à l'étape récursive.

-- Anchor: the top of the hierarchy
SELECT id, name, manager_id, 1 AS depth
FROM employees
WHERE manager_id IS NULL

Rôle du membre récursif

Le membre récursif fait référence au CTE par son nom. À chaque itération, il relie les lignes produites par l'itération précédente à la table de base pour trouver le niveau suivant.

Il ne voit pas l'ensemble du CTE construit jusque-là — uniquement les lignes ajoutées à l'étape immédiatement précédente. C'est le modèle mental essentiel que les intervieweurs cherchent à vérifier.

-- Recursive: children of the rows found so far
SELECT e.id, e.name, e.manager_id, c.depth + 1
FROM employees e
JOIN cte c ON e.manager_id = c.id

Assembler les deux parties

Combinez le membre d'ancrage et le membre récursif avec UNION ALL : le moteur effectue alors les itérations automatiquement. Chaque passage ajoute le niveau suivant jusqu'à ce que le membre récursif renvoie zéro ligne, ce qui met fin à la récursion.

Voici un parcours complet et exécutable d'un organigramme, qui suit également la depth.

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

Fonctionnement de l'arrêt

La récursion s'arrête lorsque le membre récursif ne produit aucune nouvelle ligne. Aucun compteur de boucle explicite n'est nécessaire — la jointure finit naturellement par ne plus rien produire lorsque vous atteignez les feuilles de l'arbre.

Dans l'exemple de l'organigramme, lorsque vous atteignez des employés sans subordonné direct, la jointure de l'itération suivante ne trouve aucun enfant, renvoie un résultat vide et le moteur s'arrête. Comprendre ce comportement auto-terminant est une question de suivi classique.

UNION ALL contre UNION

Les intervieweurs demandent souvent pourquoi nous utilisons UNION ALL et non UNION. Deux raisons :

  • Performances — UNION élimine les doublons à chaque itération, ce qui coûte cher.
  • Exactitude — dans un arbre, les lignes en double ne peuvent généralement pas apparaître ; leur élimination représente donc un travail inutile.

Utilisez UNION uniquement lorsque la structure est un graphe et que vous souhaitez délibérément regrouper les nœuds répétés — mais pour garantir la sécurité contre les cycles, des protections explicites sont préférables (nous y reviendrons).

Suivre la profondeur et le chemin

Deux colonnes supplémentaires rendent les résultats récursifs bien plus utiles et sont fréquemment demandées lors des entretiens :

  • profondeur — commencez à 1 dans l'ancre et ajoutez 1 dans le membre récursif.
  • chemin — accumulez la chaîne d'identifiants ou de noms afin de voir le trajet de la racine au nœud.

Construire le path sous forme de chaîne permet également de détecter les cycles par la suite.

WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 1 AS depth,
           CAST(name AS VARCHAR(1000)) AS path
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, o.depth + 1,
           o.path || ' > ' || e.name
    FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT name, depth, path FROM org;

Les types de colonnes doivent correspondre

Un piège subtil : le membre d'ancrage et le membre récursif doivent renvoyer le même nombre de colonnes avec des types compatibles. Si vous construisez une chaîne path, la valeur initiale de l'ancre doit être convertie avec une largeur suffisante (par exemple VARCHAR(1000)), sinon le moteur risque de la tronquer ou de générer une erreur d'incompatibilité de types lors des itérations suivantes.

C'est exactement le genre de détail qu'un intervieweur glisse dans un exercice pour vérifier que vous avez réellement exécuté un CTE récursif plutôt que de vous être contenté d'en lire la description.

Exemple de nomenclature

La même structure permet de résoudre un problème de nomenclature : pour une pièce donnée, lister chaque sous-pièce à n'importe quelle profondeur. L'ancre sélectionne l'ensemble principal ; le membre récursif parcourt les liens de parent_part vers child_part.

Remarquez que la structure est identique à celle de l'organigramme ; seuls les noms de colonnes changent. Comprendre qu'une même structure convient à de nombreux problèmes est la véritable compétence attendue en entretien.

WITH RECURSIVE bom AS (
    SELECT child_part, parent_part, 1 AS lvl
    FROM parts WHERE parent_part = 'ENGINE'
    UNION ALL
    SELECT p.child_part, p.parent_part, b.lvl + 1
    FROM parts p JOIN bom b ON p.parent_part = b.child_part
)
SELECT child_part, lvl FROM bom;

Remarques sur les dialectes

Voici une fiche mémo inter-dialectes que les intervieweurs apprécient :

  • PostgreSQL, SQLite, MySQL 8+ : WITH RECURSIVE name AS (...).
  • SQL Server : utilisez simplement WITH name AS (...) — le mot-clé RECURSIVE est implicite, et une valeur MAXRECURSION par défaut de 100 est appliquée.
  • Oracle : prend en charge les CTE récursifs ainsi que l'ancienne syntaxe CONNECT BY.

Dire « SQL Server n'utilise pas le mot RECURSIVE » montre une réelle maîtrise des différents environnements.

Vérification rapide

Testez votre compréhension de la structure en deux parties.

Récapitulatif

Vous maîtrisez désormais la structure des CTE récursifs :

  • WITH RECURSIVE + ancre + UNION ALL + membre récursif.
  • L'ancre amorce le niveau zéro et s'exécute une fois.
  • Le membre récursif relie l'itération précédente à la table de base et s'exécute jusqu'à ce qu'il ne renvoie plus aucune ligne.
  • Utilisez UNION ALL, suivez la depth et la path, et veillez à la compatibilité des types de colonnes.

Ensuite : appliquez cette structure pour parcourir un organigramme réel vers le bas et vers le haut.

Questions Fréquemment Posées

La leçon « Membres d’ancrage et membres récursifs » est-elle gratuite ?

Oui — le texte complet de « Membres d’ancrage et membres récursifs » 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 Interview Prep, passe à CoddyKit PRO. Le cours SQL Interview Prep comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Membres d’ancrage et membres récursifs » ?

La structure en deux parties d’un CTE récursif et le fonctionnement de l’arrêt Tu pratiques SQL Interview Prep 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 Interview Prep ?

Aucune expérience préalable n'est requise. SQL Interview Prep 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 « Membres d’ancrage et membres récursifs » ?

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 Interview Prep ?

Oui. Chaque leçon SQL Interview Prep 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. Membres d’ancrage et membres récursifs
  2. Parcourir un organigramme
  3. Générer des séries de nombres et de dates
  4. Éviter la récursivité infinie
← Retour à SQL Interview Prep