• 20 heures
  • Moyenne

Ce cours est visible gratuitement en ligne.

course.header.alt.is_video

course.header.alt.is_certifying

J'ai tout compris !

Mis à jour le 20/10/2020

Imbriquez des requêtes avec IN, ALL, ANY et EXISTS

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

Saviez-vous qu'il était possible d'imbriquer des requêtes ? Normalement oui, vous en avez déjà vu passer subrepticement dans ce cours !

Réfléchissons...

Prenons un exemple. Dans le chapitre sur le mot clé HAVING, j'expliquais que celui-ci servait à effectuer une restriction sur une table après agrégation. Mais si HAVING vous semble compliqué, il est possible d'utiliser une requête imbriquée :

-- REQUETE n°1
SELECT *
FROM (SELECT id_address, count(*) AS cnt FROM entity GROUP BY id_address) a
WHERE a.cnt > 500 ;
-- REQUETE n°2, équivalente à la requête n°1 :
SELECT id_address, count(*) AS cnt
FROM entity
GROUP BY id_address
HAVING count(*) > 500 ;

Que retournent ces requêtes ? Elles retournent les adresses auxquelles sont domiciliées plus de 500 sociétés.

Le mot clé IN

Ces deux requêtes ne renvoient que l'identifiant de l'adresse, qui est un nombre entier. Comment obtenir l'adresse de manière intelligible ?

Nous pourrions faire une jointure avec la table address, mais nous pouvons également utiliser le mot clé IN, qui signifie en français dans, en posant une question du type :

Donne-moi les lignes de la table address pour lesquelles l'identifiant se trouve DANS la colonne id_address de la table obtenue par la requête n°2.

Nous sommes encore face à une requête imbriquée, qui utilise le résultat de la requête n°2 (en haut de cette page) :

SELECT *
FROM address a
WHERE a.id_address IN (
SELECT id_address
FROM entity
GROUP BY id_address
HAVING count(*) > 500
);

Les mots clés ANY et ALL

Y a-t-il des intermédiaires qui sont plus actifs que les deux intermédiaires que nous avons trouvés précédemment : Pacher Banking S.A. et Plouf Financial Services Corp. ?

D'abord, nous avons créé une table nb_entities, indiquant pour chaque intermédiaire le nombre de sociétés dont il a participé à la création :

nb_entities

id_intermediary

intermediary_name

cnt_entities

 5000

Pacher Banking S.A.

 2184

 5001

Plouf Financial Services Corp.

 720

[...]

[...]

[...]

Pour répondre à cette question, il faut utiliser un opérateur suivi du mot clé ALL ou ANY :

SELECT * FROM nb_entities WHERE cnt_entities > ALL(SELECT cnt_entities FROM nb_entities WHERE intermediary_id IN (5000,5001,5002));

Le mot clé EXISTS

Reprenons la requête que nous avons vu plus haut, dans le paragraphe sur IN, et mettons-nous dans la peau de votre SGBDR, qui devra exécuter cette requête :

SELECT *
FROM address a
WHERE a.id_address IN (
SELECT id_address
FROM entity
GROUP BY id_address
HAVING count(*) > 500
);

Pour chaque ligne de la table address,  vous êtes censé vérifier si la condition du WHERE est vraie ou fausse, n'est-ce pas? La condition sera testée autant de fois qu'il y a de lignes.

On est ok là-dessus. Mais ici, dans le WHERE, il y a une sous-requête imbriquée (de la ligne 4 à la ligne 7). Allez-vous recalculer cette sous-requête à chaque fois que vous allez vérifier la condition du WHERE, pour chaque ligne de address ??? Ce serait bête, car le résultat de cette sous-requête est toujours le même : autant garder son résultat en mémoire plutôt que de le recalculer à chaque fois !

Sauf que... si cette sous-requête dépend de la ligne pour laquelle on est en train de tester le WHERE, il n'est plus possible de mettre le résultat en cache. :( S'il faut recalculer la sous-requête à chaque fois, cela prend du temps !

Si l'on est dans ce cas de figure, il est préférable (du point de vue performance) d'utiliser EXISTS, qui teste si une sous-requête contient au moins une ligne. S'il en trouve au moins une, il arrête l'exécution de la sous-requête, car il est satisfait !

Aller plus loin : utiliser IN sur plusieurs colonnes

Dans ce chapitre, on a vérifié si une valeur est présente parmi une liste de valeurs.

Il est également possible de vérifier si un couple de deux valeurs (valeur1, valeur2) est présent dans une liste de couples (c1, c2), de cette manière :

SELECT * FROM t1 WHERE t1.valeur1, t1.valeur2 IN (SELECT c1, c2 FROM t2) ;

Aller plus loin : les tables temporaires

Théoriquement, il n'y a pas vraiment de limite à l'imbrication de requêtes. Il est possible d'imbriquer des requêtes imbriquées elles-même dans une requête… etc ! :soleil:

Afin de ne pas avoir des requêtes à rallonge, sachez qu'il est parfois possible (selon votre SGBDR) de créer des tables temporaires. Celles-ci permettent de créer de nouvelles tables pour y stocker le résultat d'une requête, puis de l'utiliser dans une autre requête.

Par exemple, la requête n°1 en haut de cette page est équivalente à ce code :

CREATE TEMP TABLE une_table_temporaire AS
SELECT id_address, count(*) AS cnt
FROM entity
GROUP BY id_address ;
SELECT * FROM une_table_temporaire a WHERE a.cnt > 500 ;

Ces tables temporaires seront ensuite effacées à la fermeture de votre session avec le SGBDR.

En résumé

  • Pour tester si une valeur se trouve dans le résultat d'une requête, on utilise IN.

  • Soit B une liste de valeurs renvoyées par une requête. Pour tester si une condition (par exemple A < B) est vraie pour...

    • ... toutes les valeurs de B, on utilise ALL,

    • ... au moins une valeur de B, on utilise ANY.

  • EXISTS vérifie si la sous-requête contient au moins une ligne.

Exemple de certificat de réussite
Exemple de certificat de réussite