CTE en SQL, qu’est-ce qu’une expression de table commune?

Une expression de table commune ou CTE en SQL (Common Table Expression), est un bloc de résultats temporaire nommé, déclaré avant une requête SQL avec le mot-clé WITH. Elle ne persiste pas en base de données : le moteur SQL la construit à la volée et la détruit dès que l’instruction se termine. Introduite dans le standard SQL:1999, elle est aujourd’hui supportée par MySQL, PostgreSQL, SQL Server, SQLite et Oracle. Sa syntaxe repose sur un nom, suivi d’une instruction SELECT encadrée par des parenthèses, puis de la requête principale qui l’exploite. Elle s’impose comme une alternative structurée aux sous-requêtes imbriquées dans les requêtes analytiques de moyenne à haute complexité.

Quels sont les différents types de CTE ?

Il existe quatre types d’expression de tables communes:

• La CTE standard déclare un résultat intermédiaire nommé, sans lien avec aucune autre CTE du bloc WITH.
• La CTE séquentielle fait référence à une expression de table commune déclarée plus haut dans le même bloc, permettant de chaîner plusieurs transformations successives. 
• La CTE imbriquée intègre la définition d’une autre expression de table commune à l’intérieur de sa propre déclaration, créant une structure hiérarchique.
•  La CTE récursive se référence elle-même pour parcourir des données hiérarchiques comme des organigrammes, des arborescences de catégories ou des nomenclatures produit.

Ces quatre types d’expression de table commune sont documentés officiellement selon les spécifications de SQL Server et PostgreSQL. Chacun répond à un besoin précis et implique une relation différente entre les blocs déclarés dans le même WITH. Identifier le bon type vous permet d’adapter la structure de votre instruction à la logique de votre requête.

On illustre ici la syntaxe de base d’une CTE standard avant d’aborder les formes plus avancées dans les sections suivantes.

WITH commandes_recentes AS (
    SELECT id_client, COUNT(*) AS nb_commandes
    FROM commandes
    WHERE date_commande >= '2024-01-01'
    GROUP BY id_client
)
SELECT c.nom, c.prenom, cr.nb_commandes
FROM clients c
INNER JOIN commandes_recentes cr ON c.id_client = cr.id_client;

La CTE commandes_recentes agrège les commandes postérieures au 1er janvier 2024 par client en une seule passe. La requête principale assemble ensuite ce résultat intermédiaire avec la table clients via un INNER JOIN. Assurez-vous que la colonne date_commande dispose d’un index pour éviter un parcours intégral de la table.

Pourquoi utiliser des expressions de table communes ?

La CTE résout un problème concret : les sous-requêtes imbriquées sur plusieurs niveaux deviennent rapidement illisibles et difficiles à déboguer. En nommant chaque étape logique, vous rendez l’intention de chaque bloc immédiatement compréhensible pour tout développeur qui lira le code ensuite. Un autre avantage décisif est la ré-utilisabilité : une expression de table commune déclarée une seule fois peut être référencée plusieurs fois dans la même instruction sans réécriture. Cette propriété évite la duplication de blocs SQL identiques et réduit le risque d’incohérence entre deux occurrences d’une même logique. Enfin, chaque expression de table commune peut être isolée et exécutée indépendamment pour valider son résultat avant d’être intégrée dans la requête complète.

On chaîne ici deux CTE séquentielles : la première calcule le chiffre d’affaires par client, la seconde filtre les profils au-dessus d’un seuil.

WITH ca_par_client AS (
    SELECT id_client, SUM(montant) AS chiffre_affaires
    FROM commandes
    GROUP BY id_client
),
clients_premium AS (
    SELECT id_client, chiffre_affaires
    FROM ca_par_client
    WHERE chiffre_affaires > 5000
)
SELECT c.nom, c.prenom, cp.chiffre_affaires
FROM clients c
INNER JOIN clients_premium cp ON c.id_client = cp.id_client;

La CTE ca_par_client calcule le total des montants par client sur l’ensemble de la table commandes, tandis que la CTE clients_premium consomme ce résultat et n’expose que les clients dépassant 5 000 unités de chiffre d’affaires. La requête finale joint ces données filtrées à la table clients pour récupérer les informations nominatives associées.

Les expressions de table communes améliorent-elles les performances ?

Le gain de performance d’une expression de table commune dépend du moteur SQL utilisé et non de la syntaxe elle-même. Dans la plupart des moteurs modernes, une expression de table commune non récursive est traitée comme une table dérivée et ne produit pas de gain intrinsèque par rapport à une sous-requête équivalente dans FROM. SQL Server peut choisir de matérialiser l’expression de table commune en mémoire lors de réutilisations multiples, ce qui évite de recalculer plusieurs fois le même bloc. PostgreSQL, selon sa version, matérialise systématiquement certaines CTE, ce qui peut au contraire ralentir l’exécution par rapport à une sous-requête directe. Le bénéfice principal des CTE reste avant tout architectural : lisibilité, modularité et maintenabilité du code SQL sur le long terme.

Quelle est la limite des expressions de table communes ?

La limite la plus structurelle d’une expression de table commune est sa portée : elle n’existe que le temps d’exécution de l’instruction WITH qui la déclare et ne peut pas être partagée entre plusieurs requêtes ou sessions. Une CTE récursive sans condition d’arrêt correctement définie peut déclencher une boucle infinie et saturer les ressources du serveur. SQL Server impose par défaut un plafond de 100 niveaux de récursion pour se prémunir contre ce risque, configurable via l’option MAXRECURSION. Contrairement à une table temporaire, une expression de table commune ne peut pas être indexée directement, ce qui peut devenir limitant sur de très grands volumes de données. Pour des besoins de performance avancés, une table temporaire avec index ou une vue matérialisée offrent davantage de contrôle sur le plan d’exécution.

