Avancé · 25 min

CTE (WITH) : declarer des sous-requêtes nommées

Quand une sous-requête est complexe ou reuse plusieurs fois, la CTE (Common Table Expression) permet de la nommer avec WITH et de l'utiliser comme une table temporaire dans la meme requete.

Contexte

Quand une sous-requête est complexe ou reuse plusieurs fois, la CTE (Common Table Expression) permet de la nommer avec WITH et de l'utiliser comme une table temporaire dans la meme requete. C'est plus lisible et parfois plus performant.

Explication

Une CTE se declare avec WITH avant la requete principale.

Syntaxe : WITH cte AS (SELECT ... FROM ...) SELECT ... FROM cte;

Points cles :

• La CTE existe seulement pour la requete suivante
• On peut definir plusieurs CTEs consecutives
• Elle peut etre reuse plusieurs fois dans la requete principale
• Utile pour simplifier les sous-requêtes complexes ou les recursive

Exemple guidé

Objectif : calculer les moyennes, puis filtrer les etudiants >= 12.

1. Definition de la CTE 'moyennes' qui calcule AVG(note) par etudiant 2. La requete principale selectionne dans la CTE et filtre

WITH moyennes AS (
  SELECT e.id, e.prenom, e.nom, ROUND(AVG(n.note), 2) AS moyenne
  FROM etudiants e
  INNER JOIN notes n ON n.etudiant_id = e.id
  GROUP BY e.id, e.prenom, e.nom
)
SELECT prenom, nom, moyenne
FROM moyennes
WHERE moyenne >= 12
ORDER BY moyenne DESC;

À retenir

WITH nome_la_cte AS (SELECT ...) = sous-requête nommee. La CTE est visible seulement dans la requete qui la suit. On peut en chainer plusieurs avec WITH cte1 AS (...), cte2 AS (...). Plus lisible qu'une sous-requête imbriquee complexe.

Exercices de cette leçon

• E76 — Moyennes par étudiant (CTE)
• E77 — Top 3 des étudiants
• E78 — Cours et leur effectif
• E79 — Moyenne des moyennes par matière
• E80 — Liste des étudiants par année d'inscription

Faire les exercices →