SQL CTE : les expressions de table communes et la clause WITH

Leçon 6.4 · Temps de lecture : ~8 min · Exercices pratiques ↓

Une CTE SQL (expression de table commune) est un résultat temporaire nommé que vous définissez avec la clause WITH et que vous utilisez ensuite dans la requête principale comme une table. Dans cette leçon, vous apprendrez la syntaxe des CTE, la comparaison entre CTE et sous-requête, comment enchaîner plusieurs CTE et comment construire une analyse en plusieurs étapes qui reste facile à lire.

Dans les leçons précédentes, vous avez écrit des sous-requêtes dans FROM et WHERE. Elles fonctionnent, mais quand la logique se complique, les sous-requêtes imbriquées deviennent difficiles à lire : il faut partir de la requête la plus interne et remonter vers l'extérieur. Une CTE permet de nommer chaque étape et d'écrire les étapes de haut en bas, dans l'ordre où vous les pensez.

Qu'est-ce qu'une CTE en SQL ?

Une expression de table commune est un résultat de requête nommé, défini au début d'une instruction. Elle n'existe que pendant l'exécution de cette instruction : rien n'est stocké dans la base de données, et les autres requêtes ne la voient pas.

Vous pouvez voir une CTE comme une sous-requête nommée. Ses principaux avantages sont :

• Lisibilité : chaque étape a un nom parlant.
• Réutilisation : la requête principale peut référencer la même CTE plusieurs fois.
• Modularité : une requête complexe se découpe en petites étapes logiques.
• Débogage : chaque CTE peut être exécutée et vérifiée séparément.

Syntaxe de base

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

• WITH introduit la CTE.
• cte_name est le nom que la requête principale utilisera.
• La requête entre parenthèses définit les lignes de la CTE.
• La requête principale qui suit travaille avec cte_name comme avec une table ordinaire.

Votre première CTE

Trouvons les clients qui ont dépensé plus de 100 $ au total :

WITH customer_spending AS (
    SELECT customer_id,
           SUM(amount) AS total_spent,
           COUNT(*) AS payment_count,
           AVG(amount) AS avg_payment
    FROM payment
    GROUP BY customer_id
)
SELECT customer_id, total_spent, payment_count, avg_payment
FROM customer_spending
WHERE total_spent > 100
ORDER BY total_spent DESC;

Résultat : les clients ayant plus de 100 $ de paiements, avec leur nombre de paiements et leur paiement moyen, les plus gros dépensiers en premier.

La CTE calcule les dépenses par client, et la requête principale se contente de filtrer et de trier. Le nom customer_spending indique au lecteur ce que sont les données intermédiaires avant même qu'il lise comment elles sont calculées.

CTE ou sous-requête

Voici la même requête écrite avec une sous-requête dans FROM :

SELECT customer_id, total_spent, payment_count, avg_payment
FROM (
    SELECT customer_id,
           SUM(amount) AS total_spent,
           COUNT(*) AS payment_count,
           AVG(amount) AS avg_payment
    FROM payment
    GROUP BY customer_id
) AS spending_data
WHERE total_spent > 100
ORDER BY total_spent DESC;

Les deux requêtes renvoient les mêmes lignes. La différence tient à la façon dont elles se lisent :

Pour une seule étape courte, une sous-requête convient très bien. Quand la logique a deux étapes ou plus, ou qu'un même résultat est nécessaire deux fois, une CTE est généralement plus claire.

Plusieurs CTE dans une requête

Vous pouvez définir plusieurs CTE séparées par des virgules. Chacune peut utiliser les CTE définies avant elle :

WITH customer_spending AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM payment
    GROUP BY customer_id
),
high_spenders AS (
    SELECT customer_id, total_spent
    FROM customer_spending
    WHERE total_spent > 150
)
SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       c.email,
       hs.total_spent
FROM high_spenders AS hs
JOIN customer AS c ON c.customer_id = hs.customer_id
ORDER BY hs.total_spent DESC;

Résultat : les noms et e-mails des clients ayant dépensé plus de 150 $, les plus gros dépensiers en premier.

La requête se lit comme un petit plan :

1. customer_spending totalise les paiements de chaque client.
2. high_spenders ne garde que les clients au-dessus de 150 $.
3. La requête principale ajoute les noms et e-mails depuis customer.

Notez que WITH ne s'écrit qu'une fois, avant la première CTE.

Référencer une CTE plusieurs fois

Une CTE peut apparaître plusieurs fois dans la requête principale, par exemple pour joindre un résultat à lui-même. Comparons le chiffre d'affaires de chaque mois avec celui du mois précédent :

WITH monthly_sales AS (
    SELECT CAST(DATE_FORMAT(payment_date, '%Y-%m-01') AS DATE) AS month_start,
           SUM(amount) AS monthly_total
    FROM payment
    GROUP BY month_start
)
SELECT cur.month_start,
       cur.monthly_total AS current_sales,
       prev.monthly_total AS previous_sales,
       ROUND((cur.monthly_total - prev.monthly_total) / prev.monthly_total * 100, 2) AS percent_change
FROM monthly_sales AS cur
LEFT JOIN monthly_sales AS prev
       ON prev.month_start = cur.month_start - INTERVAL 1 MONTH
ORDER BY cur.month_start;

Résultat : le chiffre d'affaires de chaque mois à côté de celui du mois précédent et la variation en pourcentage. Quand le mois précédent n'a aucun paiement (mai 2005, le premier mois, et février 2006, après une interruption des données), les colonnes de comparaison valent NULL.

