Sous-requêtes et CTE : décomposer un problème en SQL

Certaines questions sont trop complexes pour tenir en une seule requête plate : « quels produits coûtent plus cher que la moyenne ? » suppose de calculer d abord la moyenne, puis de comparer. « Quels clients n ont jamais commandé d écran ? » suppose de vérifier une absence. SQL répond à cela en imbriquant une requête dans une autre : c est la sous-requête. Et quand l imbrication devient illisible, la clause WITH (CTE) nomme les étapes intermédiaires.

Ce tutoriel vous apprend à décomposer un problème en étapes claires. Vous découvrirez les sous-requêtes dans WHERE, dans FROM et dans SELECT, l opérateur EXISTS, les CTE et même les CTE récursives. Toujours sur la boutique TechStock.

🎯 Ce que vous allez apprendre

• Écrire une sous-requête scalaire pour comparer à une valeur calculée.
• Utiliser IN et EXISTS pour tester l appartenance et l existence.
• Comprendre la différence entre sous-requête simple et corrélée.
• Placer une sous-requête dans FROM comme table dérivée.
• Rendre une requête complexe lisible avec une CTE (clause WITH).
• Parcourir une hiérarchie avec une CTE récursive.

🛠️ Ce que vous allez construire

Vous allez répondre à des questions d analyse fine : les produits au-dessus du prix moyen, les clients ayant acheté un écran, le classement des clients par dépense via une étape intermédiaire nommée, et l organigramme complet de l équipe avec niveaux hiérarchiques. Ces requêtes préparent des rapports impossibles à obtenir d un seul niveau.

Prérequis

• Maîtriser les jointures (les jointures) et l agrégation (GROUP BY et HAVING).
• La base TechStock complète, avec la table employes.
• ⏱️ Temps estimé : environ 55 minutes.

Étape 1 — La sous-requête scalaire

Une sous-requête scalaire renvoie une seule valeur, utilisable partout où une valeur est attendue. Le cas d école : trouver les produits plus chers que la moyenne. La moyenne se calcule par une requête, qu on imbrique dans le WHERE de la requête principale, entre parenthèses.

SELECT nom, prix
FROM produits
WHERE prix > (SELECT AVG(prix) FROM produits)
ORDER BY prix DESC;

Le moteur évalue d abord la sous-requête, qui renvoie le prix moyen, puis compare chaque produit à cette valeur. On obtient les produits au-dessus de la moyenne, sans avoir à connaître ce chiffre à l avance. Une sous-requête scalaire peut aussi figurer dans le SELECT, par exemple pour afficher l écart de chaque produit à la moyenne.

✅ Point d étape — Le résultat ne contient que les produits dont le prix dépasse la moyenne du catalogue. Calculez la moyenne séparément pour vérifier la cohérence.

Étape 2 — Tester l appartenance avec IN

Quand la sous-requête renvoie une liste de valeurs et non une seule, on la combine avec IN. Pour lister les clients ayant commandé au moins un produit de la catégorie Écrans, on récupère d abord les identifiants de commandes concernées, puis on remonte aux clients.

SELECT DISTINCT c.nom
FROM clients AS c
JOIN commandes AS cmd ON cmd.client_id = c.id
WHERE cmd.id IN (
  SELECT lc.commande_id
  FROM lignes_commande AS lc
  JOIN produits AS p ON p.id = lc.produit_id
  JOIN categories AS cat ON cat.id = p.categorie_id
  WHERE cat.nom = 'Écrans'
);

La sous-requête produit l ensemble des commandes contenant un écran ; la requête principale ne garde que les clients liés à ces commandes. Le DISTINCT évite de répéter un client ayant passé plusieurs commandes éligibles. Beaucoup de ces requêtes peuvent aussi s écrire avec une jointure ; les deux approches sont valides, et le moteur les optimise souvent de manière comparable.

✅ Point d étape — Vous devez retrouver le client ayant commandé l écran 24 pouces. Vérifiez en consultant directement les lignes de commande concernées.

Étape 3 — EXISTS et NOT EXISTS

L opérateur EXISTS teste si une sous-requête renvoie au moins une ligne, sans s intéresser à leur contenu. Il est particulièrement adapté pour exprimer une présence ou une absence. Pour lister les clients n ayant jamais rien commandé, NOT EXISTS exprime l absence de façon directe et performante.

SELECT c.nom
FROM clients AS c
WHERE NOT EXISTS (
  SELECT 1 FROM commandes AS cmd
  WHERE cmd.client_id = c.id
);

