Découverte des CTE : WITH

En programmation, tu peux sortir un bout de code à part et lui donner un nom — genre créer une fonction. Bah, avec les CTE, c'est pareil. Tu peux sortir un SELECT-sous-requête à part du gros SELECT, lui filer un nom, et ensuite t'en servir dans ta requête SQL principale.

CTE (Common Table Expressions, ou expressions de table communes) — c'est un peu comme une bouffée d'air frais pour le dev qui en a marre des sous-requêtes imbriquées. Ça rend le code SQL non seulement plus lisible, mais carrément élégant. Si avant tu te battais avec des constructions énormes de sous-requêtes et que t'avais les yeux qui piquaient — il est temps de découvrir la "magie" des CTE.

Imagine que tu construis une maison. Souvent, t'as envie de poser les fenêtres, visser les portes (genre balancer des sous-requêtes direct), même si les murs sont pas prêts. Avec les CTE, c'est différent : d'abord tu fais un brouillon propre — tu crées une table temporaire, comme si tu faisais le plan de la maison. Et ensuite, étape par étape, tu construis les étages de ta requête. Stylé, solide, technique.

En gros, un CTE, c'est une table virtuelle que tu crées à la volée avec un SELECT. C'est un peu comme une sous-requête, mais en mieux. Si en prog tu peux sortir un bout de logique dans une fonction avec un nom clair, en SQL c'est le CTE qui prend ce rôle. Tu fais un SELECT, tu lui donnes un nom — et tu t'en sers après comme d'une partie d'une grosse requête complexe. Classe, non ? Grave.

Exemple de requête SQL avec une sous-requête :

-- requête principale
SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 75
) AS filtered_students; -- sous-requête qui a reçu le pseudo filtered_students

On a sorti la sous-requête à part :

-- CTE/sous-requête qui a reçu le pseudo filtered_students
WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 75
)

-- requête principale
SELECT *
FROM filtered_students;

Incroyable mais vrai, les sous-requêtes existaient 20 ans avant les CTE ! Dans le standard SQL-89, y'avait déjà les sous-requêtes, alors que les CTE sont arrivés seulement avec SQL-2009.

Syntaxe de WITH

Un CTE commence par le mot-clé WITH et ça ressemble à ça :

WITH cte_name AS (
    SELECT ... -- ta requête ici
)

SELECT ...
FROM cte_name;

Ici :

• cte_name — c'est le nom de ton CTE. Tu peux choisir n'importe quel nom qui a du sens, genre high_scores, filtered_data ou même best_students.
• Dans les parenthèses () tu mets la requête qui prépare les données pour la suite.
• Après avoir défini le CTE, tu peux t'en servir comme d'une table normale dans ta requête principale.

Exemple 1 : Un CTE simple

On va voir comment marche un CTE avec un exemple concret. Imagine qu'on a une table students — une liste d'étudiants avec leurs notes :

Notre but — sortir tous les étudiants qui ont une note au-dessus de 85 et afficher leurs infos.

Version sans CTE :

On peut faire ça avec une sous-requête :

SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 85
) AS filtered_students;

Et voilà la même chose, mais avec un CTE — c'est quand même plus agréable à lire :

WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 85
)
SELECT *
FROM filtered_students;

Avoue, c'est plus propre et plus clair. On a bien séparé la préparation des données (WITH) de la partie principale de la requête (SELECT). C'est comme ranger ton bureau avant de bosser — tout de suite, tu respires mieux.

Exemple 2 : Plusieurs CTE

Tu peux définir plusieurs CTE dans une seule requête. C'est super utile si tu dois préparer les données en plusieurs étapes.

On a : une table grades, où sont stockées les notes des étudiants par cours :

Objectif : pour chaque étudiant, trouver la moyenne de ses notes, puis sortir ceux qui ont une moyenne au-dessus de 85.

Solution avec plusieurs CTE :

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id, avg_grade
    FROM student_averages 	-- on utilise le premier CTE - student_averages
    WHERE avg_grade > 85
)

SELECT *
FROM high_achievers; -- on utilise le deuxième CTE - high_achievers

Ici :

1. student_averages prépare les données — les moyennes des étudiants.
2. high_achievers utilise ces données pour ne garder que ceux qui ont plus de 85.

Différence entre CTE et sous-requêtes

Spoiler : les CTE ne remplacent pas les sous-requêtes, mais parfois c'est carrément plus pratique.

Une sous-requête, c'est une requête dans une requête. C'est pratique pour aller vite, mais quand t'en as plein, ton code devient vite le bazar.

Exemple :

SELECT *
FROM (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
) AS student_averages
WHERE avg_grade > 85;

Les sous-requêtes peuvent être dans le SELECT, dans le FROM, dans le WHERE et dans le HAVING. En plus, elles peuvent utiliser les colonnes de la requête externe. Les CTE, là-dessus, c'est plus compliqué.

Par contre, les CTE rendent le code beaucoup plus lisible, donc plus facile à maintenir et avec moins de bugs. Au lieu d'imbriquer les requêtes, tu peux juste "nommer" le résultat d'une sous-requête et t'en servir après.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
)

SELECT *
FROM student_averages
WHERE avg_grade > 85;

Les CTE sont surtout utiles si tu dois utiliser les données préparées plusieurs fois dans la même requête.

Quand utiliser les CTE ?

• Quand tu veux découper une requête compliquée en plusieurs étapes logiques.
• Si tu veux que ta requête soit lisible et facile à maintenir. Personne n'a envie de se taper des structures imbriquées façon spaghetti.
• Pour préparer des données temporaires, utilisées juste dans la requête en cours.

Exemple final : analyse des cours

On va tout rassembler :

1. On trouve les étudiants avec une bonne moyenne.
2. On affiche leurs noms et les cours où ils sont inscrits.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id
    FROM student_averages
    WHERE avg_grade > 85
),
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, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Regarde comme tout est structuré :

1. D'abord, on prépare les moyennes.
2. Puis on garde que les meilleurs étudiants.
3. Ensuite, on les relie à leurs cours.

Maintenant, t'es officiellement prêt à utiliser les CTE pour faire des requêtes SQL belles, lisibles et puissantes.

Allez, à toi de jouer sur tes propres projets !