Certaines questions nécessitent deux étapes pour répondre. « Quelles commandes sont supérieures à la moyenne ? » vous oblige à trouver d’abord la moyenne, puis à comparer chaque commande avec elle. Les sous-requêtes SQL vous permettent d’écrire les deux étapes dans une seule instruction.

Ce didacticiel couvre chaque type à l’aide de deux petits tableaux. Nous avons exécuté ces requêtes dans SQLite 3.50 et les résultats affichés proviennent de cette exécution. La syntaxe de sous-requête standard est largement prise en charge et nous soulignons les points où les bases de données diffèrent.

Qu’est-ce qu’une sous-requête ?

Une sous-requête, également appelée requête interne ou imbriquée, est une instruction SELECT placée à l’intérieur d’une autre instruction, entre parenthèses. Conceptuellement, la requête interne produit un résultat que la requête externe utilise ensuite.

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

Les exemples de tableaux

CREATE TABLE customers (id INT, name TEXT, city TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES
 (1,'Asha','Pune'), (2,'Ben','Delhi'), (3,'Chitra','Pune'), (4,'Dev','Goa');

INSERT INTO orders VALUES
 (101,1,1200), (102,1,800), (103,2,300), (104,3,2500), (105,3,100);

Notez que Dev n’a aucune commande. Ce détail est important pour plusieurs exemples ci-dessous.

Types de sous-requêtes en un coup d’œil

Sous-requêtes scalaires

Une sous-requête scalaire renvoie exactement une valeur, vous pouvez donc l’utiliser partout où une valeur unique a du sens. Voici la requête précédente, avec son résultat. La moyenne de toutes les commandes est de 980 :

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- 101 | 1200
-- 104 | 2500

Vous ne pouvez pas écrire WHERE total > AVG(total), car les agrégats ne sont pas autorisés directement dans WHERE. La sous-requête contourne ce problème en calculant la valeur séparément.

Que se passe-t-il si une sous-requête « scalaire » renvoie plusieurs lignes ? La plupart des bases de données, notamment PostgreSQL, MySQL et SQL Server, génèrent une erreur. SQLite ne le fait pas. Dans notre test, il n’a utilisé silencieusement que la première ligne, ce qui pourrait cacher un vrai bug, alors ne vous fiez pas à ce comportement.

DANS et PAS DANS

Lorsque la requête interne renvoie une liste, utilisez IN. Cela permet de trouver les clients avec au moins une commande supérieure à 1 000 :

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- Asha
-- Chitra

NOT IN fait le contraire, trouvant des clients qui n’ont jamais commandé :

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Dev

Cela fonctionne ici, mais NOT IN présente un cas limite dangereux, que nous abordons ci-dessous.

Sous-requêtes dans FROM

Une sous-requête dans FROM agit comme une table temporaire, souvent appelée table dérivée. C’est utile lorsque vous devez agréger deux fois, comme dans cette requête pour les dépenses moyennes par client dans chaque ville :

SELECT city, AVG(spent) AS avg_spent
FROM (
  SELECT c.city, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.city
) AS per_customer
GROUP BY city;

-- Delhi |  300.0
-- Pune  | 2300.0

La requête interne totalise chaque client et la requête externe fait la moyenne de ces totaux. Donner un alias à la table dérivée est une bonne habitude, car certaines bases de données l’exigent. Si GROUP BY est nouveau pour vous, consultez SQL GROUP BY et HAVING.

Sous-requêtes dans SELECT

Une sous-requête scalaire peut également ajouter une colonne calculée. Celui-ci compte les commandes par client :

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- Asha   | 2
-- Ben    | 1
-- Chitra | 2
-- Dev    | 0

Dev apparaît avec zéro. Une jointure interne aurait complètement supprimé Dev, car Dev n’a pas de lignes correspondantes. Vous pourriez obtenir le même résultat avec un LEFT JOIN et un GROUP BY, que nous expliquons dans Les jointures SQL expliquées.

Sous-requêtes corrélées

L’exemple précédent est également corrélé, puisque la requête interne fait référence à c.id de la requête externe. Une sous-requête non corrélée peut s’exécuter seule. Une valeur corrélée ne peut pas, car elle dépend d’une valeur de la ligne externe actuelle.

Cela permet de trouver les commandes supérieures à la moyenne de ce même client :

SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(i.total)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

-- 101 | 1 | 1200
-- 104 | 3 | 2500

Voici comment le lire. Pour la commande 101, la requête interne fait la moyenne des commandes du client 1 (1 200 et 800), ce qui donne 1 000. Puisque 1 200 est supérieur, la ligne est conservée. Pour l’ordre 102, la même moyenne s’applique, mais 800 n’est pas supérieur, elle est donc supprimée. La commande unique du client 2 est égale à sa propre moyenne, elle est donc également supprimée.

“Une fois par ligne” décrit la logique, et les optimiseurs de requêtes les réécrivent souvent en quelque chose de beaucoup plus efficace. Néanmoins, une sous-requête corrélée lente mérite d’être étudiée. Un index sur la colonne intérieure, ici orders.customer_id, peut faire une différence majeure. Voir Explication des index SQL.

EXISTE et N’EXISTE PAS

EXISTS pose une question par oui ou par non : la requête interne renvoie-t-elle au moins une ligne ? Cela peut s’arrêter à la première correspondance, et ce que vous sélectionnez à l’intérieur n’a pas d’importance, c’est pourquoi les gens écrivent souvent SELECT 1.

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Asha, Ben, Chitra

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Dev

Le piège NOT IN NULL

C’est l’un des bogues de sous-requête les plus courants. Nous avons créé une table avec un ID réel et un NULL, puis avons exécuté les deux formulaires :

CREATE TABLE r (customer_id INT);
INSERT INTO r VALUES (1), (NULL);

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM r);
-- (no rows at all)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM r WHERE r.customer_id = c.id);
-- Ben, Chitra, Dev