Pour chaque client, le moteur vérifie s il existe au moins une commande à son nom ; NOT EXISTS ne garde que ceux pour qui ce n est pas le cas. Le SELECT 1 est une convention : peu importe ce qu on sélectionne, seule compte l existence d une ligne. Cette sous-requête fait référence à la requête externe (via c.id) : on parle de sous-requête corrélée.

✅ Point d étape — Résultat attendu : Moussa Diarra, le seul client sans commande. C est le même résultat que le LEFT JOIN du tutoriel précédent, exprimé autrement.

Étape 4 — Sous-requête corrélée

Une sous-requête corrélée dépend de la ligne courante de la requête externe : elle est réévaluée pour chaque ligne. C est puissant mais potentiellement coûteux. Affichons chaque produit avec le nombre de fois où il a été commandé, calculé par une sous-requête corrélée dans le SELECT.

SELECT p.nom,
       (SELECT COUNT(*)
        FROM lignes_commande AS lc
        WHERE lc.produit_id = p.id) AS fois_commande
FROM produits AS p
ORDER BY fois_commande DESC;

Pour chaque produit de la requête externe, la sous-requête compte ses lignes de commande. La corrélation se voit dans la condition lc.produit_id = p.id, qui référence le produit courant. Sur de grandes tables, ce type de requête peut être lent ; une jointure agrégée ou une CTE est souvent préférable, comme nous le verrons.

✅ Point d étape — Les produits jamais commandés affichent zéro, et non NULL, car COUNT renvoie toujours un nombre. C est une différence subtile mais utile avec une jointure externe.

Étape 5 — Sous-requête dans FROM

Une sous-requête peut aussi remplacer une table dans la clause FROM : on parle de table dérivée. C est utile pour agréger d abord, puis agréger de nouveau. Calculons le panier moyen, c est-à-dire la moyenne des totaux de commande. Il faut d abord le total par commande, puis la moyenne de ces totaux.

SELECT AVG(total_commande) AS panier_moyen
FROM (
  SELECT cmd.id,
         SUM(lc.quantite * lc.prix_unitaire) AS total_commande
  FROM commandes AS cmd
  JOIN lignes_commande AS lc ON lc.commande_id = cmd.id
  GROUP BY cmd.id
) AS totaux;

La sous-requête interne calcule le total de chaque commande ; la requête externe en fait la moyenne. Une table dérivée doit obligatoirement porter un alias, ici totaux, sinon le moteur la rejette. Ce motif agrégat-sur-agrégat est très fréquent en analyse.

✅ Point d étape — Une seule valeur en sortie, le panier moyen. Comparez-la à la somme totale divisée par le nombre de commandes pour confirmer.

Étape 6 — Les CTE avec WITH

Quand les sous-requêtes s empilent, la lecture devient pénible. La clause WITH définit une expression de table commune (CTE) : une requête nommée, placée en tête, que l on réutilise ensuite comme une table. Le même calcul de panier moyen gagne en clarté :

WITH totaux AS (
  SELECT cmd.id,
         SUM(lc.quantite * lc.prix_unitaire) AS total_commande
  FROM commandes AS cmd
  JOIN lignes_commande AS lc ON lc.commande_id = cmd.id
  GROUP BY cmd.id
)
SELECT AVG(total_commande) AS panier_moyen,
       MAX(total_commande) AS plus_grosse_commande
FROM totaux;

La CTE totaux est définie une fois, puis interrogée comme une table ordinaire. Le résultat est identique à la table dérivée, mais la requête se lit de haut en bas, étape par étape. On peut enchaîner plusieurs CTE séparées par des virgules, chacune s appuyant sur la précédente : c est l outil maître pour construire une analyse complexe sans se perdre.

✅ Point d étape — Vous obtenez le panier moyen et la plus grosse commande. La lisibilité gagnée par rapport à l imbrication doit vous sauter aux yeux.

Étape 7 — La CTE récursive

Une CTE récursive se réfère à elle-même pour parcourir une structure arborescente : organigramme, catégories imbriquées, nomenclature. Elle se compose d un cas de base et d un cas récursif, reliés par UNION ALL. Affichons l organigramme de l équipe avec le niveau hiérarchique de chacun.

WITH RECURSIVE hierarchie AS (
  SELECT id, nom, manager_id, 1 AS niveau
  FROM employes
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.nom, e.manager_id, h.niveau + 1
  FROM employes AS e
  JOIN hierarchie AS h ON e.manager_id = h.id
)
SELECT niveau, nom
FROM hierarchie
ORDER BY niveau, nom;

