UNION, CTE et fonctions de fenêtrage

UNION, les CTE et les fonctions de fenêtrage sont trois outils de SQL qui prolongent les agrégats et les sous-requêtes. UNION, INTERSECT et EXCEPT combinent les résultats de deux requêtes ou plus. Une CTE, introduite par WITH, nomme une sous-requête pour la lire comme une table. Une fonction de fenêtrage (ROW_NUMBER, RANK, LAG) calcule sur un groupe de lignes sans les fusionner. Les exemples interrogent la base d'exemple parc.

UNION et UNION ALL

UNION met bout à bout les résultats de deux requêtes. Les deux requêtes doivent avoir le même nombre de colonnes, de types compatibles ; les noms de colonnes du résultat sont ceux de la première. Un ORDER BY final trie l'ensemble.

La chronologie complète du ticket 9 réunit des lignes venues de deux tables :

SELECT date_ouverture AS moment, 'ouverture' AS evenement
FROM ticket WHERE id_ticket = 9
UNION ALL
SELECT date_intervention, 'intervention'
FROM intervention WHERE id_ticket = 9
UNION ALL
SELECT date_cloture, 'clôture'
FROM ticket WHERE id_ticket = 9
ORDER BY moment;

UNION supprime les lignes en double du résultat, UNION ALL les garde. SELECT id_demandeur FROM ticket UNION SELECT id_technicien FROM intervention renvoie les 13 employés qui ont participé à au moins un ticket, chacun une fois ; avec UNION ALL, on obtient 31 lignes (14 tickets + 17 interventions).

Les sujets d'E7 SLAM utilisent souvent UNION dans une vue qui rassemble des données de structures différentes (des comptes venus de plusieurs applications, par exemple). Quand une source n'a pas une colonne, on la remplace par NULL ou une constante, pour garder le même nombre de colonnes. Tu en écriras une dans le chapitre Vues SQL.

INTERSECT et EXCEPT

MySQL 8.0.31 et MariaDB 10.3 ont ajouté les deux autres opérations ensemblistes :