Pourquoi le premier ne renvoie rien ? id NOT IN (1, NULL) signifie id <> 1 AND id <> NULL. Toute comparaison avec NULL est inconnue plutôt que vraie, donc la condition dans son ensemble ne peut jamais être vraie. Il n’y a aucun message d’erreur ni aucun avertissement, juste un résultat vide qui semble plausible.

Sous-requête vs JOIN vs CTE

De nombreuses requêtes peuvent être écrites de plusieurs manières. Plus tôt, nous avons trouvé des clients avec une commande importante en utilisant IN. Voici le même résultat avec une jointure :

SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

-- Asha
-- Chitra

Notez le DISTINCT. Une jointure peut produire des doublons si un client a plusieurs correspondances, alors que IN et EXISTS ne le peuvent pas.

Les performances varient selon la base de données et les données, et les optimiseurs modernes produisent souvent des plans similaires. Choisissez donc d’abord la forme la plus claire, puis vérifiez EXPLAIN si elle s’avère lente.

Erreurs courantes

• Utilisation de = avec un résultat sur plusieurs lignes. Utilisez IN ou assurez-vous que la requête interne renvoie une ligne.
• Utilisation de NOT IN sur une colonne nullable. Vous pouvez perdre silencieusement chaque ligne.
• Utilisation d’alias peu clairs. Dans les requêtes corrélées, qualifiez chaque colonne, sinon elle pourrait être liée à la mauvaise table.
• Imbrication profonde. Trois niveaux sont difficiles à lire, alors convertissez-les en CTE.
• Ajout de ORDER BY à l’intérieur. Cela n’a généralement aucun effet sur le résultat externe et certaines bases de données le rejettent.

Pour conclure

Les sous-requêtes sont un moyen naturel d’exprimer des questions en deux étapes. Commencez par les sous-requêtes scalaires et IN, puis entraînez-vous à celles corrélées en raisonnant sur une seule ligne externe à la fois. Et rappelez-vous NOT EXISTS chaque fois que des valeurs NULL sont possibles. Pour vous entraîner, écrivez une question de trois manières, avec une sous-requête, une jointure et un CTE, et comparez les résultats et la lisibilité.

Questions fréquentes

Continuez à apprendre