monthly_sales est défini une fois et utilisé deux fois, comme cur et prev. Avec des sous-requêtes, il faudrait écrire deux fois la même agrégation et garder les deux copies synchronisées.

Exemple pratique : la dernière location de chaque client

Une tâche courante consiste à trouver l'événement le plus récent de chaque client. Avec une CTE, elle se découpe en deux étapes claires : trouver la dernière date, puis ajouter les détails.

WITH last_rental AS (
    SELECT customer_id, MAX(rental_date) AS last_rental_date
    FROM rental
    GROUP BY customer_id
)
SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       lr.last_rental_date
FROM customer AS c
LEFT JOIN last_rental AS lr ON lr.customer_id = c.customer_id
ORDER BY lr.last_rental_date
LIMIT 20;

Résultat : les 20 clients qui n'ont rien loué depuis le plus longtemps, avec la date de leur dernière location.

Dans le module 7, vous verrez comment les fonctions de fenêtrage résolvent les tâches de type « dernière ligne par groupe » quand vous avez besoin de toute la ligne, et pas seulement de la date.

Exemple pratique : l'analyse de cohortes

Les CTE sont particulièrement utiles pour les analyses en plusieurs étapes. Dans une analyse de cohortes, les clients sont regroupés par mois de leur première location, puis leur activité est suivie mois par mois :

WITH customer_cohort AS (
    SELECT customer_id,
           CAST(DATE_FORMAT(MIN(rental_date), '%Y-%m-01') AS DATE) AS cohort_month
    FROM rental
    GROUP BY customer_id
),
monthly_activity AS (
    SELECT cc.cohort_month,
           CAST(DATE_FORMAT(r.rental_date, '%Y-%m-01') AS DATE) AS rental_month,
           r.customer_id
    FROM customer_cohort AS cc
    JOIN rental AS r ON r.customer_id = cc.customer_id
)
SELECT cohort_month,
       rental_month,
       COUNT(DISTINCT customer_id) AS active_customers,
       COUNT(*) AS rentals
FROM monthly_activity
GROUP BY cohort_month, rental_month
ORDER BY cohort_month, rental_month;

Résultat : pour chaque cohorte (mois de la première location), combien de ses clients ont loué des films chaque mois suivant, et combien de locations ils ont effectuées.

1. customer_cohort affecte chaque client au mois de sa première location.
2. monthly_activity rattache la cohorte à chaque location.
3. La requête principale compte les clients actifs et les locations par cohorte et par mois.

CTE et performances

Une CTE est avant tout une façon d'organiser une requête. Dans la plupart des bases de données modernes, une CTE simple a les mêmes performances que la sous-requête équivalente, car l'optimiseur l'intègre dans la requête principale.

Il existe des différences entre les bases de données :

• PostgreSQL avant la version 12 matérialisait toujours une CTE (il la calculait une fois dans un résultat temporaire). Depuis la version 12, une CTE référencée une seule fois est intégrée ; AS MATERIALIZED et AS NOT MATERIALIZED permettent de choisir.
• MySQL (8.0+) et MariaDB (10.2+) peuvent soit fusionner une CTE dans la requête principale, soit la matérialiser. Une CTE référencée plusieurs fois est généralement calculée une seule fois.

Si une requête avec des CTE est lente, vérifiez son plan d'exécution avec EXPLAIN, comme pour toute autre requête.

Points clés :

• Une CTE est un résultat temporaire nommé défini avec WITH et disponible uniquement dans une instruction.
• Les CTE permettent d'écrire une logique en plusieurs étapes de haut en bas, avec un nom parlant pour chaque étape.
• Plusieurs CTE sont séparées par des virgules, et chacune peut utiliser les CTE définies avant elle.
• Une CTE peut être référencée plusieurs fois dans la requête principale, sans répéter la même sous-requête.
• Pour une étape courte, une sous-requête suffit ; pour une logique en plusieurs étapes, une CTE est généralement plus lisible.
• Les performances sont généralement les mêmes qu'avec des sous-requêtes, mais les règles de matérialisation diffèrent selon les bases.

Questions fréquentes

Quelle est la différence entre une CTE et une sous-requête ?

Les deux définissent un résultat intermédiaire au sein d'une requête. Une CTE s'écrit avant la requête principale avec un nom, peut être référencée plusieurs fois et se lit de haut en bas. Une sous-requête s'écrit directement là où elle est utilisée et doit être répétée à chaque usage.

Une CTE est-elle plus rapide qu'une sous-requête ?

En général, ni l'une ni l'autre n'est plus rapide : la plupart des optimiseurs traitent de la même façon une CTE simple et la sous-requête équivalente. La différence apparaît quand une base matérialise une CTE, ce qui peut aider quand le résultat est réutilisé et nuire quand la requête principale le filtre fortement. Vérifiez le plan avec EXPLAIN.

Une CTE est-elle la même chose qu'une table temporaire ?

Non. Une CTE n'existe que pendant une instruction et n'est pas stockée. Une table temporaire est créée explicitement, dure jusqu'à la fin de la session, peut être utilisée par de nombreuses requêtes et peut être indexée.

Quelles bases de données prennent en charge la clause WITH ?

PostgreSQL, SQL Server, Oracle, SQLite (3.8.3+), MySQL (8.0+) et MariaDB (10.2+) prennent tous en charge les CTE.