Sous-requêtes ① — scalaire, IN et agrégats dans WHERE

Données utilisées dans cet article — la table employee

Une sous-requête est une instruction SELECT écrite à l'intérieur d'une autre requête.

Tu peux d'abord calculer « le salaire moyen de tous les employés », puis utiliser ce résultat pour ne garder que « les employés qui gagnent plus que la moyenne » — une requête en plusieurs étapes écrite en une seule instruction.

Dans cet article, on va parcourir une par une les trois formes utilisées dans la clause WHERE — les sous-requêtes scalaires, les sous-requêtes IN, et les agrégats à l'intérieur de WHERE (sous-requêtes corrélées).

Avant les exercices, regardons les définitions de colonnes et un échantillon des données de la table employee.

① Exécute PRAGMA table_info(employee); pour vérifier les noms de colonnes, les types et la clé primaire.

② Exécute SELECT * FROM employee LIMIT 5; pour prévisualiser les 5 premières lignes. Note que certains employés ont NULL dans la colonne dept_id — on traitera ce point dans un exercice ultérieur.

Sous-requête scalaire — utiliser un résultat d'une ligne et une colonne comme valeur

Une sous-requête scalaire (une sous-requête dont le résultat fait exactement une ligne et une colonne) renvoie une seule valeur, donc tu peux l'écrire directement à droite d'un opérateur de comparaison comme = ou >.

(SELECT AVG(salary) FROM employee) renvoie une valeur — « le salaire moyen de tous les employés » — donc WHERE salary > (SELECT AVG(salary) FROM employee) ne garde que « les employés qui gagnent plus que la moyenne ».

Les points clés sont de l'entourer de parenthèses et de s'assurer qu'elle tient toujours dans une ligne et une colonne (renvoyer plusieurs lignes est une erreur).

Les fonctions d'agrégation comme AVG / MAX / MIN / COUNT renvoient une seule valeur, donc on les utilise souvent dans les sous-requêtes scalaires.

-- Sous-requête scalaire : récupérer les employés au salaire maximum
SELECT name, salary
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee);

Imagine le besoin « lister les employés dont le salaire est supérieur au salaire moyen de tous les employés ». (Exécute-le correctement et l'explication s'affiche.)

① Prends les 2 colonnes name et salary de la table employee.

② Dans la condition WHERE, ne garde que les lignes où salary est supérieur au salaire moyen de tous les employés. Calcule le salaire moyen avec une sous-requête scalaire.

③ Trie par salary en ordre décroissant.

Sous-requête IN — filtrer par des valeurs présentes dans une autre table

Quand une sous-requête renvoie plusieurs lignes sur une seule colonne, utilise IN au lieu de =.

Écrire WHERE colonne IN (SELECT autre_colonne FROM autre_table ...) ne garde que les lignes dont la valeur appartient à l'ensemble renvoyé par la sous-requête.

La sous-requête à droite de IN ne SELECT qu'une seule colonne (renvoyer plusieurs colonnes est une erreur).

Pour la nier, tu utilises NOT IN, mais il y a un piège : si des NULL se glissent du côté de la sous-requête, le résultat change (on couvre ce point à la fin de l'article).

-- Sous-requête IN : employés d'un service situé à Tokyo
SELECT name, dept_id
FROM employee
WHERE dept_id IN (
  SELECT dept_id FROM department WHERE location = 'Tokyo'
);

Imagine le besoin « lister les employés qui apparaissent au moins une fois dans les enregistrements de ventes (les employés qui ont réellement réalisé des ventes) ».

① Prends les 2 colonnes emp_id et name de la table employee.

② Construis l'ensemble des identifiants d'employés qui apparaissent dans la table sales avec une sous-requête, et utilise IN pour ne garder que les lignes où emp_id est dans cet ensemble.

③ Trie par emp_id en ordre croissant.

Agrégats dans WHERE — comparer aux moyennes de service avec une sous-requête corrélée

Avant les sous-requêtes corrélées, mettons au clair la structure à deux niveaux d'une sous-requête.

Il y a un SELECT principal qui extrait la liste des employés, et à l'intérieur, entre parenthèses, tu écris un autre SELECT (la sous-requête).

Les sous-requêtes vues jusqu'ici étaient des sous-requêtes non corrélées — calculées une seule fois au début, indépendamment du SELECT principal (par exemple, le salaire moyen de toute l'entreprise est la même valeur unique, quel que soit l'employé que tu regardes).

À l'inverse, une sous-requête qui utilise une colonne de l'employé que le SELECT principal est en train de traiter s'appelle une sous-requête corrélée (une sous-requête recalculée pour chaque employé).

Par exemple, pour lister « le mieux payé du service (l'employé dont le salaire est égal au maximum de son propre service) », le salaire maximum diffère d'un service à l'autre, donc pour chaque employé tu as besoin du salaire maximum de ce service uniquement.

Dans l'exemple ci-dessous, l'employé principal porte l'alias emp et le côté sous-requête dept_member, et WHERE dept_member.dept_id = emp.dept_id les corrèle.

Quand l'employé change, le dept_id transmis change aussi, et le résultat est recalculé.

-- Sous-requête corrélée : employés dont le salaire est égal au max de leur propre service (le mieux payé du service)
SELECT emp.name, emp.dept_id, emp.salary
FROM employee emp
WHERE emp.salary = (
    SELECT MAX(dept_member.salary)
    FROM employee dept_member
    WHERE dept_member.dept_id = emp.dept_id
  );

Imagine le besoin « au sein de chaque service, lister les employés dont le salaire est supérieur au salaire moyen de ce service ».

① Donne à la table employee l'alias emp et prends les 3 colonnes name, dept_id et salary.

② Limite-toi aux lignes où dept_id n'est pas NULL (les employés sans service sont hors périmètre).

③ Ne garde que les lignes où salary est supérieur à la moyenne salariale du même service que cet employé. Calcule la moyenne du service avec une sous-requête portant l'alias dept_member, corrélée par dept_member.dept_id = emp.dept_id.

④ Trie par dept_id croissant, et au sein d'un même service par salary décroissant.

Le piège de NOT IN et NULL

La négation de IN est NOT IN, mais si NULL est inclus dans la sous-requête à droite de NOT IN, le résultat change radicalement.

En effet, lorsque cela devient NOT IN (..., NULL), SQL vérifie « la valeur n'est égale à aucune d'elles » sous la forme valeur <> ... AND valeur <> NULL, mais valeur <> NULL ne se résout ni en vrai ni en faux (c'est NULL), donc le AND entier ne devient jamais vrai et aucune ligne ne survit.

Imagine le besoin « lister les noms des employés qui ne sont le manager (chef) de personne ». La colonne manager_id de employee contient des NULL pour les employés sans chef. (Exécute-le correctement et l'explication s'affiche.)

① Exécute le NOT IN tel quel dans l'éditeur et confirme que le résultat ressort à 0 ligne.

② Exclus les lignes où manager_id est NULL du côté de la sous-requête, et corrige-le pour que les employés qui ne sont le manager de personne ressortent correctement.

③ Trie par emp_id en ordre croissant.