CTE : requêtes en plusieurs étapes avec WITH

Les vraies requêtes ont tendance à croître : trois JOIN, un GROUP BY, une sous-requête, une autre sous-requête… et elles deviennent illisibles. CTE ("Tableau commun Expressions", la clause WITH) permettent de donner un nom à un étape intermédiaire et utilisez-la comme s’il s’agissait d’une table dans la requête principale.

Syntaxe de base

WITH nome_cte AS (
  SELECT …    -- query interna
)
SELECT …      -- query principale, usa nome_cte come fosse una tabella
FROM   nome_cte;

Un CTE n'est valable que pour la requête qui le suit : il ne survit pas l'exécution.

Exemple

Retrouvez les 3 clients qui ont le plus dépensé (somme sur commandes livrées) :

WITH total_spent AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM   orders      o
  JOIN   order_items oi ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY o.customer_id
)
SELECT c.email,
       s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
ORDER BY s.total DESC
LIMIT  3;

Lire en anglais : _ "calculez d'abord les dépenses par client ; puis rejoignez-nous avec clients et prenez les 3 premiers"_. Sans CTE, vous auriez besoin d'un inline sous-requête dans le FROM (légal, mais plus bruyant).

Plusieurs CTE en cascade

Vous pouvez définir plusieurs CTE séparés par des virgules, et chacun peut faire référence aux précédents :

WITH total_spent AS (
  SELECT customer_id, SUM(quantity * unit_price) AS total
  FROM   order_items oi
  JOIN   orders      o ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY customer_id
),
average AS (
  SELECT AVG(total) AS total_average
  FROM   total_spent
)
SELECT c.email, s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
CROSS JOIN average
WHERE  s.total > average.total_average
ORDER BY s.total DESC;

CROSS JOIN average car average a toujours une seule ligne : le Le produit cartésien ajoute simplement total_average à chaque ligne du résultat.

CTE vs sous-requête : quand utiliser laquelle

Les CTE brillent lorsque :

• la même sous-requête est utilisée deux fois ou plus dans la requête principale ;
• vous souhaitez rendre la structure explicite dans les étapes nommées (auto-documentée) ;
• ils sont le prérequis aux CTE récursifs (WITH RECURSIVE, la prochaine leçon de ce module).

Une sous-requête en ligne est plus concise pour une étape utilisée une seule fois.

Essayez-le

Exercice de révision