Sous-requêtes SQL : IN, EXISTS, NOT EXISTS

Une sous-requête SQL est un SELECT entre parenthèses, placé à l'intérieur d'une autre requête. Elle répond à une question intermédiaire : « quel est le prix moyen ? », « quels employés ont un poste ? ». Ce chapitre présente les sous-requêtes scalaires, IN et NOT IN, EXISTS et NOT EXISTS, les sous-requêtes corrélées et les tables dérivées, sur la base d'exemple parc.

Sous-requête scalaire

Une sous-requête scalaire renvoie une seule valeur (une ligne, une colonne). Elle s'utilise partout où une valeur est attendue. Les postes plus chers que la moyenne du parc (901 €) :

SELECT num_inventaire, prix_achat
FROM poste
WHERE prix_achat > (SELECT AVG(prix_achat) FROM poste)
ORDER BY prix_achat DESC, num_inventaire;

Elle renvoie les 8 portables, de PC-0012 (1 249 €) à PC-0005 (1 029 €). Dans le SELECT, la même sous-requête calcule un écart :

SELECT num_inventaire, prix_achat,
       ROUND(prix_achat - (SELECT AVG(prix_achat) FROM poste), 2) AS ecart_moyenne
FROM poste
WHERE id_poste IN (1, 2, 12)
ORDER BY id_poste;

Si la sous-requête renvoie plusieurs lignes là où une valeur est attendue, MySQL lève l'erreur 1242, Subquery returns more than 1 row. Si elle n'en renvoie aucune, elle vaut NULL.

IN et NOT IN

Avec IN, la sous-requête renvoie une liste de valeurs (une colonne, plusieurs lignes). Les employés qui ont ouvert au moins un ticket :

SELECT nom, prenom
FROM employe
WHERE id_employe IN (SELECT id_demandeur FROM ticket)
ORDER BY nom;

Elle renvoie 10 employés : tous sauf Inès Bonnet et les trois membres du service informatique. Un employé qui a ouvert deux tickets n'apparaît qu'une fois : IN teste l'appartenance, il ne multiplie pas les lignes comme une jointure.

NOT IN cache le piège le plus célèbre de SQL. Les postes qui n'ont fait l'objet d'aucun ticket :

SELECT num_inventaire
FROM poste
WHERE id_poste NOT IN (SELECT id_poste FROM ticket);

Résultat : aucune ligne. Pourtant PC-0003 n'a aucun ticket. La sous-requête renvoie la liste des id_poste des tickets, et cette liste contient des NULL (les tickets 2, 4, 7 et 13). Pour PC-0003, MySQL évalue 3 NOT IN (1, NULL, 5, …), c'est-à-dire 3 <> 1 AND 3 <> NULL AND …. Le terme 3 <> NULL vaut UNKNOWN, donc toute la condition vaut UNKNOWN, et la ligne est rejetée. Il en va de même pour tous les postes.

SELECT num_inventaire
FROM poste
WHERE id_poste NOT IN (SELECT id_poste FROM ticket WHERE id_poste IS NOT NULL)
ORDER BY num_inventaire;

ANY, SOME et ALL

ANY (ou son synonyme SOME) et ALL comparent une valeur à chaque valeur d'une liste :

• x > ALL (sous-requête) : x est plus grand que toutes les valeurs ;
• x > ANY (sous-requête) : x est plus grand qu'au moins une valeur ;
• x = ANY (sous-requête) équivaut à x IN (sous-requête), et x <> ALL (…) à x NOT IN (…), avec le même piège des NULL.

Les postes plus chers que tous les postes HP :

SELECT num_inventaire, marque, prix_achat
FROM poste
WHERE prix_achat > ALL (SELECT prix_achat FROM poste WHERE marque = 'HP')
ORDER BY prix_achat DESC, num_inventaire;

Elle renvoie les 6 postes à plus de 1 029 € (le HP le plus cher) : les quatre Lenovo et les deux Dell Latitude. > ALL (…) équivaut ici à > (SELECT MAX(prix_achat) …), écriture souvent plus lisible. Attention : si la sous-requête ne renvoie aucune ligne, > ALL est vrai pour toutes les lignes.

EXISTS et NOT EXISTS

EXISTS (sous-requête) est vrai si la sous-requête renvoie au moins une ligne. Ce qu'elle sélectionne n'a pas d'importance : on écrit SELECT 1 par convention. La sous-requête fait référence à la ligne en cours de la requête principale : c'est ce qui la relie.

SELECT p.num_inventaire
FROM poste p
WHERE NOT EXISTS (SELECT 1 FROM ticket t WHERE t.id_poste = p.id_poste)
ORDER BY p.num_inventaire;

Elle renvoie les 7 bons postes, de PC-0003 à PC-0015. Pour chaque poste, la question est « existe-t-il un ticket qui le concerne ? ». Les tickets dont id_poste est NULL ne vérifient jamais t.id_poste = p.id_poste, ils ne gênent pas.

Les employés sans poste, version NOT EXISTS :

SELECT e.nom, e.prenom
FROM employe e
WHERE NOT EXISTS (SELECT 1 FROM poste p WHERE p.id_employe = e.id_employe)
ORDER BY e.nom;

Elle renvoie Inès Bonnet et Paul Martin, comme l'anti-jointure avec LEFT JOIN.

