Optimiser ses requêtes SQL, c’est indispensable quand on travaille régulièrement sur un ensemble de tables ou base de données. Tu as sans doute déjà écrit des requêtes SQL qui commencent à ressembler à une usine à gaz avec beaucoup (trop) de sous-requêtes imbriquées. C’est là qu’intervient l’instruction WITH, aussi appelée CTE (Common Table Expression). Dans cet article, on va voir ce que c’est, pourquoi c’est utile, et surtout comment s’en servir concrètement 😉.
C’est quoi une CTE ?
Une CTE (Common Table Expression) est une table temporaire nommée, définie au début d’une requête SQL grâce au mot-clé WITH. Elle n’existe que le temps de l’exécution de la requête principale : elle n’est pas stockée en base, elle ne crée pas de vue, elle est simplement disponible comme une table virtuelle pour la requête qui suit.
La syntaxe de base ressemble à ça :
WITH nom_cte AS (
SELECT ...
FROM ...
WHERE ...
)
SELECT *
FROM nom_cte;Simple, lisible, et puissant. Voyons maintenant pourquoi tu devrais l’utiliser.
Pourquoi utiliser une CTE pour optimiser ses requêtes ?
Lisibilité et maintenabilité du code
Le premier avantage des CTEs, c’est la clarté. Plutôt que d’imbriquer des sous-requêtes dans des sous-requêtes, tu décomposes ta logique en blocs nommés et lisibles. Ton collègue (ou toi-même dans 6 mois) te remerciera.
Compare ces deux approches pour récupérer les clients ayant passé plus de 3 commandes :
Sans CTE (sous-requête imbriquée) :
SELECT *
FROM clients
WHERE id IN (
SELECT client_id
FROM commandes
GROUP BY client_id
HAVING COUNT(*) > 3
);Avec l’utilisation de CTE :
WITH clients_actifs AS (
SELECT client_id
FROM commandes
GROUP BY client_id
HAVING COUNT(*) > 3
)
SELECT *
FROM clients
WHERE id IN (SELECT client_id FROM clients_actifs);La logique est séparée, le bloc s’appelle clients_actifs : on sait immédiatement ce qu’il représente.
Réutilisabilité dans la même requête
Une CTE peut être référencée plusieurs fois dans la requête principale, ce qui évite de répéter le même bloc de code. C’est particulièrement utile quand tu as une logique de filtrage ou d’agrégation complexe que tu dois appliquer à plusieurs endroits.
WITH ventes_2026 AS (
SELECT vendeur_id, SUM(montant) AS total
FROM ventes
WHERE YEAR(date_vente) = 2026
GROUP BY vendeur_id
)
SELECT v.vendeur_id, v.total, e.nom
FROM ventes_2026 v
JOIN employes e ON e.id = v.vendeur_id
WHERE v.total > (SELECT AVG(total) FROM ventes_2026);Ici, ventes_2026 est utilisée deux fois : dans le JOIN et dans la sous-requête du WHERE. Sans CTE, il faudrait dupliquer toute la logique.
CTEs multiples : chaîner les étapes
Tu peux définir plusieurs CTEs dans la même requête, et même les faire référencer les unes les autres. C’est idéal pour construire une logique étape par étape.
WITH
commandes_recentes AS (
SELECT *
FROM commandes
WHERE date_commande >= DATE_SUB(NOW(), INTERVAL 30 DAY)
),
total_par_client AS (
SELECT client_id, SUM(montant) AS total
FROM commandes_recentes
GROUP BY client_id
),
top_clients AS (
SELECT client_id, total
FROM total_par_client
WHERE total > 500
)
SELECT c.nom, c.email, t.total
FROM clients c
JOIN top_clients t ON t.client_id = c.id
ORDER BY t.total DESC;On décompose la logique en 3 étapes claires : filtrer les commandes récentes, calculer les totaux, garder uniquement les tops clients. Chaque CTE a une responsabilité unique.
Les CTEs récursives : un cas d’usage puissant
Les CTEs supportent la récursivité, ce qui les rend indispensables pour travailler avec des données hiérarchiques comme des arborescences (catégories, organigrammes, menus…).
Voici un exemple concret pour afficher toute une hiérarchie de catégories :
WITH RECURSIVE arbre_categories AS (
-- Ancre : point de départ (catégories racines)
SELECT id, nom, parent_id, 0 AS niveau
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- Partie récursive : enfants de chaque noeud
SELECT c.id, c.nom, c.parent_id, a.niveau + 1
FROM categories c
JOIN arbre_categories a ON a.id = c.parent_id
)
SELECT *
FROM arbre_categories
ORDER BY niveau, nom;La CTE s’appelle elle-même jusqu’à ce qu’il n’y ait plus d’enfants à trouver. Le moteur SQL prends les résultats de la première itération (stockée dans arbre_categories) puis cherche tous les enfants dont le parent_id correspond. C’est quelque chose qu’il serait très difficile de faire proprement sans cette fonctionnalité.
CTEs et performances : ce qu’il faut vraiment savoir
Un point souvent mal compris : les CTEs ne sont pas automatiquement plus rapides qu’une sous-requête. Leur avantage principal est la lisibilité (c’est aussi une façon d’optimiser ses requêtes 😉). Cependant, quelques éléments méritent attention :
Matérialisation vs. inline
Selon le moteur SQL utilisé, une CTE peut être :
- Inline : le moteur la « déplie » comme une sous-requête et l’optimise globalement avec la requête principale. C’est ce que fait PostgreSQL par défaut depuis la version 12.
- Matérialisée : le moteur exécute la CTE une fois, stocke le résultat en mémoire, puis l’utilise. MySQL/MariaDB matérialise les CTEs, ce qui peut introduire un coût si le résultat est volumineux.
Sur PostgreSQL, tu peux forcer la matérialisation avec WITH nom AS MATERIALIZED (...) ou l’empêcher avec NOT MATERIALIZED.
Éviter de charger inutilement
Si ta CTE charge des millions de lignes mais que ta requête finale n’en utilise que quelques dizaines, pense à filtrer dès la CTE plutôt qu’après. Les index ne traversent pas toujours la barrière d’une CTE matérialisée : mets tes conditions de filtrage au bon endroit. Il faut toujours bien garder en tête les index pour optimiser les requêtes SQL.
En parlant d’optimisation des performances, si tu veux aller plus loin sur ce sujet, j’ai écrit un article complet sur comment améliorer les performances de ton application web. Les bonnes pratiques côté base de données s’inscrivent dans une démarche globale d’optimisation.
CTEs vs vues vs tables temporaires
Pour t’aider à choisir la bonne approche :
| Caractéristique | CTE | Vue | Table temporaire |
|---|---|---|---|
| Durée de vie | Requête unique | Persistante | Session |
| Stockage | Aucun | Aucun (ou matérialisée) | Disque/mémoire |
| Réutilisable entre requêtes | Non | Oui | Oui |
| Récursivité | Oui | Non | Non |
| Lisibilité | Excellente | Bonne | Moyenne |
Cas d’usage où les CTEs sont indispensables
- Rapports analytiques complexes : décomposer les calculs en étapes (agrégation → filtrage → ranking).
- Hierarchies et arbres : catégories, menus, commentaires imbriqués, organigrammes.
- Déduplication : isoler les doublons dans une CTE avant de les traiter.
- Pagination avancée : calculer les offsets ou les totaux dans une CTE, les utiliser dans la requête principale.
- Calculs inter-lignes : cumuls, moyennes glissantes : souvent combinés avec les fonctions de fenêtrage (
OVER,PARTITION BY)
Exemple complet : classement des vendeurs avec cumul
Pour finir, voici un exemple un peu plus complet qui combine une CTE avec des fonctions de fenêtrage :
WITH ventes_mensuelles AS (
SELECT
vendeur_id,
DATE_FORMAT(date_vente, '%Y-%m') AS mois,
SUM(montant) AS total_mois
FROM ventes
WHERE YEAR(date_vente) = 2026
GROUP BY vendeur_id, DATE_FORMAT(date_vente, '%Y-%m')
),
classement AS (
SELECT
vendeur_id,
mois,
total_mois,
RANK() OVER (PARTITION BY mois ORDER BY total_mois DESC) AS rang,
SUM(total_mois) OVER (PARTITION BY vendeur_id ORDER BY mois) AS cumul
FROM ventes_mensuelles
)
SELECT e.nom, c.mois, c.total_mois, c.rang, c.cumul
FROM classement c
JOIN employes e ON e.id = c.vendeur_id
WHERE c.rang <= 3
ORDER BY c.mois, c.rang;Cette requête retourne le top 3 des vendeurs chaque mois avec leur cumul annuel. Imagines faire ça avec des sous-requêtes imbriquées : bonne chance pour la relire 3 semaines plus tard 😅.
Optimiser ses requêtes SQL avec les CTEs
Les CTEs avec WITH sont l’un de ces outils SQL qui, une fois adoptés, deviennent indispensables. Elles rendent le code plus lisible, plus maintenable, et permettent d’exprimer des logiques complexes de manière structurée. Côté performances, elles ne sont pas magiques, il faut comprendre comment ton moteur les traite, mais elles s’intègrent parfaitement dans une stratégie d’optimisation globale.
Si tu veux aller plus loin et comprendre comment ces optimisations SQL s’inscrivent dans une vision plus large de la performance applicative, je t’invite à lire mon article sur l’amélioration des performances de ton application web.
Aussi, tu peux consulter le guide suivant pour aller plus loin sur les CTE :Apprendre à utiliser et manipuler les CTE (développez.com).