Création de tables temporaires avec WITH

Les tables temporaires, c’est super pratique pour garder des résultats intermédiaires et les manipuler ensuite. C’est un peu comme en programmation : au lieu de copier-coller un gros bout de code partout, tu le mets dans une variable et tu l’utilises tranquille. Dans le monde SQL, ces "variables" sont souvent des tables temporaires.

Mais les CTE rendent ça encore plus simple — tu peux faire des tables temporaires sans te prendre la tête :

• Pas besoin de s’occuper de la suppression.

  Un CTE ne vit que le temps de l’exécution de la requête. Dès que le SQL a fini, le CTE disparaît, comme un assistant invisible qui a tout fait et s’est évaporé sans laisser de trace.

• Le code est plus lisible.

  Créer un CTE, c’est bien plus simple que de gérer la création et la suppression d’une vraie table temporaire. Tout est sous tes yeux, tout est clair.

• Parfait pour les besoins "one-shot".

  Si tu veux juste traiter des données à une étape précise — le CTE est nickel. Rapide, propre, sans effets de bord.

Syntaxe d’un CTE comme table temporaire

On rafraîchit la syntaxe d’un CTE :

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

Ici, WITH crée une "expression de table" temporaire, accessible dans toute la requête où elle est définie. Ça ressemble à une table, mais ça ne vit que jusqu’à la fin de la requête.

Exemple : compter les meilleurs élèves

On va créer une table temporaire avec un CTE pour trouver les étudiants qui ont une moyenne (grade) supérieure à 90. Ensuite, on affiche la liste.

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Voilà ce qu’on fait :

• on utilise WITH pour créer la table temporaire high_achievers.
• à l’intérieur du CTE, on regroupe les notes (grades) pour chaque étudiant (student_id) et on calcule la moyenne.
• dans la requête SELECT *, on se réfère juste à la table temporaire high_achievers comme à une table normale.

Comparaison entre CTE et tables temporaires

Parfois, on se demande : c’est quoi la différence entre utiliser un CTE et une table temporaire créée avec CREATE TEMP TABLE ?

Voici un exemple de table temporaire classique (CREATE TEMP TABLE) pour les mêmes données :

CREATE TEMP TABLE high_achievers_temp AS
	SELECT student_id, AVG(grade) AS avg_grade
	FROM grades
	GROUP BY student_id
	HAVING AVG(grade) > 90;

SELECT *
FROM high_achievers_temp;

DROP TABLE high_achievers_temp;  -- N’oublie pas de supprimer la table !

Et la même requête avec un CTE :

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Quand utiliser un CTE, et quand une table temporaire ?

Le CTE est parfait si tu veux préparer des données intermédiaires et les utiliser tout de suite — sans prise de tête. Pas besoin de penser à la suppression : le CTE disparaît tout seul à la fin de la requête. Il est visible direct dans la structure du code SQL, pas caché ailleurs comme une table temporaire. Ça rend les requêtes plus propres, plus simples et plus lisibles, surtout si tu fais plusieurs étapes de traitement à la suite. Et tu peux même combiner les CTE, les imbriquer et faire des trucs complexes — on verra ça plus tard.

Cette structure marche super bien quand la requête est ponctuelle et que tu n’as besoin des données que dans ce contexte. Mais si tu veux réutiliser le résultat à plusieurs endroits dans le système ou garder les données intermédiaires pendant toute la session — la table temporaire sera plus fiable. Surtout si tu as beaucoup de données et que tu veux de la perf : dans ces cas-là, les vraies tables temporaires sont plus stables et rapides.

Tout dépend de ce que tu veux faire : le CTE, c’est l’outil rapide et élégant pour traiter des données localement. La table temporaire, c’est le bourrin pour les scénarios plus lourds et longs.

Exemple : Agrégation de données

Imaginons qu’on a une table enrollments qui contient les inscriptions des étudiants aux cours. On veut savoir combien d’étudiants sont inscrits à chaque cours, mais seulement pour les cours avec plus de 5 étudiants.

Avec un CTE, ça donne :

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
    HAVING COUNT(student_id) > 5
)

SELECT *
FROM course_counts
ORDER BY student_count DESC;

CTE multiples : étapes en chaîne

Et si tu veux découper le boulot en plusieurs étapes ? Par exemple, d’abord sélectionner les étudiants avec une bonne moyenne, puis trouver leurs cours ? Facile !

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Maintenant, la requête a deux CTE :

1. high_achievers trouve les meilleurs élèves.
2. student_courses fait le lien entre étudiants et cours.

Résultat : une liste des étudiants avec une bonne moyenne et leurs cours.

Erreurs fréquentes avec les CTE

Des données trop volumineuses. Les CTE sont matérialisés en mémoire dans PostgreSQL. Si tu crées un CTE avec un résultat énorme, ça peut ralentir la requête ou dépasser la limite de mémoire.

En abuser. Utiliser un CTE là où une simple sous-requête suffirait peut rendre le code confus.

Oublier les index. Si les données du CTE viennent de grosses tables sans index, la requête sera lente.