CTE ou sous-requête SQL : lisibilité, réutilisation et performance

Une CTE nomme un résultat avec WITH ; une sous-requête intègre une requête dans une autre instruction. Choisissez la forme qui rend les transformations les plus compréhensibles. Une CTE n’est pas automatiquement plus rapide et n’est pas une table permanente.

Ce guide compare une CTE ordinaire, non récursive, à une sous-requête utilisée comme table dérivée dans FROM. Les sous-requêtes scalaires, les sous-requêtes corrélées et les CTE récursives répondent à d’autres besoins et ne sont pas interchangeables dans tous ces exemples.

Situation	Point de départ utile
Un calcul intermédiaire court	Sous-requête
Plusieurs étapes nommées	CTE
Réutiliser un résultat dans plusieurs instructions	Envisager une vue ou une table temporaire
Choisir selon la performance	Mesurer sur la vraie base de données

On cherche les clients ayant au moins 150 de paiements effectués. Les paiements remboursés ne doivent pas contribuer au total.

Essayez dans SQLite

Ouvrez SQL Lab, collez cette préparation et exécutez-la une fois. Lancez ensuite chaque requête séparément. Les noms des tables et des colonnes restent en anglais pour utiliser le même SQL dans toutes les langues. Le laboratoire et les leçons liées sont actuellement en anglais.

CREATE TABLE blog_payments (
  id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT, amount INTEGER
);
INSERT INTO blog_payments VALUES
  (1, 1, 'paid', 120), (2, 1, 'paid', 80), (3, 1, 'refunded', 90),
  (4, 2, 'paid', 60), (5, 3, 'paid', 200);

1. Utiliser une sous-requête comme table dérivée

La requête intérieure filtre les paiements effectués et calcule le total par client. La requête extérieure filtre ces totaux. L’alias totals nomme la table dérivée. Les clients 1 et 3 sont retenus avec 200 chacun ; le client 2 n’a que 60.

SELECT customer_id, paid_total
FROM (
  SELECT customer_id, SUM(amount) AS paid_total
  FROM blog_payments
  WHERE status = 'paid'
  GROUP BY customer_id
) AS totals
WHERE paid_total >= 150
ORDER BY customer_id;

Résultat attendu

customer_id	paid_total
1	200
3	200

2. Nommer le même résultat avec une CTE

Le résultat est identique. WITH place le calcul intermédiaire avant le SELECT principal et le nomme paid_totals. Ce nom existe seulement dans cette instruction. Après un autre SELECT indépendant, paid_totals n’est pas une table stockée que l’on peut interroger.

WITH paid_totals AS (
  SELECT customer_id, SUM(amount) AS paid_total
  FROM blog_payments
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT customer_id, paid_total
FROM paid_totals
WHERE paid_total >= 150
ORDER BY customer_id;

Résultat attendu

customer_id	paid_total
1	200
3	200

3. Enchaîner les transformations sans imbrication profonde

La première CTE calcule les totaux payés. La seconde classe les clients ; la requête principale résume les segments. Filtrage, agrégation par client, classification et rapport final deviennent des étapes distinctes. Le segment high contient deux clients et 400 de revenus ; standard contient un client et 60.

WITH paid_totals AS (
  SELECT customer_id, SUM(amount) AS paid_total
  FROM blog_payments
  WHERE status = 'paid'
  GROUP BY customer_id
), customer_segments AS (
  SELECT customer_id, paid_total,
    CASE WHEN paid_total >= 150 THEN 'high' ELSE 'standard' END AS segment
  FROM paid_totals
)
SELECT segment, COUNT(*) AS customers, SUM(paid_total) AS revenue
FROM customer_segments
GROUP BY segment
ORDER BY segment;

Résultat attendu

segment	customers	revenue
high	2	400
standard	1	60

Exercice et vérification

Remplacez le seuil de 150 par 210 dans les deux premières requêtes. Aucune ne doit retourner de ligne. Incluez ensuite tous les statuts et rétablissez 150 : le client 1 atteint 290 et le client 3 reste à 200. Cela vérifie que le filtre des lignes définit le résultat métier, et non le choix entre CTE et sous-requête.

Pour évaluer la performance, comparez les plans et les durées sur le moteur et les données réels. PostgreSQL peut intégrer certaines CTE dans la requête principale ou les matérialiser selon la requête ; sa documentation détaille les conditions et les options. SQLite possède son propre comportement de planification. Nommer un résultat ne garantit ni une exécution physique unique ni une mise en cache.

N’ajoutez pas systématiquement des options de matérialisation pour optimiser. Une investigation utile note la version, les index, le nombre de lignes, le plan et les durées de plusieurs exécutions. Ce petit exemple enseigne la structure et la justesse ; il ne désigne pas un gagnant en production.

Choisissez une CTE lorsqu’un nom pertinent clarifie l’intention. Choisissez une sous-requête courte lorsqu’elle rassemble une logique liée. Dans les deux cas, nommez les colonnes nécessaires au lieu de SELECT * et vérifiez les nombres de lignes avant et après les jointures pour repérer les duplications accidentelles.

Documentation et suite du parcours

Essayez les leçons sur les CTE et les sous-requêtes dans FROM. Ces exemples fonctionnent dans SQLite et utilisent la syntaxe WITH ordinaire, également disponible dans PostgreSQL et MySQL 8. Aucune option de matérialisation propre au moteur n’est employée.

• Documentation officielle SQLite
• Documentation officielle du moteur