Expressions de table communes (WITH)
Transformez les requêtes imbriquées en clauses WITH lisibles, enchaînez les CTE et découvrez les règles de matérialisation de PostgreSQL 12 et versions ultérieures.
Expressions de table communes (WITH) est une leçon SQL Academy 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 Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.
Qu'est-ce qu'une CTE ?
Une expression de table commune (CTE) est une sous-requête nommée définie avec WITH :
WITH paid_orders AS (
SELECT * FROM orders WHERE status = 'paid'
)
SELECT user_id, COUNT(*)
FROM paid_orders
GROUP BY user_id;Pourquoi utiliser des CTE ?
Trois grands avantages :
- Lisibilité — décomposer une requête de 200 lignes en étapes nommées
- Réutilisation — référencer plusieurs fois le même résultat intermédiaire
- Récursivité — seules les CTE prennent en charge les requêtes récursives (leçon suivante)
Enchaîner les CTE
Définissez plusieurs CTE dans un seul WITH et utilisez-en une dans la suivante :
WITH last_30 AS (
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '30 days'
),
per_user AS (
SELECT user_id, SUM(total) AS revenue FROM last_30 GROUP BY user_id
)
SELECT u.email, p.revenue
FROM users u
JOIN per_user p ON p.user_id = u.id
ORDER BY p.revenue DESC LIMIT 20;Réutiliser une CTE
Si le même résultat intermédiaire est utilisé deux fois, une CTE rend l'intention évidente :
WITH recent_users AS (
SELECT id FROM users WHERE created_at >= NOW() - INTERVAL '7 days'
)
SELECT 'new orders' AS metric, COUNT(*) FROM orders
WHERE user_id IN (SELECT id FROM recent_users)
UNION ALL
SELECT 'new revenue', SUM(total) FROM orders
WHERE user_id IN (SELECT id FROM recent_users);Matérialisation des CTE (PostgreSQL ≤ 11)
Les anciennes versions de PG matérialisaient toujours la sortie des CTE — ce qui créait une barrière pour le planificateur. À partir de PG 12, le planificateur intègre les CTE par défaut, sauf indication contraire :
-- Force the old materialise behaviour (rarely needed):
WITH x AS MATERIALIZED (SELECT ...) ...
-- Force inlining (default):
WITH x AS NOT MATERIALIZED (SELECT ...) ...CTE modifiant les données
Les CTE peuvent utiliser INSERT/UPDATE/DELETE — pratique pour déplacer des lignes entre des tables de manière atomique :
WITH moved AS (
DELETE FROM orders WHERE status = 'archived' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;CTE dans le DML
RETURNING + WITH pour « trouver et agir » :
WITH cancelled AS (
UPDATE orders SET status = 'cancelled'
WHERE created_at < NOW() - INTERVAL '14 days'
AND status = 'pending'
RETURNING id
)
INSERT INTO audit_log (event, order_id)
SELECT 'auto-cancel', id FROM cancelled;Ordre d'exécution
Les CTE modifiant les données s'exécutent indépendamment dans le même instantané. Chaque instruction voit l'état BEFORE du début de toutes les modifications — c'est surprenant, mais prévisible.
CTE, sous-requêtes et vues
Comparaison :
- Sous-requête — définie directement dans la requête ; utilisée une seule fois
- CTE — nommée ; utilisée plusieurs fois dans la requête ; supprimée à la fin de la requête
- Vue — nommée ; persistante ; réutilisable dans plusieurs requêtes
N'utilisez pas trop les CTE
Tout encapsuler dans une CTE rend les requêtes lisibles, mais peut masquer leur coût. Avec d'énormes résultats intermédiaires, le planificateur peut choisir de moins bons plans que pour un JOIN équivalent.
Récapitulatif
Les CTE nomment les requêtes intermédiaires.
- Améliorent la lisibilité
- Permettent la réutilisation dans une même requête
- Sont nécessaires pour la récursivité
- Sont intégrées par défaut dans les versions modernes de PG
Vérification rapide
Quel mot-clé commence une expression de table commune ?
Questions Fréquemment Posées
La leçon « Expressions de table communes (WITH) » est-elle gratuite ?
Oui — le texte complet de « Expressions de table communes (WITH) » 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 Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.
Qu'est-ce que j'apprendrai dans « Expressions de table communes (WITH) » ?
Transformez les requêtes imbriquées en clauses WITH lisibles, enchaînez les CTE et découvrez les règles de matérialisation de PostgreSQL 12 et versions ultérieures. Tu pratiques SQL Academy 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 Academy ?
Aucune expérience préalable n'est requise. SQL Academy 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 « Expressions de table communes (WITH) » ?
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 Academy ?
Oui. Chaque leçon SQL Academy 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
- Sous-requêtes scalaires, de lignes et de tables
- Sous-requêtes corrélées et non corrélées
- Expressions de table communes (WITH)
- CTE récursives pour les hiérarchies