Des requêtes dans des requêtes : les sous-requêtes

Une sous-requête qui renvoie une valeur

Partout où une valeur unique pourrait aller, une requête renvoyant exactement une ligne et une colonne peut aller à la place.

SELECT name,
       price,
       ROUND((SELECT AVG(price) FROM products), 2) AS average_price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC
LIMIT 20;

(SELECT AVG(price) FROM products) produit un nombre, et le reste de la requête le traite comme si vous aviez tapé ce nombre. C’est la réponse à « pourquoi ne puis-je pas écrire WHERE price > AVG(price) » — un agrégat résume un groupe, et pendant l’exécution de WHERE il n’y a pas encore de groupes. La sous-requête contourne cela en étant une requête complète, terminée avant que l’externe ne filtre.

Une sous-requête qui renvoie une liste

SELECT first_name, last_name, email, country
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE status = 'refunded'
)
ORDER BY last_name
LIMIT 20;

La requête interne fournit une colonne d’identifiants de clients ; IN teste l’appartenance. Lisez de l’intérieur vers l’extérieur : « les clients dont l’identifiant figure parmi les identifiants clients des commandes remboursées ».

EXISTS : demander si quelque chose correspond

SELECT c.first_name, c.last_name, c.country
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.total > 1000
)
ORDER BY c.last_name
LIMIT 20;

EXISTS prend une requête et vaut vrai si elle renvoie au moins une ligne. Le SELECT 1 est une convention : personne ne regarde la valeur sélectionnée, seulement si une ligne est revenue, il n’y a donc aucun intérêt à sélectionner de vraies colonnes.

Cette sous-requête mentionne c.id de la requête externe, ce qui la rend corrélée — une question différente pour chaque client, plutôt qu’une liste calculée une seule fois. Pour « existe-t-il une ligne liée », c’est en général la forme la plus claire et celle que les moteurs optimisent le mieux.

Le piège de NOT IN

110 des 120 employés n’ont personne sous leurs ordres. Cette requête les cherche :

-- Employees nobody reports to. 110 of the 120 qualify,
-- and this query finds none of them.
SELECT first_name, last_name, position
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

Zéro ligne. Aucune erreur.

manager_id peut être nul, et les dix personnes en haut de l’organigramme y ont NULL. La requête interne renvoie donc une liste contenant un NULL, et x NOT IN (1, 2, NULL) demande : « x est-il différent de 1, et de 2, et de cette valeur inconnue ? ». La dernière partie est inconnue, donc toute la condition est inconnue, donc aucune ligne n’est jamais conservée — pour tout x. Un seul NULL quelque part dans la liste vide le résultat.

IN n’est pas affecté de la même façon : une ligne qui correspond à une vraie valeur est conservée, quoi qu’il y ait d’autre dans la liste. Seule la forme niée exige que toutes les comparaisons soient vraies, et c’est pourquoi un seul inconnu suffit à tout vider.

La correction, c’est soit WHERE manager_id IS NOT NULL dans la sous-requête, soit — mieux, parce qu’on ne peut pas l’oublier — NOT EXISTS :

SELECT e.first_name, e.last_name, e.position
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM employees m
  WHERE m.manager_id = e.id
)
ORDER BY e.last_name
LIMIT 20;

NOT EXISTS interroge des lignes, pas des valeurs : NULL n’entre donc jamais dans une comparaison. Prendre NOT EXISTS par défaut supprime toute une catégorie de réponses fausses et silencieuses.

Une sous-requête comme table

Une requête qui renvoie plusieurs lignes et colonnes peut se placer là où irait une table, dans FROM. Une telle requête s’appelle une table dérivée.

SELECT category, products, average
FROM (
  SELECT cat.name AS category,
         COUNT(*) AS products,
         ROUND(AVG(p.price), 2) AS average
  FROM products p
  JOIN categories cat ON cat.id = p.category_id
  GROUP BY cat.id
)
WHERE products >= 10
ORDER BY average DESC;

C’est l’autre réponse à « je veux filtrer sur un agrégat » : regrouper dans la requête interne, puis filtrer le résultat comme une table ordinaire. Cela fait le même travail que HAVING, et cela permet en plus de filtrer sur un alias, ce que HAVING ne peut pas faire de manière portable.

Les tables dérivées s’imbriquent, et c’est là qu’elles deviennent difficiles à suivre : à trois niveaux, la requête se lit de l’intérieur vers l’extérieur. La prochaine leçon les remplace par une écriture qui se lit de haut en bas.

Une sous-requête par ligne

SELECT p.name,
       p.price,
       (SELECT COUNT(*) FROM reviews r WHERE r.product_id = p.id) AS reviews,
       (SELECT ROUND(AVG(r.rating), 2) FROM reviews r WHERE r.product_id = p.id) AS avg_rating
FROM products p
ORDER BY avg_rating DESC, reviews DESC
LIMIT 20;

Ce que vous savez faire maintenant

Tout, sauf rendre lisibles les requêtes compliquées, ce que couvre la prochaine leçon.