Le cas de base sélectionne la directrice, sans supérieur, au niveau un. Le cas récursif joint chaque employé aux personnes déjà trouvées dont il dépend, en incrémentant le niveau. Le moteur répète l opération jusqu à épuisement. On obtient toute la hiérarchie à plat, avec la profondeur de chacun. La récursivité est la seule façon élégante de traiter une profondeur inconnue en SQL.

✅ Point d étape — La directrice apparaît au niveau 1, le responsable au niveau 2, les vendeurs au niveau 3. Cet ordre confirme que la récursion descend correctement l arbre.

🐞 Pièges fréquents

🌍 Réalités du terrain

Les sous-requêtes et les CTE rendent le code lisible, mais toutes ne se valent pas en performance. Une sous-requête corrélée réévaluée des milliers de fois peut mettre un serveur modeste à genoux ; sur de gros volumes, une jointure agrégée équivalente est souvent bien plus rapide. La bonne démarche est d écrire d abord la requête la plus claire, puis de vérifier son coût avec le plan d exécution avant de l optimiser si nécessaire. Les CTE, elles, sont un excellent outil de structuration : décomposer un rapport en étapes nommées facilite la maintenance, surtout dans une équipe où plusieurs personnes relisent le code. Gardez en tête que sur certains moteurs anciens, une CTE peut empêcher certaines optimisations ; le plan d exécution reste le juge de paix.

✅ Récapitulatif

Vous savez maintenant décomposer un problème : sous-requête scalaire pour comparer à une valeur calculée, IN et EXISTS pour l appartenance et l existence, sous-requête corrélée pour un calcul par ligne, table dérivée dans FROM, CTE pour la lisibilité, et CTE récursive pour les hiérarchies. Ces outils transforment des questions complexes en requêtes maîtrisées. L étape suivante quitte l interrogation pour la conception : bien modéliser la base qui porte toutes ces données.

🧾 Aide-mémoire

💪 À vous de jouer

1. Affichez les produits dont le prix dépasse le prix moyen de leur propre catégorie.
2. Listez les clients ayant dépensé plus que le client moyen, via une CTE.
3. Affichez chaque employé avec le nombre de personnes sous sa responsabilité directe.

-- 1 (sous-requête corrélée par catégorie)
SELECT nom, prix FROM produits AS p
WHERE prix > (SELECT AVG(prix) FROM produits AS x
               WHERE x.categorie_id = p.categorie_id);

-- 2 (CTE)
WITH depense AS (
  SELECT c.id, c.nom, SUM(lc.quantite*lc.prix_unitaire) AS total
  FROM clients AS c
  JOIN commandes AS cmd ON cmd.client_id=c.id
  JOIN lignes_commande AS lc ON lc.commande_id=cmd.id
  GROUP BY c.id, c.nom)
SELECT nom, total FROM depense
WHERE total > (SELECT AVG(total) FROM depense);

-- 3
SELECT m.nom, COUNT(e.id) AS nb_subordonnes
FROM employes AS m
LEFT JOIN employes AS e ON e.manager_id = m.id
GROUP BY m.nom;

Tutoriels associés

• Agréger et résumer avec GROUP BY et HAVING
• Modéliser une base avec les clés étrangères

Pour aller plus loin

• 🔝 Retour au guide : Apprendre le SQL
• Sous-requêtes PostgreSQL : postgresql.org
• Requêtes WITH (CTE) : documentation des CTE

FAQ

Sous-requête ou jointure ? Beaucoup de problèmes acceptent les deux. La jointure est souvent plus rapide pour combiner des données ; la sous-requête ou EXISTS exprime plus naturellement une condition d existence. Le plan d exécution tranche en cas de doute.

IN ou EXISTS ? EXISTS gère mieux les valeurs NULL et s arrête dès la première correspondance. IN est très lisible sur une petite liste. Pour tester une absence, NOT EXISTS est généralement plus sûr que NOT IN.

Une CTE est-elle stockée ? Non, c est une définition temporaire valable le temps de la requête. Pour réutiliser durablement une requête, on crée plutôt une vue.

Quand utiliser la récursivité ? Pour parcourir une structure de profondeur inconnue : hiérarchie d employés, arborescence de catégories, graphe de dépendances. Pour une profondeur fixe, de simples jointures suffisent.

Mots-clés : sous-requête SQL, CTE, clause WITH, EXISTS, requête récursive, table dérivée.