SQL WITH : les CTE (Common Table Expressions)

Quand une requête devient longue, avec des sous-requêtes imbriquées les unes dans les autres, elle devient vite illisible. Une CTE (expression de table commune), introduite par WITH, permet de découper le problème en étapes nommées, que l'on utilise ensuite comme des tables.

Syntaxe

WITH etape AS (
  SELECT …
)
SELECT …
FROM etape;

Comment ça marche

Une CTE se déclare au début de la requête : WITH nom AS (requête). Dans la requête principale, nom s'utilise exactement comme une table. Elle n'existe que le temps de la requête : rien n'est enregistré dans la base.

Plusieurs étapes

On peut enchaîner plusieurs CTE, séparées par des virgules, et chacune peut utiliser les précédentes : WITH a AS (…), b AS (SELECT … FROM a) SELECT … FROM b. La requête se lit alors de haut en bas, comme une recette.

Les CTE récursives

WITH RECURSIVE permet à une CTE de faire référence à elle-même. C'est l'outil idéal pour parcourir une hiérarchie (un organigramme) ou générer une suite de valeurs.

Schéma

Schéma simplifié pour illustrer le principe. Les exemples ci-dessous utilisent la vraie base d'exercices de SQL Quest (une boutique en ligne fictive : voir le schéma complet).

Exemples avec leurs résultats

WITH paniers AS (
  SELECT commande_id, SUM(quantite * prix_unitaire) AS montant
  FROM lignes_commande
  GROUP BY commande_id
)
SELECT COUNT(*) AS commandes, ROUND(AVG(montant), 2) AS panier_moyen, ROUND(MAX(montant), 2) AS plus_gros
FROM paniers;

WITH depenses AS (
  SELECT o.client_id, SUM(l.quantite * l.prix_unitaire) AS total
  FROM commandes o JOIN lignes_commande l ON l.commande_id = o.id
  GROUP BY o.client_id
),
moyenne AS (
  SELECT AVG(total) AS m FROM depenses
)
SELECT d.client_id, ROUND(d.total, 2) AS total
FROM depenses d, moyenne
WHERE d.total > moyenne.m
ORDER BY d.total DESC
LIMIT 6;

WITH RECURSIVE organigramme AS (
  SELECT id, prenom, nom, poste, 0 AS niveau
  FROM employes WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.prenom, e.nom, e.poste, o.niveau + 1
  FROM employes e JOIN organigramme o ON e.manager_id = o.id
)
SELECT niveau, prenom, nom, poste
FROM organigramme
ORDER BY niveau, nom
LIMIT 8;

Les erreurs fréquentes

WITH t AS (SELECT * FROM produits); SELECT * FROM t;

WITH t AS (SELECT * FROM produits) SELECT * FROM t;

WITH a AS (SELECT 1 AS x) WITH b AS (SELECT x FROM a) SELECT * FROM b;

WITH a AS (SELECT 1 AS x), b AS (SELECT x FROM a) SELECT * FROM b;

WITH t AS (SELECT * FROM produits) SELECT 1; SELECT * FROM t;

WITH t AS (SELECT * FROM produits) SELECT COUNT(*) FROM t;

Exercices

Essayez d'écrire la requête vous-même avant d'ouvrir la solution.

WITH ca AS (SELECT p.categorie, SUM(l.quantite * l.prix_unitaire) AS total FROM lignes_commande l JOIN produits p ON p.id = l.produit_id GROUP BY p.categorie) SELECT categorie, ROUND(total, 2) AS total FROM ca WHERE total > 30000;

WITH t AS (SELECT client_id, COUNT(*) AS nb FROM commandes GROUP BY client_id) SELECT ROUND(AVG(nb), 2) AS moyenne FROM t;

Questions fréquentes