Sous-requêtes et CTE

Prérequis : cours précédent (jointures).

Pourquoi

Une requête de 80 lignes imbriquées est illisible et intestable. Les CTE (Common Table Expressions, WITH) découpent en étapes nommées : chaque étape se teste seule, l'ensemble se lit comme une recette.

Concepts

• Sous-requête : requête dans une requête (dans SELECT, FROM, WHERE). Puissante, vite illisible au-delà d'un niveau.
• CTE (WITH …) : nommer chaque étape (actifs AS (…), ca AS (…)), puis SELECT final limpide. En Postgres, matérialisée ou non selon l'optimiseur — vérifier avec EXPLAIN.
• CTE récursive : WITH RECURSIVE pour les hiérarchies (organigramme, catégories, threads) : cas de base + UNION ALL + condition d'arrêt. Sans garde-fou, récursion infinie → LIMIT de sécurité en dev.

Exemple : lisible en 3 étapes

WITH actifs AS (
  SELECT id, name FROM customers WHERE active
),
ca AS (
  SELECT customer_id, SUM(total) AS total
  FROM orders WHERE created_at >= date_trunc('quarter', now())
  GROUP BY customer_id
)
SELECT a.name, COALESCE(ca.total, 0) AS ca
FROM actifs a LEFT JOIN ca ON ca.customer_id = a.id
ORDER BY ca.total DESC NULLS LAST;

Cas réel

Rapport trimestriel en sous-requêtes gigognes : personne n'osait le toucher, un bug y a vécu 8 mois. Réécrit en 4 CTE nommées : le bug (filtre sur la mauvaise année) visible en 5 minutes.

Pièges

• CTE utilisée une fois pour 3 lignes : overkill, une sous-requête suffit (YAGNI aussi en SQL).
• Récursive sans condition d'arrêt testée : boucle infinie en prod. Toujours un garde (depth < 100).
• COALESCE oublié sur les LEFT : NULL qui se propagent dans les totaux et les exports.

À retenir

CTE = étapes nommées testables ; récursive = hiérarchies + garde ; COALESCE avec les LEFT.