• A INTERSECT B : les lignes présentes dans A et dans B ;
• A EXCEPT B : les lignes de A absentes de B (Oracle l'appelle MINUS).

-- Les employés qui n'ont jamais ouvert de ticket
SELECT id_employe FROM employe
EXCEPT
SELECT id_demandeur FROM ticket
ORDER BY id_employe;

SELECT id_employe FROM poste INTERSECT SELECT id_demandeur FROM ticket renvoie les 9 employés qui ont un poste et ont ouvert un ticket. Comme UNION, ces opérateurs suppriment les doublons ; INTERSECT ALL et EXCEPT ALL les gardent.

Ce sont les opérations de la théorie des ensembles étudiée en mathématiques (E3) : UNION est la réunion A ∪ B, INTERSECT l'intersection A ∩ B, EXCEPT la différence A \ B. La nuance : une table SQL peut contenir des doublons (un multiensemble), d'où l'existence des variantes ALL.

Les CTE avec WITH

Une CTE (common table expression) nomme une sous-requête avant la requête principale, avec WITH. Elle se lit de haut en bas, comme une suite d'étapes, et peut servir plusieurs fois. MySQL l'accepte depuis la 8.0, MariaDB depuis la 10.2.

Les services qui ont ouvert plus de tickets que la moyenne des services :

WITH tickets_par_service AS (
  SELECT e.id_service, COUNT(*) AS nb_tickets
  FROM ticket t
  JOIN employe e ON e.id_employe = t.id_demandeur
  GROUP BY e.id_service
)
SELECT s.libelle, tps.nb_tickets
FROM tickets_par_service tps
JOIN service s ON s.id_service = tps.id_service
WHERE tps.nb_tickets > (SELECT AVG(nb_tickets) FROM tickets_par_service)
ORDER BY tps.nb_tickets DESC, s.libelle;

La CTE est utilisée deux fois : comme table principale et dans la sous-requête de la moyenne (14 tickets pour 4 services, soit 3,5). Cette moyenne ne compte que les services qui ont au moins un ticket, car la jointure interne écarte les autres. Pour compter l'Informatique et le Juridique avec 0, pars de service avec des LEFT JOIN : la moyenne tombe à 14 / 6, environ 2,33, et le résultat reste le même ici. Pose-toi la question avant d'écrire : « la moyenne des services », de quels services ? Avec des tables dérivées, il aurait fallu écrire deux fois le même calcul. Plusieurs CTE se séparent par des virgules : WITH a AS (...), b AS (...) SELECT ..., et b peut utiliser a.

CTE récursive

Une CTE récursive se référence elle-même. Elle parcourt une hiérarchie de profondeur inconnue, ce qu'une auto-jointure ne fait que niveau par niveau. Elle a deux parties reliées par UNION ALL :

• la partie initiale donne les premières lignes (ici, la directrice, qui n'a pas de responsable) ;
• la partie récursive joint la table aux lignes déjà trouvées (ici, les employés dont le responsable est dans le résultat). Elle s'arrête quand elle ne produit plus de ligne.

WITH RECURSIVE organigramme AS (
  SELECT id_employe, nom, 1 AS niveau,
         CAST(nom AS CHAR(200)) AS chemin
  FROM employe
  WHERE id_responsable IS NULL
  UNION ALL
  SELECT e.id_employe, e.nom, o.niveau + 1,
         CONCAT(o.chemin, ' > ', e.nom)
  FROM employe e
  JOIN organigramme o ON e.id_responsable = o.id_employe
)
SELECT niveau, nom, chemin
FROM organigramme
ORDER BY chemin;

MySQL arrête une récursion qui dépasse 1 000 niveaux (variable cte_max_recursion_depth) : c'est une protection contre une hiérarchie qui boucle (A responsable de B, B responsable de A).

Une CTE récursive sait aussi générer des lignes. Le nombre de tickets ouverts chaque jour de la semaine du 19 octobre, y compris les jours sans ticket :

WITH RECURSIVE jours AS (
  SELECT CAST('2026-10-19' AS DATE) AS jour
  UNION ALL
  SELECT DATE_ADD(jour, INTERVAL 1 DAY) FROM jours WHERE jour < '2026-10-25'
)
SELECT j.jour, COUNT(t.id_ticket) AS tickets_ouverts
FROM jours j
LEFT JOIN ticket t ON DATE(t.date_ouverture) = j.jour
GROUP BY j.jour
ORDER BY j.jour;

Sans la série de jours, un simple GROUP BY DATE(date_ouverture) n'afficherait que les jours où un ticket a été ouvert.

Fonctions de fenêtrage : numéroter et classer

Une fonction de fenêtrage calcule une valeur à partir d'un ensemble de lignes liées à la ligne courante, sans les fusionner : contrairement à GROUP BY, chaque ligne reste dans le résultat. On la reconnaît à la clause OVER. MySQL les propose depuis la 8.0, MariaDB depuis la 10.2.

• PARTITION BY découpe les lignes en groupes indépendants (facultatif) ;
• ORDER BY fixe l'ordre à l'intérieur de chaque groupe.

Trois fonctions numérotent les lignes. Elles ne diffèrent qu'en cas d'égalité :

SELECT num_inventaire, prix_achat,
       ROW_NUMBER() OVER (ORDER BY prix_achat DESC, num_inventaire) AS num_ligne,
       RANK()       OVER (ORDER BY prix_achat DESC) AS rang,
       DENSE_RANK() OVER (ORDER BY prix_achat DESC) AS rang_dense
FROM poste
WHERE marque = 'Lenovo'
ORDER BY prix_achat DESC, num_inventaire;

ROW_NUMBER numérote sans jamais répéter. Entre deux ex æquo, l'ordre n'est pas garanti : d'où le second critère num_inventaire, qui rend la numérotation stable. RANK donne le même rang aux ex æquo puis saute des rangs, comme un classement sportif ; DENSE_RANK ne saute pas de rang.

L'usage le plus fréquent : garder la première ligne de chaque groupe. La dernière intervention de chaque ticket, avec son compte rendu (ce que MAX seul ne sait pas faire) :

WITH numerotees AS (
  SELECT id_ticket, date_intervention, id_technicien, compte_rendu,
         ROW_NUMBER() OVER (PARTITION BY id_ticket
                            ORDER BY date_intervention DESC) AS rn
  FROM intervention
)
SELECT id_ticket, date_intervention, id_technicien, compte_rendu
FROM numerotees
WHERE rn = 1
ORDER BY id_ticket;

Elle renvoie une ligne pour chacun des 11 tickets qui ont au moins une intervention ; pour le ticket 9, c'est la restauration des données par Chloé Lambert, le 6 octobre.

Cumuls, LAG et LEAD

Avec une fonction d'agrégation suivie de OVER, tu obtiens un total glissant. Le temps cumulé de Chloé Lambert, intervention après intervention :

SELECT date_intervention, duree_min,
       SUM(duree_min) OVER (ORDER BY date_intervention) AS cumul
FROM intervention
WHERE id_technicien = 11
ORDER BY date_intervention;

Avec ORDER BY dans OVER, la fenêtre par défaut va du début du groupe à la ligne courante, lignes de même valeur de tri comprises. Pour un cumul strictement ligne par ligne malgré des égalités, précise ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Sans ORDER BY, SUM(...) OVER () donne le total de toutes les lignes, pratique pour calculer une part :

SELECT id_technicien,
       SUM(duree_min) AS minutes,
       RANK() OVER (ORDER BY SUM(duree_min) DESC) AS classement,
       ROUND(100 * SUM(duree_min) / SUM(SUM(duree_min)) OVER (), 1) AS part_pct
FROM intervention
GROUP BY id_technicien
ORDER BY classement;

Les fenêtres s'appliquent après le GROUP BY : SUM(SUM(duree_min)) OVER () additionne les totaux des trois groupes.

LAG(colonne) lit la valeur de la ligne précédente dans la fenêtre, LEAD(colonne) celle de la ligne suivante. Le délai entre deux tickets sur le même poste repère les pannes qui reviennent :

SELECT id_poste, id_ticket, date_ouverture,
       DATEDIFF(date_ouverture,
                LAG(date_ouverture) OVER (PARTITION BY id_poste
                                          ORDER BY date_ouverture)) AS jours_depuis_precedent
FROM ticket
WHERE id_poste IN (1, 4)
ORDER BY id_poste, date_ouverture;

Le premier ticket de chaque poste n'a pas de précédent : LAG renvoie NULL. Le poste PC-0001 a eu deux fois un écran noir à 40 jours d'écart : une piste pour le service informatique.

Exercices

SELECT 'demandeur' AS role, e.nom
FROM ticket t
JOIN employe e ON e.id_employe = t.id_demandeur
WHERE t.id_ticket = 9
UNION
SELECT 'technicien', e.nom
FROM intervention i
JOIN employe e ON e.id_employe = i.id_technicien
WHERE i.id_ticket = 9
ORDER BY role, nom;

SELECT id_service FROM service
EXCEPT
SELECT id_service FROM employe;

SELECT s.id_service FROM service s
WHERE NOT EXISTS (SELECT 1 FROM employe e WHERE e.id_service = s.id_service);

WITH RECURSIVE equipe AS (
  SELECT id_employe, nom FROM employe WHERE id_employe = 7
  UNION ALL
  SELECT e.id_employe, e.nom
  FROM employe e
  JOIN equipe q ON e.id_responsable = q.id_employe
)
SELECT nom FROM equipe
WHERE id_employe <> 7
ORDER BY nom;

SELECT date_intervention,
       ROW_NUMBER() OVER (ORDER BY date_intervention) AS n,
       SUM(duree_min) OVER (ORDER BY date_intervention) AS cumul
FROM intervention
WHERE id_technicien = 10
ORDER BY date_intervention;

WITH premieres AS (
  SELECT i.id_ticket, i.date_intervention,
         ROW_NUMBER() OVER (PARTITION BY i.id_ticket ORDER BY i.date_intervention) AS rn
  FROM intervention i
)
SELECT t.id_ticket,
       TIMESTAMPDIFF(MINUTE, t.date_ouverture, p.date_intervention) AS delai_min
FROM ticket t
JOIN premieres p ON p.id_ticket = t.id_ticket AND p.rn = 1
ORDER BY delai_min DESC, t.id_ticket;