Clause WITH — étager ta requête avec des résultats intermédiaires nommés

Données utilisées dans cet article — employee et department

Un CTE (Common Table Expression) est un mécanisme où tu écris WITH nom AS (SELECT ...) pour donner un nom à une sous-requête, puis tu références ce nom dans les requêtes qui suivent.

Comme tu peux découper une sous-requête profondément imbriquée en étapes, ton code devient bien plus lisible.

Le matériel est la table employee (30 lignes ; la colonne manager_id pointe vers l'emp_id du manager, une structure auto-référencée) et la table department (6 lignes).

Tu vas faire des exercices qui utilisent des CTE pour écrire des agrégats multi-étapes de façon plus lisible.

Avant de commencer les exercices, regarde les définitions de colonnes et les données d'exemple des deux tables que cet article utilise — employee et department.

① Exécute PRAGMA table_info(employee); et PRAGMA table_info(department); pour vérifier les définitions de colonnes des deux tables.

② Exécute SELECT * FROM employee LIMIT 5; et SELECT * FROM department LIMIT 5; pour prévisualiser les 5 premières lignes de chacune. Regarde aussi comment NULL apparaît dans la colonne manager_id (les employés qui n'ont pas de manager).

Construire un résultat intermédiaire nommé avec WITH — rendre les requêtes multi-étapes lisibles

Quand tu écris WITH nom AS (SELECT ...), le résultat du SELECT interne reçoit un nom, et tu peux le référencer depuis FROM ou JOIN dans la requête principale qui suit.

Au lieu d'écrire le processus en deux étapes « d'abord construire un résultat intermédiaire, puis l'utiliser pour produire le résultat final » sous forme de sous-requête imbriquée, tu peux le décomposer dans une forme qui se lit de haut en bas.

Un CTE est un nom temporaire qui disparaît une fois la requête principale terminée — il ne crée pas de vraie table.

La syntaxe est WITH nom AS ( ... ) SELECT ... FROM nom ...;.

-- Regrouper les hauts salaires dans le CTE high_earner,
-- puis joindre avec department pour attacher le nom du service
WITH high_earner AS (
  SELECT emp_id, name, dept_id, salary
  FROM employee
  WHERE salary >= 7000000
)
SELECT h.name, h.salary, d.dept_name
FROM high_earner h
LEFT JOIN department d ON h.dept_id = d.dept_id
ORDER BY h.salary DESC;

Dans le code ci-dessus, le nom high_earner découpe le résultat intermédiaire « les employés dont salary >= 7 000 000 », et la requête principale le référence comme une table avec FROM high_earner.

Voici à quoi ressemble ce flux sous forme de schéma.

Tu peux écrire le même traitement avec une sous-requête (un SELECT imbriqué dans les parenthèses d'un autre SELECT).

Reconstruisons la version WITH précédente sous forme de sous-requête au lieu d'un CTE.

-- Le même traitement écrit comme sous-requête (table dérivée)
-- Un SELECT vit dans les parenthèses du FROM, donc tu lis externe → interne → externe
SELECT h.name, h.salary, d.dept_name
FROM (
  SELECT emp_id, name, dept_id, salary
  FROM employee
  WHERE salary >= 7000000
) AS h
LEFT JOIN department d ON h.dept_id = d.dept_id
ORDER BY h.salary DESC;

Le résultat est exactement le même qu'avec la version WITH, mais avec la version sous-requête tu dois plonger dans un SELECT imbriqué à l'intérieur de FROM pour comprendre ce que fait le SELECT interne.

La version WITH découpe le résultat intermédiaire avec le nom `high_earner`, donc les deux étapes « ① filtrer les hauts salaires → ② attacher le nom du service » se lisent directement de haut en bas.

L'écart grandit à mesure que tu ajoutes des étapes intermédiaires — quand tu en as 3 ou 4, les sous-requêtes profondément imbriquées deviennent difficiles à suivre.

Besoin : « Je veux découper les employés qui n'ont pas de manager (cadres dirigeants avec manager_id = NULL) et les lister avec leur nom de service. » Résous-le en deux étapes avec un CTE. (Si tu l'exécutes correctement, l'explication apparaîtra.)

① Avec WITH, définis un CTE nommé executive qui récupère depuis employee les lignes où manager_id est NULL (emp_id, name, dept_id).

② Fais un LEFT JOIN entre ce CTE et department sur dept_id, et renvoie name, dept_id, dept_name.

③ Trie le résultat par name croissant.

Chaîner plusieurs clauses WITH — découper un agrégat multi-étapes une étape à la fois

Après WITH, tu peux lister plusieurs CTE séparés par des virgules.

La forme est WITH a AS (...), b AS (...) SELECT ..., et un CTE plus tardif peut référencer un précédent.

Cela te permet de découper un agrégat multi-étapes (« calculer la moyenne par service » → « sélectionner les employés au-dessus de cette moyenne » → « attacher le nom du service ») en étapes nommées une à la fois, pour que même dans une longue requête, le rôle de chaque étape reste séparé.

-- ① dept_avg : salaire moyen par service
-- ② top_earner : employés au-dessus de la moyenne de leur propre service
-- ③ Requête principale : attacher le nom du service et sortir
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
),
top_earner AS (
  SELECT e.name, e.dept_id, e.salary
  FROM employee e
  JOIN dept_avg da ON e.dept_id = da.dept_id
  WHERE e.salary > da.avg_salary
)
SELECT d.dept_name, t.name, t.salary
FROM top_earner t
JOIN department d ON t.dept_id = d.dept_id
ORDER BY d.dept_name, t.salary DESC;

Besoin : « Je veux calculer le salaire moyen de chaque service, puis lister uniquement les employés dont le salaire dépasse cette moyenne, avec leur nom de service attaché. » Résous-le en enchaînant deux CTE.

① Pour le premier CTE, agrège employee par dept_id et produis le salaire moyen par service. Exclus les employés dont dept_id est NULL de la moyenne.

② Pour le deuxième CTE, joins employee avec le premier CTE sur dept_id et sélectionne les employés (name, dept_id, salary) dont le salary est supérieur à la moyenne de ce service.

③ Dans la requête principale, joins le deuxième CTE avec department sur dept_id et renvoie dept_name, name, salary. Trie par nom de service croissant, et au sein d'un même service par salaire décroissant.