0Pricing
SQL Interview Prep · Leçon

CTE, sous-requête ou table temporaire

Comparer les compromis liés à la matérialisation, à la réutilisation et au comportement de l’optimiseur

CTE, sous-requête ou table temporaire est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 3 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Interview Prep comprend 4 leçons au total.

Trois façons de structurer la logique

Lorsqu'une requête a besoin d'un résultat intermédiaire, vous disposez de trois outils courants : une sous-requête, une CTE et une table temporaire. Les évaluateurs vous demandent de les comparer, car votre choix indique si vous comprenez la matérialisation et le comportement de l'optimiseur.

Cette leçon vous fournit un cadre de décision que vous pourrez restituer sous pression.

La sous-requête

Une sous-requête est une requête intégrée dans une autre, souvent dans FROM, WHERE ou SELECT. Elle fait partie de la même instruction et l'optimiseur la considère comme une seule unité.

  • Aucun nom n'est nécessaire (les tables dérivées nécessitent toutefois un alias).
  • L'optimiseur est libre de la fusionner avec la requête externe.
  • Elle devient verbeuse et difficile à lire lorsqu'elle est profondément imbriquée.
SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) t
WHERE t.total > 1000;

La CTE

Une CTE est une sous-requête nommée dans un bloc WITH, dont la portée est limitée à une seule instruction. Elle se lit mieux qu'une sous-requête profondément imbriquée et peut être référencée plusieurs fois.

  • Elle est nommée, ce qui documente son intention.
  • Elle peut être référencée plusieurs fois dans une même instruction.
  • Sa portée reste limitée à une seule instruction, après quoi elle disparaît.
WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM spend
WHERE total > 1000;

La table temporaire

Une table temporaire est une table réelle et physique qui existe pendant la session (ou la transaction). Vous la remplissez avec une instruction, puis vous l'interrogez dans des instructions distinctes ultérieures.

  • Elle persiste pendant plusieurs instructions de la session.
  • Vous pouvez lui ajouter des index et recueillir des statistiques.
  • Elle entraîne des E/S disque et nécessite un nettoyage explicite.
CREATE TEMP TABLE spend AS
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

SELECT * FROM spend WHERE total > 1000;

La matérialisation : la distinction essentielle

Le concept clé que les évaluateurs cherchent à vérifier est la matérialisation : le fait que le résultat intermédiaire soit ou non physiquement écrit quelque part.

  • Les sous-requêtes et les CTE ne sont généralement pas matérialisées ; l'optimiseur les intègre souvent directement.
  • Une table temporaire est toujours matérialisée sur un support de stockage.
  • Certaines bases de données permettent de forcer ou d'empêcher la matérialisation des CTE à l'aide d'indications.

Barrières d'optimisation et ancien piège de PostgreSQL

Historiquement, PostgreSQL traitait chaque CTE comme une barrière d'optimisation : il la matérialisait et empêchait la propagation des prédicats vers les sources. Depuis PostgreSQL 12, les CTE simples et non récursives référencées une seule fois sont intégrées directement par défaut, avec les indications MATERIALIZED et NOT MATERIALIZED pour remplacer ce comportement.

Mentionner cette nuance est un excellent signal de maîtrise pour un profil expérimenté.

WITH spend AS NOT MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total
    FROM orders GROUP BY customer_id
)
SELECT * FROM spend WHERE total > 1000;

Réutilisation au sein d'une même instruction

Si vous référencez plusieurs fois le même résultat intermédiaire dans une seule instruction, une CTE peut être plus claire que la répétition d'une sous-requête. Attention toutefois : une CTE intégrée directement peut être recalculée à chaque référence.

Lorsque le recalcul est coûteux, forcer la matérialisation (ou utiliser une table temporaire) évite d'effectuer le travail deux fois.

Réutilisation entre plusieurs instructions

Les CTE et les sous-requêtes n'existent que pendant une seule instruction. Si vous avez besoin du même résultat dans plusieurs requêtes distinctes, la table temporaire est l'outil adapté.

Cas courant : un processus ETL ou un rapport en plusieurs étapes, dans lequel vous construisez une seule fois un ensemble de données de préparation, puis exécutez plusieurs analyses dessus. L'indexation de la table temporaire peut alors accélérer chacune des requêtes suivantes.

Indexation et statistiques

Seule une table temporaire peut contenir des index et des statistiques récentes. Pour un ensemble intermédiaire volumineux joint à de nombreuses reprises, cela peut être déterminant.

  • CTE/sous-requête : l'optimiseur estime les coûts à partir des tables sous-jacentes.
  • Table temporaire : vous pouvez exécuter ANALYZE dessus et ajouter des index adaptés à vos jointures ultérieures.

Ainsi, pour les résultats volumineux et fortement réutilisés, une table temporaire peut offrir de meilleures performances malgré les étapes supplémentaires.

Le cadre de décision

Voici une réponse concise pour l'entretien :

  • Sous-requête : utilisation ponctuelle, imbrication limitée, lisibilité satisfaisante.
  • CTE : améliore la lisibilité ou est référencée plusieurs fois dans une même instruction.
  • Table temporaire : réutilisée entre plusieurs instructions, très volumineuse, ou nécessaire lorsque vous avez besoin d'index et de statistiques.

Par défaut, choisissez une CTE pour la clarté ; utilisez une table temporaire lorsque la matérialisation ou la réutilisation entre plusieurs instructions apporte réellement un avantage.

Comment présenter le compromis

Évitez les affirmations absolues comme « les CTE sont toujours plus lentes ». Dites plutôt : les CTE et les sous-requêtes sont généralement intégrées directement, donc elles servent surtout la lisibilité ; une table temporaire est matérialisée et vaut la peine lorsque je réutilise un résultat volumineux entre plusieurs instructions ou que j'ai besoin d'un index.

Reconnaître que ce comportement dépend du moteur (et de la version de PostgreSQL) montre une véritable maîtrise du sujet.

Vérification rapide

Choisissez le scénario dans lequel une table temporaire est clairement le meilleur choix.

Récapitulatif : CTE, sous-requête ou table temporaire

Le choix dépend principalement de la matérialisation et de la portée.

  • Sous-requêtes et CTE : généralement intégrées directement, limitées à une seule instruction, choisies pour leur lisibilité.
  • Les CTE ajoutent un nom et permettent la réutilisation au sein d'une même instruction.
  • Tables temporaires : toujours matérialisées, persistantes entre plusieurs instructions et indexables.
  • PostgreSQL 12+ intègre directement les CTE simples ; utilisez les indications MATERIALIZED pour contrôler ce comportement.

Ensuite : transformer une requête imbriquée et confuse en CTE claires.

Questions Fréquemment Posées

La leçon « CTE, sous-requête ou table temporaire » est-elle gratuite ?

Oui — le texte complet de « CTE, sous-requête ou table temporaire » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Interview Prep, passe à CoddyKit PRO. Le cours SQL Interview Prep comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « CTE, sous-requête ou table temporaire » ?

Comparer les compromis liés à la matérialisation, à la réutilisation et au comportement de l’optimiseur Tu pratiques SQL Interview Prep avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Interview Prep ?

Aucune expérience préalable n'est requise. SQL Interview Prep sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 3 sur 4.

Combien de temps prend la leçon « CTE, sous-requête ou table temporaire » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Interview Prep ?

Oui. Chaque leçon SQL Interview Prep inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. Écrire votre premier CTE
  2. Enchaîner plusieurs CTE
  3. CTE, sous-requête ou table temporaire
  4. Transformer des requêtes imbriquées en CTE
← Retour à SQL Interview Prep