CTEs (Common Table Expressions)

SQL - Cet article fait partie d'une série.

Partie 2: Cet article

Partie 3: Fonctions temporaires & macros

Partie 3: Modification

Partie 4: Agrégations simples & avancées

Partie 4: Suppression

Partie 5: Création et structures

Partie 6: Pivot et Unpivot

Partie 7: Manipulations & Expressions utiles

Partie 8: Dates & Calendriers

Partie 9: Préparation aux entretiens

Partie 12: Classement

Partie 13: Window functions

Partie 15: Ordre d’écriture & d’exécution

🧠 C’est quoi une CTE ?
#

Une CTE est comme une vue temporaire et nommée dans une requête. Elle permet de :

Casser une requête en plusieurs morceaux lisibles
Réutiliser des sous-ensembles intermédiaires
Éviter les sous-requêtes imbriquées illisibles

WITH alias AS (
  SELECT ...
)
SELECT * FROM alias;

Ou en chaîner plusieurs :

WITH
  step1 AS (SELECT ...),
  step2 AS (SELECT * FROM step1 WHERE ...)
SELECT * FROM step2;

Quelques exemples
#

✅ CTE pour simplifier une agrégation
#

WITH commandes_par_client AS (
  SELECT client_id, COUNT(*) AS nb_commandes
  FROM commandes
  GROUP BY client_id
)
SELECT c.nom, cpc.nb_commandes
FROM clients c
LEFT JOIN commandes_par_client cpc ON c.client_id = cpc.client_id;

👉 On sépare l’agrégation de la jointure = plus lisible, modulaire.

✅ Plusieurs CTEs
#

WITH
  commandes_par_client AS (
    SELECT client_id, COUNT(*) AS nb_commandes
    FROM commandes
    GROUP BY client_id
  ),
  clients_avec_commandes AS (
    SELECT * FROM clients WHERE client_id IN (SELECT client_id FROM commandes)
  )
SELECT cac.nom, cpc.nb_commandes
FROM clients_avec_commandes cac
JOIN commandes_par_client cpc ON cac.client_id = cpc.client_id;

✅ Remplacer une sous-requête
#

-- Moins lisible
SELECT nom
FROM clients
WHERE client_id IN (
  SELECT client_id FROM commandes WHERE produit = 'Chaise'
);

-- Mieux avec une CTE
WITH acheteurs_chaise AS (
  SELECT client_id FROM commandes WHERE produit = 'Chaise'
)
SELECT c.nom
FROM clients c
JOIN acheteurs_chaise ac ON c.client_id = ac.client_id;

🔄 CTEs récursives : cas typique = hiérarchies, arbres
#

💡 On utilise le mot-clé spécial RECURSIVE dans le WITH

✅ Trouver tous les subordonnés d’un manager
#

WITH RECURSIVE hierarchie AS (
  -- Base : les subordonnés directs d'Alice
  SELECT emp_id, nom, manager_id
  FROM employes
  WHERE manager_id = 1

  UNION ALL

  -- Récursivité : les subordonnés de ceux déjà trouvés
  SELECT e.emp_id, e.nom, e.manager_id
  FROM employes e
  JOIN hierarchie h ON e.manager_id = h.emp_id
)
SELECT * FROM hierarchie;

👀 → Retourne tous les descendants directs et indirects d’Alice (emp_id = 1)

⚠️ Pièges fréquents
#

Erreur	Cause	Solution
Pas de récursivité	oublié de `RECURSIVE`	Ajouter `WITH RECURSIVE ...`
Récursion infinie	pas de condition d’arrêt	Assure-toi que les relations sont finies
Mauvais nommage	même nom de colonne en base et CTE	Renomme les colonnes au besoin

🏋️‍♂️ Exercices pratiques
#

Noms des clients qui ont commandé plus d’un produit
Astuce : CTE pour compter, puis filtre
Liste des clients sans aucune commande
Astuce : LEFT JOIN + CTE d’agrégation
Liste hiérarchique des subordonnés de Bob (emp_id = 2)
Pour chaque produit, nombre de clients différents l’ayant commandé

import duckdb

# Liste des clients qui ont commandé >1 produit
duckdb.sql("""
WITH nb_cmd AS (
  SELECT client_id, COUNT(*) AS n
  FROM commandes
  GROUP BY client_id
)
SELECT c.nom, n
FROM clients c
LEFT JOIN nb_cmd ON c.client_id = nb_cmd.client_id
WHERE n > 1;
""").df()