Sous-requêtes corrélées

Une sous-requête est corrélée quand elle utilise une colonne de la requête principale (e.id_employe dans la requête des employés sans poste). Conceptuellement, elle est réévaluée pour chaque ligne de la requête principale. La plus longue intervention de chaque technicien :

SELECT i.id_technicien, i.id_ticket, i.duree_min
FROM intervention i
WHERE i.duree_min = (SELECT MAX(i2.duree_min)
                     FROM intervention i2
                     WHERE i2.id_technicien = i.id_technicien)
ORDER BY i.id_technicien;

Pour chaque intervention i, la sous-requête calcule la durée maximale du même technicien ; la ligne est gardée si elle atteint ce maximum. Les trois techniciens ont tous passé leur plus longue intervention sur le ticket 9, le rançongiciel. Les alias i et i2 sont indispensables : sans eux, id_technicien = id_technicien serait toujours vrai.

Tables dérivées

Une sous-requête dans le FROM est une table dérivée : son résultat sert de table à la requête principale. Elle doit avoir un alias (sinon, erreur 1248, Every derived table must have its own alias). La durée moyenne de traitement par ticket demande deux niveaux d'agrégation : la somme par ticket, puis la moyenne de ces sommes.

SELECT ROUND(AVG(total), 1) AS moyenne_par_ticket
FROM (SELECT id_ticket, SUM(duree_min) AS total
      FROM intervention
      GROUP BY id_ticket) AS par_ticket;

Les 11 tickets traités ont demandé 1 255 minutes, soit 114,1 minutes en moyenne. Les tables dérivées résolvent aussi le problème des doublons après jointure : on agrège chaque table enfant dans sa propre table dérivée, puis on joint des résultats qui ont une ligne par employé.

La division : « pour tous »

« Quels postes ont tous les logiciels de la catégorie gestion ? » Ce type de question, avec « tous » ou « chaque », s'appelle une division relationnelle. SQL n'a pas d'opérateur « pour tout » ; on le reformule avec une double négation : un poste convient s'il n'existe pas de logiciel de gestion qui ne soit pas installé sur lui.

SELECT p.num_inventaire
FROM poste p
WHERE NOT EXISTS (
  SELECT 1
  FROM logiciel l
  WHERE l.categorie = 'gestion'
    AND NOT EXISTS (
      SELECT 1
      FROM installation i
      WHERE i.id_poste = p.id_poste
        AND i.id_logiciel = l.id_logiciel
    )
);

Seul le poste de Karim Morel a Sage et Odoo. Une autre écriture compte : un poste convient si son nombre de logiciels de gestion est égal au nombre total de logiciels de gestion.

SELECT p.num_inventaire
FROM poste p
JOIN installation i ON i.id_poste    = p.id_poste
JOIN logiciel l     ON l.id_logiciel = i.id_logiciel
WHERE l.categorie = 'gestion'
GROUP BY p.id_poste, p.num_inventaire
HAVING COUNT(*) = (SELECT COUNT(*) FROM logiciel WHERE categorie = 'gestion');

Les deux versions diffèrent dans un cas limite : si la catégorie ne contient aucun logiciel, la double négation renvoie tous les postes (« pour tout élément d'un ensemble vide » est vrai), la version par comptage n'en renvoie aucun. Choisis selon ce que l'énoncé attend.

Sous-requête ou jointure ?

MySQL 8 transforme lui-même beaucoup de sous-requêtes IN et EXISTS en jointures (semi-jointures et anti-jointures) : choisis l'écriture la plus lisible, puis vérifie le plan avec EXPLAIN si la requête est lente.

Exercices

SELECT id_ticket, titre
FROM ticket
WHERE id_demandeur IN (
  SELECT id_employe
  FROM employe
  WHERE id_service = (SELECT id_service FROM service WHERE code = 'COMM')
)
ORDER BY id_ticket;

SELECT p.num_inventaire, p.marque, p.prix_achat
FROM poste p
WHERE p.prix_achat > (SELECT AVG(p2.prix_achat)
                      FROM poste p2
                      WHERE p2.marque = p.marque)
ORDER BY p.marque, p.num_inventaire;

SELECT id_technicien, COUNT(*) AS nb
FROM intervention
GROUP BY id_technicien
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM intervention GROUP BY id_technicien);

SELECT e.nom
FROM employe e
WHERE NOT EXISTS (
  SELECT 1
  FROM ticket t
  WHERE t.priorite = 'critique'
    AND NOT EXISTS (
      SELECT 1
      FROM intervention i
      WHERE i.id_ticket = t.id_ticket
        AND i.id_technicien = e.id_employe
    )
)
ORDER BY e.nom;

SELECT nom FROM employe
WHERE id_employe NOT IN (SELECT id_responsable FROM employe);

-- Correction 1 : écarter les NULL de la sous-requête
SELECT nom FROM employe
WHERE id_employe NOT IN (SELECT id_responsable FROM employe
                         WHERE id_responsable IS NOT NULL)
ORDER BY nom;

-- Correction 2 : NOT EXISTS
SELECT e.nom FROM employe e
WHERE NOT EXISTS (SELECT 1 FROM employe sub WHERE sub.id_responsable = e.id_employe)
ORDER BY e.nom;