Sous-requêtes

• Jointures
• Sommaire
• Jointures et sous-requêtes : modification de données

Nous allons maintenant apprendre à imbriquer plusieurs requêtes, ce qui vous permettra de faire en une seule fois ce qui vous aurait, jusqu'ici, demandé plusieurs étapes.

Une sous-requête est une requête à l'intérieur d'une autre requête. Avec le SQL, vous pouvez construire des requêtes imbriquées sur autant de niveaux que vous voulez. Vous pouvez également mélanger jointures et sous-requêtes. Tant que votre requête est correctement structurée, elle peut être aussi complexe que vous voulez.

Une sous-requête peut être faite dans une requête de type SELECT, INSERT, UPDATE ou DELETE (et quelques autres que nous n'avons pas encore vues). Nous ne verrons dans ce chapitre que les requêtes de sélection. Les jointures et sous-requêtes pour la modification, l'insertion et la suppression de données étant traitées dans le prochain chapitre.

La plupart des requêtes de sélection que vous allez voir dans ce chapitre sont tout à fait réalisables autrement, souvent avec une jointure. Certains préfèrent les sous-requêtes aux jointures parce que c'est légèrement plus clair comme syntaxe, et peut-être plus intuitif. Il faut cependant savoir qu'une jointure sera toujours au moins aussi rapide que la même requête faite avec une sous-requête. Par conséquent, s'il est important pour vous d'optimiser les performances de votre application, utilisez plutôt des jointures lorsque c'est possible.

• Sous-requêtes dans le FROM
• Sous-requêtes dans les conditions
• Sous-requêtes corrélées

Sous-requêtes dans le FROM

Lorsque l'on fait une requête de type SELECT, le résultat de la requête nous est envoyé sous forme de table. Et grâce aux sous-requêtes, il est tout à fait possible d'utiliser cette table et de refaire une recherche uniquement sur les lignes de celle-ci.

Exemple : on sélectionne toutes les femelles parmi les perroquets et les tortues .

1
2
3
4
5
6

SELECT Animal.id, Animal.sexe, Animal.date_naissance, Animal.nom, Animal.espece_id
FROM Animal
INNER JOIN Espece
    ON Espece.id = Animal.espece_id
WHERE sexe = 'F' 
AND Espece.nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone');

Parmi ces femelles perroquets et tortues, on veut connaître la date de naissance de la plus âgée. On va donc faire une sélection dans la table des résultats de la requête.

1
2
3
4
5
6
7
8
9

SELECT MIN(date_naissance)
FROM (
    SELECT Animal.id, Animal.sexe, Animal.date_naissance, Animal.nom, Animal.espece_id
    FROM Animal
    INNER JOIN Espece
        ON Espece.id = Animal.espece_id
    WHERE sexe = 'F'
    AND Espece.nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
) AS tortues_perroquets_F;

Les règles à respecter

Parenthèses

Une sous-requête doit toujours se trouver dans des parenthèses, afin de définir clairement ses limites.

Alias

Dans le cas des sous-requêtes dans le FROM, il est également obligatoire de préciser un alias pour la table intermédiaire (le résultat de notre sous-requête). Si vous ne le faites pas, MySQL déclenchera une erreur. Ici, on l'a appelée tortues_perroquets_F. Nommer votre table intermédiaire permet de plus de vous y référer si vous faites une jointure dessus, ou si certains noms de colonnes sont ambigus et que le nom de la table doit être précisé. Attention au fait qu'il ne s'agit pas de la table Animal, mais bien d'une table tirée d'Animal. Par conséquent, si vous voulez préciser le nom de la table dans le SELECT principal, vous devez écrire SELECT MIN(tortues_perroquets_F.date_naissance), et non pas SELECT MIN(Animal.date_naissance).

Cohérence des colonnes

Les colonnes sélectionnées dans le SELECT "principal" doivent bien sûr être présentes dans la table intermédiaire. La requête suivante, par exemple, ne fonctionnera pas :

1
2
3
4
5
6
7
8
9

SELECT MIN(date_naissance)
FROM (
    SELECT Animal.id, Animal.nom
    FROM Animal
    INNER JOIN Espece
        ON Espece.id = Animal.espece_id
    WHERE sexe = 'F'
    AND Espece.nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
) AS tortues_perroquets_F;

En effet, tortues_perroquets_F n'a que deux colonnes : id et nom. Il est donc impossible de sélectionner la colonne date_naissance de cette table.

Noms ambigus

Pour finir, attention aux noms de colonnes ambigus. Une table, même intermédiaire, ne peut pas avoir deux colonnes ayant le même nom. Si deux colonnes ont le même nom, il est nécessaire de renommer explicitement au moins l'une des deux.

Donc, si on veut sélectionner la colonne Espece.id en plus dans la sous-requête, on peut procéder ainsi :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT MIN(date_naissance)
FROM (
    SELECT Animal.id, Animal.sexe, Animal.date_naissance, Animal.nom, Animal.espece_id, 
            Espece.id AS espece_espece_id         -- On renomme la colonne id de Espece, donc il n'y a plus de doublons.
    FROM Animal                                   -- Attention de ne pas la renommer espece_id, puisqu'on sélectionne aussi la colonne espece_id dans Animal !
    INNER JOIN Espece
        ON Espece.id = Animal.espece_id
    WHERE sexe = 'F'
    AND Espece.nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
) AS tortues_perroquets_F;

Sous-requêtes dans les conditions

Je vous ai donc dit que lorsque vous faites une requête SELECT, le résultat est sous forme de table. Ces tables de résultats peuvent avoir :

• plusieurs colonnes et plusieurs lignes ;
• plusieurs colonnes mais une seule ligne ;
• plusieurs lignes mais une seule colonne ;
• ou encore une seule ligne et une seule colonne (c'est-à-dire juste une valeur).

Les sous-requêtes renvoyant plusieurs lignes et plusieurs colonnes ne sont utilisées que dans les clauses FROM. Nous allons ici nous intéresser aux trois autres possibilités uniquement.

Comparaisons

Pour rappel, voici un tableau des opérateurs de comparaison.

On peut utiliser des comparaisons de ce type avec des sous-requêtes qui donnent comme résultat soit une valeur (c'est-à-dire une seule ligne et une seule colonne), soit une ligne (plusieurs colonnes mais une seule ligne).

Sous-requête renvoyant une valeur

Le cas le plus simple est évidemment d'utiliser une sous-requête qui renvoie une valeur.

1
2
3
4

SELECT id, sexe, nom, commentaires, espece_id, race_id
FROM Animal
WHERE race_id = 
    (SELECT id FROM Race WHERE nom = 'Berger Allemand');  -- la sous-requête renvoie simplement 1

Remarquez que cette requête peut également s'écrire avec une jointure plutôt qu'une sous-requête :

1
2
3
4

SELECT Animal.id, sexe, Animal.nom, commentaires, Animal.espece_id, race_id
FROM Animal
INNER JOIN Race ON Race.id = Animal.race_id
WHERE Race.nom = 'Berger Allemand';

Voici un exemple de requête avec sous-requête qu'il est impossible de faire avec une simple jointure :

1
2
3
4
5

SELECT id, nom, espece_id
FROM Race
WHERE espece_id = (
    SELECT MIN(id)     -- Je rappelle que MIN() permet de récupérer la plus petite valeur de la colonne parmi les lignes sélectionnées
    FROM Espece);

En ce qui concerne les autres opérateurs de comparaison, le principe est exactement le même :

1
2
3
4
5
6

SELECT id, nom, espece_id
FROM Race
WHERE espece_id < (
    SELECT id    
    FROM Espece
    WHERE nom_courant = 'Tortue d''Hermann');

Ici la sous-requête renvoie 3, donc nous avons bien les races dont l'espèce a un id inférieur à 3 (donc 1 et 2 ).

Sous-requête renvoyant une ligne

Dans le cas d'une sous-requête dont le résultat est une ligne, la syntaxe est la suivante :

1
2
3
4
5
6

SELECT *
FROM nom_table1
WHERE [ROW](colonne1, colonne2) = (    -- le ROW n'est pas obligatoire
    SELECT colonneX, colonneY
    FROM nom_table2
    WHERE...);                         -- Condition qui ne retourne qu'UNE SEULE LIGNE

Cette requête va donc renvoyer toutes les lignes de la table1 dont la colonne1 = la colonneX de la ligne résultat de la sous-requête ET la colonne2 = la colonneY de la ligne résultat de la sous-requête.

Vous voulez un exemple peut-être ? Allons-y !

1
2
3
4
5
6

SELECT id, sexe, nom, espece_id, race_id 
FROM Animal
WHERE (id, race_id) = (
    SELECT id, espece_id
    FROM Race
    WHERE id = 7);

Décomposons calmement. Voyons d'abord ce que la sous-requête donne comme résultat.

1
2
3

SELECT id, espece_id
FROM Race
WHERE id = 7;

Et comme condition, on a WHERE (id, race_id) = #le résultat de la sous-requête#. Donc la requête renverra les lignes de la table Animal pour lesquelles id vaut 7 et race_id vaut 2.

Conditions avec IN et NOT IN

IN

Vous connaissez déjà l'opérateur IN, qui compare une colonne avec une liste de valeurs.

Exemple

1
2
3
4
5

SELECT Animal.id, Animal.nom, Animal.espece_id
FROM Animal
INNER JOIN Espece
    ON Espece.id = Animal.espece_id
WHERE Espece.nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone');

Cet opérateur peut également s'utiliser avec une sous-requête dont le résultat est une colonne ou une valeur. On peut donc réécrire la requête ci-dessus en utilisant une sous-requête plutôt qu'une jointure :

1
2
3
4
5
6
7

SELECT id, nom, espece_id
FROM Animal
WHERE espece_id IN (
    SELECT id 
    FROM Espece
    WHERE nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
);

Le fonctionnement est plutôt facile à comprendre. La sous-requête donne les résultats suivants :

1
2
3

SELECT id            -- On ne sélectionne bien qu'UNE SEULE COLONNE.
FROM Espece
WHERE nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone');

Ainsi, la requête principale sélectionnera les lignes qui ont un espece_id parmi ceux renvoyés par la sous-requête, donc 3 ou 4.

NOT IN

Si l'on utilise NOT IN, c'est bien sûr le contraire, on exclut les lignes qui correspondent au résultat de la sous-requête. La requête suivante nous renverra donc les animaux dont l'espece_id n'est pas 3 ou 4.

1
2
3
4
5
6
7

SELECT id, nom, espece_id
FROM Animal
WHERE espece_id NOT IN (
    SELECT id 
    FROM Espece
    WHERE nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
);

Conditions avec ANY, SOME et ALL

Les conditions avec IN et NOT IN sont un peu limitées, puisqu'elles ne permettent que des comparaisons de type "est égal" ou "est différent". Avec ANY et ALL, on va pouvoir utiliser les autres comparateurs (plus grand, plus petit, etc.).

• ANY : veut dire "au moins une des valeurs".
• SOME : est un synonyme de ANY.
• ALL : signifie "toutes les valeurs".

ANY (ou SOME)

La requête suivante signifie donc "Sélectionne les lignes de la table Animal, dont l'espece_id est inférieur à au moins une des valeurs sélectionnées dans la sous-requête". C'est-à-dire inférieur à 3 ou à 4. Vous aurez donc dans les résultats toutes les lignes dont l'espece_id vaut 1, 2 ou 3 (puisque 3 est inférieur à 4).

1
2
3
4
5
6
7

SELECT *
FROM Animal
WHERE espece_id < ANY (
    SELECT id
    FROM Espece
    WHERE nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
);

ALL

Par contre, si vous utilisez ALL plutôt que ANY, cela signifiera "Sélectionne les lignes de la table Animal, dont l'espece_id est inférieur à toutes les valeurs sélectionnées dans la sous-requête". Donc inférieur à 3 et à 4. Vous n'aurez donc plus que les lignes dont l'espece_id vaut 1 ou 2.

1
2
3
4
5
6
7

SELECT *
FROM Animal
WHERE espece_id < ALL (
    SELECT id
    FROM Espece
    WHERE nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
);

Remarque : lien avec IN

Remarquez que = ANY est l'équivalent de IN, tandis que <> ALL est l'équivalent de NOT IN. Attention cependant que ANY et ALL (et SOME) ne peuvent s'utiliser qu'avec des sous-requêtes, et non avec des valeurs comme on peut le faire avec IN. On ne peut donc pas faire ceci :

1
2
3

SELECT id
FROM Espece
WHERE nom_courant = ANY ('Tortue d''Hermann', 'Perroquet amazone');

1

#1064 - You have an error in your SQL syntax;

Sous-requêtes corrélées

Une sous-requête corrélée est une sous-requête qui fait référence à une colonne (ou une table) qui n'est pas définie dans sa clause FROM, mais bien ailleurs dans la requête dont elle fait partie.

Vu que ce n'est pas une définition extrêmement claire de prime abord, voici un exemple de requête avec une sous-requête corrélée :

1
2
3
4
5
6
7

SELECT colonne1 
FROM tableA
WHERE colonne2 IN (
    SELECT colonne3
    FROM tableB
    WHERE tableB.colonne4 = tableA.colonne5
    );

Si l'on prend la sous-requête toute seule, on ne pourra pas l'exécuter :

1
2
3

SELECT colonne3
FROM tableB
WHERE tableB.colonne4 = tableA.colonne5

En effet, seule la tableB est sélectionnée dans la clause FROM, il n'y a pas de jointure avec la tableA, et pourtant on utilise la tableA dans la condition.

Par contre, aucun problème pour l'utiliser comme sous-requête, puisque la clause FROM de la requête principale sélectionne la tableA. La sous-requête est donc corrélée à la requête principale.

Attention : si MySQL rencontre une table inconnue dans une sous-requête, elle va aller chercher dans les niveaux supérieurs uniquement si cette table existe. Donc, imaginons que l'on a une requête avec 3 niveaux : la requête principale (niveau 1), une ou plusieurs sous-requêtes (niveau 2) et une ou plusieurs sous-sous-requêtes, c'est-à-dire une sous-requête dans une sous-requête (niveau 3).

• Une sous-requête (niveau 2) peut être corrélée à la requête principale (niveau 1).
• Une sous-sous-requête (niveau 3) peut être corrélée à la sous-requête dont elle dépend (niveau 2), ou à la requête principale (niveau 1).
• Mais une sous-requête (niveau 2) ne peut pas être corrélée à une autre sous-requête (niveau 2).

Si l'on prend le schéma suivant, on peut donc remonter l'arbre, mais jamais descendre d'un cran pour trouver les tables nécessaires.

Peuvent être corrélées à :

• A : B, C, D, E, F, G et H
• B : aucune
• C : D, E, F, G et H
• D : F, G et H
• E : aucune

Le temps de vous expliquer le fonctionnement de EXISTS et NOT EXISTS, et nous verrons un exemple de sous-requête corrélée.

Conditions avec EXISTS et NOT EXISTS

Les conditions EXISTS et NOT EXISTS s'utilisent de la manière suivante :

1
2

SELECT * FROM nom_table
WHERE [NOT] EXISTS (sous-requête)

Une condition avec EXISTS sera vraie (et donc la requête renverra quelque chose) si la sous-requête correspondante renvoie au moins une ligne. Une condition avec NOT EXISTS sera vraie si la sous-requête correspondante ne renvoie aucune ligne.

Exemple : on sélectionne les races s'il existe un animal qui s'appelle Balou.

1
2

SELECT id, nom, espece_id FROM Race 
WHERE EXISTS (SELECT * FROM Animal WHERE nom = 'Balou');

Vu qu'il existe bien un animal du nom de Balou dans notre table Animal, la condition est vraie, on sélectionne donc toutes les races. Si l'on avait utilisé un nom qui n'existe pas, la requête n'aurait renvoyé aucun résultat.

Vous conviendrez cependant qu'une telle requête n'a pas beaucoup de sens, c'était juste pour vous faire comprendre le principe. En général, on utilise WHERE [NOT] EXISTS avec des sous-requêtes corrélées.

Exemple : je veux sélectionner toutes les races dont on ne possède aucun animal.

1
2

SELECT * FROM Race
WHERE NOT EXISTS (SELECT * FROM Animal WHERE Animal.race_id = Race.id);

La sous-requête est bien corrélée à la requête principale, puisqu'elle utilise la table Race, qui n'est pas sélectionnée dans la sous-requête.

En résultat, on a bien le Sphynx, puisqu'on n'en possède aucun.

En résumé

• Une sous-requête est une requête imbriquée dans une autre requête.
• Il est obligatoire de donner un alias au résultat d'une sous-requête lorsqu'on l'utilise dans une clause FROM.
• IN, ANY, SOME et ALL s'utilisent uniquement avec des sous-requêtes renvoyant une seule colonne.
• Une sous-requête corrélée est une sous-requête utilisant une table référencée uniquement dans la requête dont elle dépend, et non dans la sous-requête elle-même.
• EXISTS renvoie vrai si la sous-requête qui y est associée renvoie au moins un résultat.

• Jointures
• Sommaire
• Jointures et sous-requêtes : modification de données