Pour aller plus loin sur les sous-requêtes imbriquées et corrélées, consultez notre article requêtes imbriquées et corrélées en SQL.

Cas d'usage concrets en entretien data

Calculer un classement par catégorie

Imaginons une table ventes avec les colonnes vendeur, categorie et montant. On veut afficher les 3 meilleurs vendeurs par catégorie.

WITH classement AS (
  SELECT
    vendeur,
    categorie,
    montant,
    RANK() OVER (PARTITION BY categorie ORDER BY montant DESC) AS rang
  FROM ventes
)
SELECT vendeur, categorie, montant
FROM classement
WHERE rang <= 3;

Sans WITH, cette requête aurait nécessité une sous-requête imbriquée dans le FROM. Avec WITH, la logique de classement est isolée et immédiatement compréhensible.

Filtrer après agrégation

WITH est très pratique pour appliquer un filtre après un GROUP BY, sans avoir à utiliser HAVING ou une sous-requête.

WITH chiffre_affaires AS (
  SELECT
    client_id,
    SUM(montant) AS total
  FROM commandes
  GROUP BY client_id
)
SELECT client_id, total
FROM chiffre_affaires
WHERE total > 10000
ORDER BY total DESC;

Ce pattern — agréger dans une CTE, filtrer dans la requête principale — est l'un des plus fréquents en analyse de données.

Combiner plusieurs étapes analytiques

Un cas très courant en data analyse : calculer une métrique en plusieurs étapes (taux de conversion, panier moyen, rétention). Chaque étape devient une CTE.

WITH
  commandes_par_client AS (
    SELECT client_id, COUNT(*) AS nb_commandes
    FROM commandes
    GROUP BY client_id
  ),
  clients_actifs AS (
    SELECT client_id
    FROM commandes_par_client
    WHERE nb_commandes >= 3
  )
SELECT c.nom, c.email
FROM clients c
INNER JOIN clients_actifs ca ON c.id = ca.client_id;

Ce type de requête en cascade est exactement ce que les recruteurs cherchent à voir : une logique claire, structurée, et maintenable.

Les erreurs fréquentes avec WITH

Déclarer une CTE sans l'utiliser

Certains SGBD comme PostgreSQL retournent une erreur si une CTE est déclarée mais jamais référencée dans la requête principale. C'est une erreur qui arrive souvent lors du refactoring.

Oublier la virgule entre deux CTEs

La virgule entre deux CTEs est obligatoire. Oublier ce détail provoque une erreur de syntaxe immédiate. Le mot-clé WITH ne s'écrit qu'une seule fois, au début du bloc.

Confondre CTE et vue matérialisée

Une CTE n'est pas mise en cache entre plusieurs références dans la même requête sur tous les SGBD. Dans PostgreSQL, la CTE est matérialisée par défaut (jusqu'à la version 12), ce qui peut parfois dégrader les performances. À partir de PostgreSQL 12, le comportement par défaut est laissé à l'optimiseur, mais vous pouvez forcer avec WITH ... AS MATERIALIZED ou WITH ... AS NOT MATERIALIZED.

Pour les cas où vous avez besoin d'une vue persistante et optimisée, consultez notre guide sur les vues matérialisées SQL.

WITH et les fonctions fenêtre : une combinaison puissante

Les fonctions fenêtre comme RANK(), LAG() ou SUM() OVER(...) produisent des colonnes calculées qu'on ne peut pas filtrer directement dans un WHERE. La clause WITH résout élégamment ce problème.

WITH evolution AS (
  SELECT
    date_vente,
    montant,
    LAG(montant) OVER (ORDER BY date_vente) AS montant_precedent
  FROM ventes
)
SELECT
  date_vente,
  montant,
  montant - montant_precedent AS evolution
FROM evolution
WHERE montant_precedent IS NOT NULL;

Sans WITH, on serait obligé d'utiliser une sous-requête dans le FROM, ce qui est plus difficile à lire et à maintenir.

Performances : ce que dit la documentation

D'après la documentation officielle de PostgreSQL (v15), une CTE non matérialisée est traitée comme une sous-requête inline, ce qui laisse l'optimiseur l'intégrer au plan d'exécution global. Une CTE matérialisée est calculée une fois et mise en mémoire.

En pratique, pour des CTEs simples et peu volumineuses, la différence de performance est négligeable. Pour des CTEs sur des tables de plusieurs millions de lignes, il vaut mieux utiliser EXPLAIN ANALYZE pour vérifier le plan d'exécution réel.

La règle générale : privilégiez la lisibilité avec WITH, et optimisez avec EXPLAIN si vous observez un problème de performance.

Questions fréquentes

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

Une vue est un objet permanent en base de données, accessible par n'importe quelle requête. Une CTE définie avec WITH est temporaire et ne vit que le temps de la requête. La vue nécessite des droits de création ; la CTE non.

Peut-on utiliser WITH dans un INSERT ou un UPDATE ?

Oui. La clause WITH peut précéder un INSERT, un UPDATE ou un DELETE dans la plupart des SGBD modernes. Par exemple, dans PostgreSQL, on peut écrire WITH données AS (...) INSERT INTO table SELECT * FROM données;. C'est utile pour préparer les données avant insertion.

Une CTE améliore-t-elle toujours les performances ?

Non. Une CTE améliore principalement la lisibilité. L'impact sur les performances dépend du SGBD, de la version, et du fait que la CTE soit matérialisée ou non. Mesurez toujours avec EXPLAIN ANALYZE avant de conclure.

Combien de CTEs peut-on enchaîner dans un seul bloc WITH ?

Il n'y a techniquement pas de limite fixée par le standard SQL. En pratique, au-delà de 5 à 6 CTEs, il vaut mieux envisager une vue ou une table temporaire pour maintenir la lisibilité et faciliter le débogage.

WITH RECURSIVE est-il différent de WITH ?

Oui. WITH RECURSIVE permet à une CTE de se référencer elle-même, ce qui est nécessaire pour parcourir des structures hiérarchiques (arborescences, graphes). La syntaxe et la logique sont différentes et font l'objet d'un traitement spécifique dans notre article sur les requêtes récursives SQL.

Conclusion

La clause WITH est l'un des outils les plus utiles du SQL moderne. Elle ne remplace pas les sous-requêtes ou les jointures, mais elle les rend beaucoup plus lisibles et maintenables dès que la logique dépasse quelques niveaux. En entretien data, l'écrire correctement du premier coup — bonne syntaxe, bonne structure, bonnes nominations — est un signal fort de maturité technique.

Entraînez-vous à réécrire vos sous-requêtes imbriquées avec WITH : vous verrez immédiatement la différence de clarté. Pour mettre ces connaissances en pratique sur des exercices réels, rendez-vous sur SQL Pratique et testez vos requêtes directement dans l'environnement en ligne.