Les CTE sont-ils une mauvaise pratique en SQL ?

Non, les CTE sont une construction recommandée par les documentations officielles de tous les grands SGBD du marché. Elles deviennent problématiques uniquement dans deux situations précises : quand elles remplacent des jointures simples sans apporter de lisibilité supplémentaire, ou quand une expression de table commune récursive est mal bornée. Sur de très grands volumes de données, une CTE matérialisée par le moteur peut consommer plus de mémoire qu’une jointure directement optimisée par l’optimiseur de requêtes. Dans ce cas, une table temporaire avec un index ciblé constitue une alternative plus performante et plus prévisible. En dehors de ces cas limites, intégrer des CTE dans votre code SQL améliore sa qualité, sa lisibilité et sa facilité de maintenance.

Peut-on utiliser des CTE dans une vue ?

Oui, la clause WITH peut être incluse directement dans le corps d’un CREATE VIEW, ce qui est supporté par PostgreSQL, SQL Server et Oracle. L’expression de table commune intégrée à la vue reste invisible depuis l’extérieur : seul le résultat final de la vue est exposé aux requêtes appelantes et aux utilisateurs autorisés. Cette approche est particulièrement utile pour structurer des vues analytiques complexes sans multiplier les sous-requêtes imbriquées dans la définition. La expression de table commune est recalculée à chaque appel de la vue, comme n’importe quelle sous-requête : aucune donnée n’est mise en cache entre deux exécutions. Consultez la documentation officielle de votre SGBD avant de déployer cette structure, car la compatibilité peut varier selon la version installée.

On crée ici une vue analytique qui intègre une CTE pour pré-agréger les données avant de les exposer comme table virtuelle permanente.

CREATE VIEW vue_clients_premium AS
WITH ca_par_client AS (
    SELECT id_client, SUM(montant) AS chiffre_affaires
    FROM commandes
    GROUP BY id_client
)
SELECT c.nom, c.prenom, ca.chiffre_affaires
FROM clients c
INNER JOIN ca_par_client ca ON c.id_client = ca.id_client
WHERE ca.chiffre_affaires > 5000;

La vue vue_clients_premium encapsule la logique d’agrégation dans un objet SQL permanent interrogeable par toutes les requêtes futures. La expression de table commune interne est reconstruite à chaque appel de la vue, sans mise en cache des données entre deux exécutions. Testez la CTE seule avant de créer la vue pour vous assurer que le résultat intermédiaire est correct.

Quelle est la différence entre une vue et une CTE en SQL ?

La distinction fondamentale porte sur la durée de vie : une vue est un objet permanent enregistré dans le catalogue de la base, accessible indéfiniment par toutes les sessions. Une CTE disparaît dès que l’instruction WITH qui la contient termine son exécution, sans laisser aucune trace dans la base. Ni l’une ni l’autre ne stockent physiquement les données : toutes deux enregistrent une définition de requête et recalculent les résultats à chaque appel. La expression de table commune supporte la récursivité native, ce qu’une vue standard ne peut pas reproduire sans passer par des procédures stockées ou des fonctions. La vue permet de configurer des droits d’accès par utilisateur, ce qui est impossible avec une CTE dont la portée est strictement limitée à une instruction.

Critère	CTE (WITH)	Vue (CREATE VIEW)
Durée de vie	Une seule instruction	Permanente en base
Réutilisabilité	Même instruction uniquement	Toutes sessions et requêtes
Récursivité	Oui	Non
Stockage des données	Non	Non
Gestion des droits	Non applicable	Configurable par utilisateur
Indexation directe	Non	Non (sauf vue matérialisée)

Quand utiliseriez-vous une CTE plutôt qu’une sous-requête ?

Optez pour une expression de table commune dès qu’un même bloc de calcul doit être référencé plusieurs fois dans la même instruction SQL. Réécrire deux fois la même sous-requête imbriquée est un signal clair qu’une CTE nommée s’impose pour éviter la duplication. Elle est également préférable quand la logique de la sous-requête est assez complexe pour bénéficier d’un nom explicite qui documente son rôle dans l’instruction. La expression de table commune s’impose aussi dès que vous avez besoin de récursivité pour traverser une hiérarchie, ce qu’une sous-requête classique ne peut pas accomplir. Au-delà de deux niveaux d’imbrication, restructurez systématiquement vos sous-requêtes en CTE séquentielles pour préserver la lisibilité du code.

On transforme ici une sous-requête doublement imbriquée en deux CTE séquentielles lisibles et testables séparément.

-- Sous-requête imbriquée (deux niveaux, difficile à déboguer)
SELECT nom FROM clients
WHERE id_client IN (
    SELECT id_client FROM commandes
    WHERE montant > (SELECT AVG(montant) FROM commandes)
);

-- Réécriture avec CTE séquentielles
WITH seuil AS (
    SELECT AVG(montant) AS valeur_moyenne FROM commandes
),
clients_au_dessus AS (
    SELECT DISTINCT id_client FROM commandes
    WHERE montant > (SELECT valeur_moyenne FROM seuil)
)
SELECT c.nom
FROM clients c
INNER JOIN clients_au_dessus ca ON c.id_client = ca.id_client;

La CTE seuil isole le calcul de la moyenne dans un bloc nommé, testable indépendamment avant d’être consommé. La CTE clients_au_dessus utilise cette valeur pour filtrer les identifiants clients concernés, sans répéter le calcul. Le mot-clé DISTINCT évite les doublons si un même client possède plusieurs commandes dépassant le seuil calculé.

Sources MySQL, PostgreSQL, SqlServer