LE SQL de A à Z : 5e partie - les sous-requêtes

Nous voici maintenant dans le plus puissant du SQL avec les sous-requêtes, c'est-à-dire des requêtes situées à l'intérieur d'autres requêtes, et plus puissant nous aborderons la corrélation des sous-requêtes.

Article lu fois.

L'auteur

SQLPro

L'article

Publié le 26 août 2003 - Mis à jour le 13 juillet 2008

ePub, Azw et Mobi

Liens sociaux

Préambule▲

La structure de la base de données exemple, ainsi qu'une version des principales bases utilisées sont disponibles dans la page « La base de données exemple ».

1. Les sous-requêtes▲

Vous trouverez des compléments d'information sur le sujet aux pages 154 à 174 de l'ouvrage « SQL », collection « La Référence », Campus Press éditeur.

Avec SQL il est possible d'imbriquer des requêtes un peu à la manière de poupées gigognes.

Mais pour toucher du doigt ce que l'on peut faire avec des sous-requêtes, posons-nous la question : Où placer une sous-requête ?

Observons les types de résultats qu'une requête produit pour déterminer les emplacements qu'elle peut prendre au sein d'un ordre SELECT :

Résultat	Typologie des résultats
Sélectionnez `TIT_CODE CLI_NOM CLI_PRENOM -------- ---------- ---------- Mme. BOYER Martine Mme. GALLACIER Noëlle Mme. HESS Lucette Mme. LETERRIER Monique Mme. MARTINET Carmen Mme. DAVID Jacqueline Mme. MOURGUES Jacqueline Mme. ZAMPIERO Annick Mme. ROURE Marie-Louise Mme. DE CONINCK Patricia ...`	Une table composée de colonnes et de lignes.
Sélectionnez `CLI_PRENOM ---------- Alain Marc Alain Paul Jean Alain Marcel Arsène André Daniel ...`	Une table d'une seule colonne, c'est-à-dire une liste.
Sélectionnez `TIT_CODE CLI_NOM CLI_PRENOM -------- ------------------------ M. RAY Yannick Mme. ROURE Marie-Louise M. RECHUL Jacques M. ROUSSILLON Alain`	Une ligne d'une table.
Sélectionnez `MOYENNE -------- 406,74 E`	Une table d'une seule ligne et d'une seule colonne, c'est-à-dire une valeur unique.
Sélectionnez `TIT_CODE CLI_NOM CLI_PRENOM -------- ---------- ----------`	Pas de réponse (une table vide) et par opposition, une table NON vide.

Voyons maintenant comment imbriquer cela dans une autre requête.

Imbrication requête (résultat de requête)	Typologie de résultat et emplacement de la sous-requête	Représentation
Sélectionnez `SELECT * FROM ( TIT_CODE CLI_NOM CLI_PRENOM -------- ---------- ---------- Mme. BOYER Martine Mme. GALLACIER Noëlle Mme. HESS Lucette Mme. LETERRIER Monique Mme. MARTINET Carmen Mme. DAVID Jacqueline Mme. MOURGUES Jacqueline Mme. ZAMPIERO Annick Mme. ROURE Marie-Louise Mme. DE CONINCK Patricia )`	Une requête renvoyant une table peut être imbriquée dans la clause FROM d'une autre requête.	Sélectionnez `SELECT * FROM ( SELECT TIT_CODE, CLI_NOM, CLI_PRENOM FROM MaTable ) TableReponse ici, TableReponse est le renommage en table du résultat de la requête, car la clause FROM doit porter sur des tables nommées.`
Sélectionnez `SELECT * FROM Matable WHERE uneColonne IN ( CLI_PRENOM ---------- Alain Marc Alain Paul Jean Alain Marcel Arsène André Daniel )`	Une requête renvoyant une seule colonne avec plusieurs valeurs peut être imbriqué dans le prédicat IN, mais aussi dans les prédicats ALL, SOME(ANY).	Sélectionnez `SELECT * FROM Matable WHERE uneColonne IN ( SELECT CLI_NOM FROM MaTable )`
Sélectionnez `SELECT * FROM maTable WHERE (valeur1, valeur2, valeur3) MATCH (TIT_CODE CLI_NOM CLI_PRENOM -------- ------------------------ M. RAY Yannick Mme. ROURE Marie-Louise M. RECHUL Jacques M. ROUSSILLON Alain )`	Une requête renvoyant une seule ligne peut être imbriquée dans un prédicat MATCH et comparée à une ligne valuée.	Sélectionnez `SELECT * FROM maTable WHERE (valeur1, valeur2, valeur3) MATCH (SELECT TIT_CODE, CLI_NOM, CLI_PRENOM FROM T_CLIENT WHERE CLI_NOM LIKE'R%' )`
Sélectionnez `SELECT , (MOYENNE -------- 406,74 E) AS MOYENNE_DES_COMMANDES FROM MaTable ou SELECT FROM MaTable WHERE MaColonne = (MOYENNE -------- 406,74 E) ou SELECT COUNT(), MaColonne FROM MaTable GROUP BY MaColonne HAVING COUNT() = (MOYENNE -------- 406,74 E) ou SELECT * FROM MaTable T1 JOIN AutreTable T2 ON T1.colonne1 = T2.colonne2 - (MOYENNE -------- 406,74 E) Etc...`	Une requête renvoyant une valeur unique peut être imbriquée partout ou une constante peut figurer…	Sélectionnez `SELECT , (SELECT AVG(colonne) FROM UneTable) AS MOYENNE_DES_COMMANDES FROM MaTable ou SELECT FROM MaTable WHERE MaColonne = (SELECT AVG(colonne) FROM UneTable) ou SELECT COUNT(), MaColonne FROM MaTable GROUP BY MaColonne HAVING COUNT() = (SELECT AVG(colonne) FROM UneTable) ou SELECT * FROM MaTable T1 JOIN AutreTable T2 ON T1.colonne1 = T2.colonne2 - (SELECT AVG(colonne) FROM UneTable)`
Sélectionnez `SELECT * FROM MaTable WHERE EXISTS ( TIT_CODE CLI_NOM CLI_PRENOM -------- ------- ---------- )`	Une requête renvoyant des valeurs ou pas peut être imbriquée dans un prédicat EXISTS, UNIQUE et MATCH.	Sélectionnez `SELECT * FROM MaTable WHERE EXISTS (SELECT TIT_CODE CLI_NOM CLI_PRENOM FROM UneTable)`

Nous verrons qu'il existe des opérateurs spécialisés, comme EXISTS pour traiter particulièrement de cas d'imbrication. En particulier, les opérateurs ALL, SOME(ANY), MATCH et UNIQUE.

Chaque fois que vous voudrez mettre une sous-requête dans un ordre SQL, assurez-vous que la sous-requête est comprise dans une paire de parenthèses.

1-1. Sous-requêtes renvoyant une seule valeur▲

Nous allons d'abord étudier ce que nous pouvons faire en utilisant des sous-requêtes ne renvoyant qu'une valeur unique. La plupart du temps, nous avons l'assurance de ne renvoyer qu'une valeur unique si nous utilisons une requête dont l'unique colonne est le résultat d'un calcul statistique (agrégation) comme les MAX, MIN, AVG, COUNT et SUM. C'est pourquoi on trouvera souvent ce genre d'expression dans les requêtes imbriquées des filtres WHERE et HAVING, mais aussi parfois dans la clause SELECT.

1-1-1. Dans la clause SELECT▲

On peut placer dans la clause SELECT, à la place de colonnes, des sous-requêtes, voire même combiner par une opération, une colonne et une sous-requête.

Notre hôtelier voudrait connaître l'évolution du prix moyen de ses chambres par rapport à son tarif de référence au premier janvier 2000.
Le prix moyen des chambres pour n'importe quelle date d'application peut être obtenu par :

Exemple 1 :

Sélectionnez

SELECT TRF_DATE_DEBUT, AVG(TRF_CHB_PRIX) AS MOYENNE
FROM   TJ_TRF_CHB
GROUP  BY TRF_DATE_DEBUT

Sélectionnez

TRF_DATE_DEBUT MOYENNE 
-------------- --------
1999-01-01     255.2500
1999-09-01     280.6500
2000-01-01     306.0500
2000-09-01     382.2500
2001-01-01     407.6500

Le tarif de référence qui nous intéresse est visible sur la ³^e ligne de la réponse. Nous pouvons l'obtenir en précisant la requête :

Exemple 2 :

Sélectionnez

SELECT AVG(TRF_CHB_PRIX) AS MOYENNE
FROM   TJ_TRF_CHB
WHERE  TRF_DATE_DEBUT = '2000-01-01'

Sélectionnez

TRF_DATE_DEBUT MOYENNE 
-------------- --------
1999-01-01     -50.8000
1999-09-01     -25.4000
2000-01-01     .0000
2000-09-01     76.2000
2001-01-01     101.6000

Pour calculer l'écart, il suffit de reporter ce nombre en le soustrayant du prix moyen de la requête de l'exemple 1 :

Exemple 3 :

Sélectionnez

SELECT TRF_DATE_DEBUT, 
       AVG(TRF_CHB_PRIX) - 306.05 AS MOYENNE
FROM   TJ_TRF_CHB
GROUP  BY TRF_DATE_DEBUT

Sélectionnez

TRF_DATE_DEBUT MOYENNE 
-------------- --------
1999-01-01     -50.8000
1999-09-01     -25.4000
2000-01-01     .0000
2000-09-01     76.2000
2001-01-01     101.6000

Il ne suffit plus que de remplacer la valeur 306.50 par la requête qui l'a générée :

Exemple 4 :

Sélectionnez

SELECT TRF_DATE_DEBUT, 
       AVG(TRF_CHB_PRIX) - (SELECT AVG(TRF_CHB_PRIX) 
                            FROM   TJ_TRF_CHB
                            WHERE  TRF_DATE_DEBUT = '2000-01-01')  AS MOYENNE
FROM   TJ_TRF_CHB
GROUP  BY TRF_DATE_DEBUT

Sélectionnez

TRF_DATE_DEBUT MOYENNE 
-------------- --------
1999-01-01     -50.8000
1999-09-01     -25.4000
2000-01-01     .0000
2000-09-01     76.2000
2001-01-01     101.6000

NOTA : remarquez que nous n'avons plus besoin de nommer les colonnes de la sous-requête. Observez aussi que la sous-requête a été placée dans une paire de parenthèses.

1-1-2. Dans les filtres WHERE et HAVING▲

C'est l'endroit le plus classique pour placer une sous-requête.

Premier exemple :

Intéressons de savoir quelles sont les chambres au 01/01/2000 qui ont un prix voisin à + ou - 10 € de la moyenne des prix au 1/1/2000.
Nous savons déjà que la moyenne des prix au 1/1/200 de toutes les chambres a déjà été calculée par la requête de l'exemple 2 et sa valeur est 306.50.
Nous pouvons donc formuler ainsi la requête :

Exemple 5 :

Sélectionnez

SELECT CHB_ID, TRF_CHB_PRIX
FROM   TJ_TRF_CHB
WHERE  TRF_CHB_PRIX BETWEEN 296.5 AND 316.50
  AND  TRF_DATE_DEBUT = '2000-01-01'

Sélectionnez

CHB_ID      TRF_CHB_PRIX 
----------- ------------ 
2           300.0000
6           300.0000
9           300.0000
16          300.0000
19          300.0000

Il semble qu'il faudrait envisager de placer deux fois la sous-requête. Mais une petite astuce due aux propriétés des équations va nous permettre de résoudre ce problème. En effet, si nous retirons du prix de la chambre la valeur 306.50, la requête devient :

Exemple 6 :

Sélectionnez

SELECT CHB_ID, TRF_CHB_PRIX
FROM   TJ_TRF_CHB
WHERE  TRF_CHB_PRIX - 306.50 BETWEEN -10 AND +10
  AND  TRF_DATE_DEBUT = '2000-01-01'

Sélectionnez

CHB_ID      TRF_CHB_PRIX 
----------- ------------
2           300.0000
6           300.0000
9           300.0000
16          300.0000
19          300.0000

Ce qui d'ailleurs est le strict énoncé du départ. Dès lors le remplacement de cette somme par la requête de l'exemple 2 est un jeu d'enfant :

Exemple 7 :

Sélectionnez

SELECT CHB_ID, TRF_CHB_PRIX
FROM   TJ_TRF_CHB
WHERE  TRF_CHB_PRIX - (SELECT AVG(TRF_CHB_PRIX)
                       FROM TJ_TRF_CHB
                       WHERE TRF_DATE_DEBUT = '2000-01-01') BETWEEN -10 AND +10
AND TRF_DATE_DEBUT = '2000-01-01'

Sélectionnez

CHB_ID      TRF_CHB_PRIX 
----------- ------------
2           300.0000
6           300.0000
9           300.0000
16          300.0000
19          300.0000

Deuxième exemple :

Notre hôtelier désire savoir quels sont les mois pour lesquels le taux d'occupation de son hôtel a dépassé les 2/3.
Calculer le taux d'occupation (c'est-à-dire le nombre de chambres occupées chaque mois) est assez simple. Il suffit de compter le nombre d'occurrences de la table où sont stockées les informations des réservations (TJ_CHB_PLN_CLI).

Exemple 8 :

Sélectionnez

SELECT EXTRACT(YEAR FROM PLN_JOUR) AS ANNEE, 
       EXTRACT(MONTH FROM PLN_JOUR) AS MOIS, 
       COUNT(*) AS NOMBRE 
FROM   TJ_CHB_PLN_CLI
GROUP  BY EXTRACT(YEAR FROM PLN_JOUR), EXTRACT(MONTH FROM PLN_JOUR)
ORDER  BY ANNEE, MOIS

Sélectionnez

ANNEE       MOIS        NOMBRE 
----------- ----------- ------
1999        1           404
1999        2           354
1999        3           405
1999        4           382
1999        5           436
1999        6           392
1999        7           394
1999        8           424
1999        9           399
1999        10          419
...

De même calculer un taux d'occupation de 66.67 % consiste à faire le décompte des chambres et le multiplier par ce facteur, ce qui donne un taux d'occupation par nuit, que l'on peut ensuite ramener au mois par une cote mal taillée de 30 jours (référence comptable) :

Exemple 9 :

Sélectionnez

SELECT COUNT(*) * 30 * 0.6667 AS TAUX_OCCUPATION_MOYEN
FROM T_CHAMBRE

Sélectionnez

TAUX_OCCUPATION_MOYEN
---------------------
400.0200

Dès lors nous avons les éléments pour imbriquer nos requêtes.
Le filtrage d'un agrégat (calcul statistique) ne peut se faire que par le filtre HAVING :

Exemple 10 :

Sélectionnez

SELECT EXTRACT(YEAR FROM PLN_JOUR) AS ANNEE, 
       EXTRACT(MONTH FROM PLN_JOUR) AS MOIS, 
       COUNT(*) AS NOMBRE 
FROM   TJ_CHB_PLN_CLI
GROUP  BY EXTRACT(YEAR FROM PLN_JOUR), EXTRACT(MONTH FROM PLN_JOUR)
HAVING COUNT(*) &gt; 400.02
ORDER  BY ANNEE, MOIS

Sélectionnez

ANNEE       MOIS        NOMBRE 
----------- ----------- ------
1999        1           404
1999        3           405
1999        5           436
1999        8           424
1999        10          419
1999        12          440
2000        1           418
2000        2           402
2000        3           422
2000        4           401
...

La touche finale consistant à remplacer la valeur numérique 400.02 par la requête de l'exemple 9 en n'oubliant pas les parenthèses :

Exemple 11 :

Sélectionnez

SELECT EXTRACT(YEAR FROM PLN_JOUR) AS ANNEE, 
       EXTRACT(MONTH FROM PLN_JOUR) AS MOIS, 
       COUNT(*) AS NOMBRE 
FROM   TJ_CHB_PLN_CLI
GROUP  BY EXTRACT(YEAR FROM PLN_JOUR), EXTRACT(MONTH FROM PLN_JOUR)
HAVING COUNT(*) > (SELECT COUNT(*) * 30 * 0.6667 
                   FROM   T_CHAMBRE)
ORDER  BY ANNEE, MOIS

Sélectionnez

ANNEE       MOIS        NOMBRE 
----------- ----------- ------
1999        1           404
1999        3           405
1999        5           436
1999        8           424
1999        10          419
1999        12          440
2000        1           418
2000        2           402
2000        3           422
2000        4           401
...

Ce n'est pas plus compliqué que cela ! En fait, il faut comprendre que les mécanismes de base pour l'implémentation des sous-requêtes sont toujours les mêmes : décomposition de la requête en éléments simples, création des requêtes élémentaires puis réassemblage du tout.

NOTA : on peut aussi placer une sous-requête dans le filtre de jointure ON.

1-2. sous-requêtes renvoyant une liste ( Opérateurs IN, ALL et ANY(SOME) )▲

Une liste de valeurs, c'est-à-dire une colonne, ne peut être utilisée comme critère de comparaison que par des opérateurs spécialisés.
C'est le cas de l'opérateur IN, mais aussi des opérateurs ALL et ANY (ou SOME) que nous allons étudier dans un second temps.

1-2-1. Dans le prédicat IN▲

L'opérateur IN est utilisable dans tous les prédicats, c'est pourquoi on le retrouve dans les filtres WHERE et HAVING. Pour alimenter une liste de valeur pour le prédicat IN, il suffit de placer une requête ne renvoyant qu'une seule colonne.

Premier exemple :

Monsieur BOUVIER vient réserver une chambre, et comme il s'y prend à l'avance, il aimerait prendre une chambre dans laquelle il n'a jamais dormi au cours de l'année 2001.
Dans ce genre de cas, la négation se fait par différence : toutes les chambres de l'hôtel MOINS les chambres dans lesquelles monsieur BOUVIER a déjà couché au cours de l'année 2001.

Trouver les chambres occupées par monsieur BOUVIER au cours de l'année 2001, n'est pas bien difficile :

Exemple 12 :

Sélectionnez

SELECT DISTINCT C.CHB_ID 
FROM   TJ_CHB_PLN_CLI J J
       JOIN T_CLIENT C
            ON J.CLI_ID = C.CLI_ID
WHERE  C.CLI_NOM ='BOUVIER'
  AND  EXTRACT(YEAR FROM J.PLN_JOUR) = 2001

Sélectionnez

CHB_ID 
----------- 
1
2
3
4
6
7
9
10
11
12
...

De même, trouver toutes les chambres de l'hôtel, relève de la plus élémentaire des requêtes :

Exemple 13 :

Sélectionnez

SELECT CHB_ID
FROM   T_CHAMBRE

Sélectionnez

CHB_ID 
----------- 
1
2
3
4
5
6
7
8
9
10
...

Dès lors l'utilisation du IN, et plus particulièrement ici du NOT IN, va permettre de faire le lien entre les deux requêtes :

Exemple 14 :

Sélectionnez

SELECT CHB_ID
FROM   T_CHAMBRE
WHERE  CHB_ID NOT IN (SELECT DISTINCT C.CHB_ID 
                      FROM   TJ_CHB_PLN_CLI
                             JOIN T_CLIENT C
                                  ON J.CLI_ID = C.CLI_ID
                      WHERE  C.CLI_NOM ='BOUVIER'
                        AND  EXTRACT(YEAR FROM J.PLN_JOUR) = 2001)

Sélectionnez

CHB_ID 
------
5
8

Notre client pourra coucher dans l'une des chambres 5 ou 8.

NOTA : beaucoup de requêtes utilisant le IN (comme le NOT IN) peuvent être simplifiées en utilisant des jointures. Le IN par des jointures internes, le NOT IN par des jointures externes associées à une clause HAVING COUNT(…) = 0. En général les performances seront meilleures en utilisant une jointure que dans le cas d'une sous-requête avec [NOT] IN.

Ainsi notre exemple 14, peut se récrire :

Exemple 15 :

Sélectionnez

SELECT DISTINCT H.CHB_ID
FROM   T_CHAMBRE H
       LEFT OUTER JOIN TJ_CHB_PLN_CLI J
            ON H.CHB_ID = J.CHB_ID 
       LEFT OUTER JOIN T_CLIENT C
            ON J.CLI_ID = C.CLI_ID AND   CLI_NOM ='BOUVIER'
   AND EXTRACT(YEAR FROM J.PLN_JOUR) = 2001
GROUP  BY H.CHB_ID, J.CHB_ID
HAVING COUNT(C.CLI_ID) = 0
ORDER  BY H.CHB_ID

Sélectionnez

CHB_ID 
------ 
5
8

Second exemple :

Le gérant de l'hôtel voudrait savoir quels sont les mois (et les années) qui ont eu un nombre de nuitées égal aux nuitées enregistrées au cours de n'importe quel mois de janvier.

Là encore, il convient de décortiquer la question pour en trouver les requêtes élémentaires.
Pour connaître le nombre de nuitées des mois de janvier, il suffit de totaliser la colonne CHB_PLN_CLI_NB_PERS de la table TJ_CHB_PLN_CLI, comme suit :

Exemple 16 :

Sélectionnez

SELECT COUNT (CHB_PLN_CLI_NB_PERS) AS PERSONNE,
       EXTRACT(YEAR FROM PLN_JOUR) AS ANNEE
FROM   TJ_CHB_PLN_CLI
WHERE  EXTRACT(MONTH FROM PLN_JOUR) = 1
GROUP  BY EXTRACT(YEAR FROM PLN_JOUR)

Sélectionnez

PERSONNE    ANNEE 
----------- -----
404         1999
415         2001
418         2000

Étendre le comptage à tous les mois de toutes les années n'est pas plus difficile :

Exemple 17 :

Sélectionnez

SELECT COUNT (CHB_PLN_CLI_NB_PERS) AS PERSONNE,
       EXTRACT(YEAR FROM PLN_JOUR) ANNEE,
       EXTRACT(MONTH FROM PLN_JOUR) MOIS
FROM   TJ_CHB_PLN_CLI
GROUP  BY EXTRACT(YEAR FROM PLN_JOUR), EXTRACT(MONTH FROM PLN_JOUR)

Sélectionnez

PERSONNE    ANNEE       MOIS 
----------- ----------- ----
412         2000        12
389         2001        11
405         1999        3
402         2000        2
415         2001        1
382         1999        4
422         2000        3
379         2001        2
424         1999        8
413         2000        7
...

Maintenant, pour ne plus filtrer ces comptages que sur les valeurs retournées par la première requête (404, 415, 418), il ne suffit plus que d'utiliser la clause HAVING comme suit :

Exemple 18 :

Sélectionnez

SELECT COUNT (CHB_PLN_CLI_NB_PERS) AS PERSONNE,
       EXTRACT(YEAR FROM PLN_JOUR) ANNEE,
       EXTRACT(MONTH FROM PLN_JOUR) MOIS
FROM   TJ_CHB_PLN_CLI
GROUP  BY EXTRACT(YEAR FROM PLN_JOUR), EXTRACT(MONTH FROM PLN_JOUR)
HAVING COUNT (CHB_PLN_CLI_NB_PERS) IN (404, 415, 418)

Sélectionnez

PERSONNE    ANNEE       MOIS 
----------- ----------- ----
415         2001        1
404         1999        1
415         2001        7
404         2001        4
418         2000        1

c'est-à-dire en reprenant le résultat de la requête 16 dans une liste IN.

Il ne nous reste plus qu'à remplacer le contenu de la dernière parenthèse située après le IN par la première requête à laquelle on ne laisse subsister que la colonne de comptage dans la clause SELECT :

Exemple 19 :

Sélectionnez

SELECT COUNT (CHB_PLN_CLI_NB_PERS) AS PERSONNE,
       EXTRACT(YEAR FROM PLN_JOUR) ANNEE,
       EXTRACT(MONTH FROM PLN_JOUR) MOIS
FROM   TJ_CHB_PLN_CLI
GROUP  BY EXTRACT(YEAR FROM PLN_JOUR), EXTRACT(MONTH FROM PLN_JOUR)
HAVING COUNT (CHB_PLN_CLI_NB_PERS) IN (SELECT COUNT (CHB_PLN_CLI_NB_PERS)
                                       FROM   TJ_CHB_PLN_CLI
                                       WHERE  EXTRACT(MONTH FROM PLN_JOUR) = 1
                                       GROUP  BY EXTRACT(YEAR FROM PLN_JOUR),
                                                 EXTRACT(MONTH FROM PLN_JOUR))

Sélectionnez

PERSONNE    ANNEE       MOIS 
----------- ----------- ----
415         2001        1
404         1999        1
415         2001        7
404         2001        4
418         2000        1

C'est bien évidemment le résultat attendu !

1-2-2. Dans les prédicats ALL, ANY(SOME)▲

L'opérateur IN que nous venons de voir ne permet qu'une comparaison avec une stricte égalité.
Il arrive que l'on soit confronté au cas où l'on souhaite que le critère de comparaison des deux ensembles soit une inégalité. Par exemple, pour trouver une valeur supérieure ou égale à toutes les valeurs d'un ensemble donné.
Ceci est possible à l'aide des opérateurs ALL et ANY (l'opérateur SOME étant un autre nom de l'opérateur ANY).

Les opérateurs ANY (ou SOME) et ALL permettent de comparer des ensembles de valeurs de manière globale.
Voici les conditions d'utilisation des opérateurs ALL et ANY (ou SOME) :

Sélectionnez

ALL demande une comparaison à toutes les valeurs pour que le prédicat soit vrai 
ANY (ou SOME qui est un synonyme) est vrai si, au moins une valeur de l'ensemble répond vrai à la comparaison.
Le critère de comparaison devant être, de préférence, une inégalité (>, >=, <, <=, <>).

Notons par exemple que <> ALL correspondons au NOT IN.

Plongeons-nous dans le quotidien de notre hôtelier, qui, cette fois-ci, doit héberger une équipe de football! De fait, ce dernier cherche à savoir quel est l'étage qui permet de coucher le maximum de personnes. En fait, son but inavoué est de circonscrire les débordements qui pourraient intervenir lors de la troisième mi-temps afin d'épargner le sommeil des autres clients, et par conséquent il envisage de loger tout ce petit monde au même étage avec à chaque bout du corridor un vigile.

La première idée qui vient à l'esprit pour tenter ce calcul est d'imbriquer deux calculs d'agrégation : obtenir le maximum de la somme des couchages par étage. Or une telle formulation :

Exemple 20 :

Sélectionnez

SELECT MAX(SUM(CHB_COUCHAGE)), CHB_ETAGE
FROM   T_CHAMBRE
GROUP  BY CHB_ETAGE

Sélectionnez

Message d'erreur :
Impossible d'exécuter une fonction d'agrégation 
sur une expression comportant un agrégat.

N'est pas possible en SQL.

En revanche, il est facile de calculer le volume des couchages par étage :

Exemple 21 :

Sélectionnez

SELECT SUM(CHB_COUCHAGE) AS COUCHAGE, CHB_ETAGE
FROM   T_CHAMBRE
GROUP  BY CHB_ETAGE

Sélectionnez

COUCHAGE    CHB_ETAGE 
----------- --------- 
23          1er
22          2e 
9           RDC

On voit d'ailleurs assez facilement quel est l'étage permettant le couchage le plus important.

En fin de compte, la solution à ce problème est sous vos yeux. Quelle est la particularité du chiffre 23, par rapport à la colonne COUCHAGE ?
D'en être le maximum, on est d'accord, mais encore, si nous devions comparer 23 avec tous les autres chiffres, quel critère pourrions-nous retenir ?
Ça y est, vous êtes sur la voieb! Oui, c'est bien cela 23 est supérieur ou égal à chacun des nombres 23, 22 et 9 !

Dès lors l'opérateur ALL venant à notre rescousse, va nous tirer de cet embarras :

Exemple 22 :

Sélectionnez

SELECT CHB_ETAGE 
FROM   T_CHAMBRE 
GROUP  BY CHB_ETAGE
HAVING SUM(CHB_COUCHAGE) >= ALL (SELECT SUM(CHB_COUCHAGE)
                                 FROM   T_CHAMBRE
                                 GROUP  BY CHB_ETAGE)

Sélectionnez

CHB_ETAGE 
--------- 
1er

Nous devons comprendre que, des deux ensembles distincts possédant les mêmes valeurs, nous cherchons à obtenir l'élément qui est au moins aussi fort que tous les éléments figurant dans l'autre ensemble. Autrement dit nous cherchons à savoir quel est l'étage dont le total de places de couchage est supérieur ou égal à tous les totaux de places de couchage de tous les étages !

Avec l'opérateur ANY, nous pouvons obtenir différents enseignements. D'abord nous pouvons obtenir le complément à la requête précédente en faisant :

Exemple 23 :

Sélectionnez

SELECT CHB_ETAGE 
FROM   T_CHAMBRE 
GROUP  BY CHB_ETAGE
HAVING SUM(CHB_COUCHAGE) < ANY (SELECT SUM(CHB_COUCHAGE)
                                FROM   T_CHAMBRE
                                GROUP  BY CHB_ETAGE)

Sélectionnez

CHB_ETAGE 
--------- 
2e
RDC

Il suffit d'utiliser le critère de comparaison strictement complémentaire. Cela correspond à demander tous les étages sauf celui de capacité maximale.

En outre si nous voulons tous les étages sauf celui dont le couchage est le plus petit, nous pouvons faire :

Exemple 24 :

Sélectionnez

SELECT CHB_ETAGE 
FROM   T_CHAMBRE 
GROUP  BY CHB_ETAGE
HAVING SUM(CHB_COUCHAGE) > ANY (SELECT SUM(CHB_COUCHAGE)
                                FROM   T_CHAMBRE
                                GROUP  BY CHB_ETAGE)

Sélectionnez

CHB_ETAGE 
--------- 
1er
2e

1-3. sous-requêtes comparant une ligne (prédicat MATCH)▲

Bien que rarement implémenté, car reposant sur la technique de la ligne valuée, le prédicat MATCH permet de comparer un ensemble de valeur de ligne à un ensemble de lignes retourné par une sous-requête. La comparaison s'effectue globalement pour tous les éléments de la ligne testée par rapport aux valeurs des colonnes.

La syntaxe de l'opérateur MATCH est la suivante :

Sélectionnez

<ligne> MATCH [UNIQUE] [SIMPLE | PARTIAL | FULL] <requête>

Sélectionnez `<ligne>`	Sélectionnez `ROW(valeur1, valeur2, ... , valeurN)`
Sélectionnez `<requête>`	Sélectionnez `(SELECT colonne1, colonne2, ... colonneN FROM ...)`

Les conditions de vérification d'un tel prédicat sont les suivantes :

Sélectionnez `<ligne> MATCH <requête> ou (équivalent) <ligne> MATCH SIMPLE <requête>`	Sélectionnez `vrai, si : - soit une des colonnes de <ligne> contient un marqueur NULL - soit <ligne> ne contient aucun marqueur NULL et qu'au moins une ligne de <ligne> correspond exactement à <ligne>`
Sélectionnez `<ligne> MATCH UNIQUE <requête> ou (équivalent) <ligne> MATCH UNIQUE SIMPLE <requête>`	Sélectionnez `vrai, si : - soit une des colonnes de <ligne> contient un marqueur NULL - soit <ligne> ne contient aucun marqueur NULL et que <ligne> contient une unique ligne correspondant exactement à <ligne>`
Sélectionnez `<ligne> MATCH PARTIAL <requête>`	Sélectionnez `vrai, si : - soit toutes les colonnes de <ligne> contiennent des marqueurs NULL - soit toutes les colonnes non NULL de <ligne> correspondent au moins à une ligne de <requête> ayant exactement les valeurs requises`
Sélectionnez `<ligne> MATCH UNIQUE PARTIAL <requête>`	Sélectionnez `vrai, si : - soit toutes les colonnes de <ligne> contiennent des marqueurs NULL - soit toutes les colonnes non NULL de <ligne> correspondent à une unique ligne de <requête> ayant exactement les valeurs requises`
Sélectionnez `<ligne> MATCH FULL <requête>`	Sélectionnez `vrai, si : - soit toutes les colonnes de <ligne> contiennent des marqueurs NULL - soit aucune des colonnes de <ligne> ne possède de marqueur NULL et que les valeurs de <ligne> correspondent au moins à une ligne de <requête> ayant exactement les valeurs requises`
Sélectionnez `<ligne> MATCH UNIQUE FULL <requête>`	Sélectionnez `vrai, si : - soit toutes les colonnes de <ligne> contiennent des marqueurs NULL - soit aucune des colonnes de <ligne> ne possède de marqueur NULL et que les valeurs de <ligne> correspondent à une unique ligne de <requête> ayant exactement les valeurs requises`

Un petit exemple étant le bienvenu, cherchons à savoir quels sont les mois et les années pour lesquels on n'a pas eût un maximum de personne dans une chambre avec réservations préalables :

Exemple 25 :

Sélectionnez

SELECT DISTINCT EXTRACT(YEAR FROM PLN_JOUR) AS ANNEE,
                EXTRACT(MONTH FROM PLN_JOUR) AS MOIS, 
FROM TJ_CHB_PLN_CLI 
WHERE NOT ROW(CHB_PLN_CLI_NB_PERS, CHB_PLN_CLI_RESERVE)
      MATCH FULL (SELECT MAX(CHB_PLN_CLI_NB_PERS), 1
                  FROM TJ_CHB_PLN_CLI)
ORDER BY ANNEE , MOIS

Sélectionnez

ANNEE       MOIS 
----------- ----
1999        2

Elle est équivalente à :

Exemple 26 :

Sélectionnez

SELECT DISTINCT EXTRACT(YEAR FROM PLN_JOUR) AS ANNEE,
                EXTRACT(MONTH FROM PLN_JOUR) AS MOIS
FROM   TJ_CHB_PLN_CLI T1
WHERE  NOT EXISTS (SELECT DISTINCT EXTRACT(YEAR FROM PLN_JOUR) AS ANNEE,
                          EXTRACT(MONTH FROM PLN_JOUR) AS MOIS 
                   FROM   TJ_CHB_PLN_CLI
                   WHERE  (CHB_PLN_CLI_NB_PERS = (SELECT MAX(CHB_PLN_CLI_NB_PERS)
                                                  FROM   TJ_CHB_PLN_CLI)
                     AND  CHB_PLN_CLI_RESERVE = 1)
                     AND  EXTRACT(YEAR FROM PLN_JOUR) = EXTRACT(YEAR FROM T1.PLN_JOUR)
                     AND  EXTRACT(MONTH FROM PLN_JOUR) = EXTRACT(MONTH FROM T1.PLN_JOUR))
ORDER BY ANNEE , MOIS

Sélectionnez

ANNEE       MOIS 
----------- ----
1999        2

parce qu'elle ne possède pas de valeurs NULL lors des calculs.

On voit bien que le prédicat MATCH propose une écriture plus élégante pour des requêtes complexes d'évaluation de lignes valuées avec différentes correspondances. Cependant il se fait encore rare dans les SGBDR !

1-4. Sous-requêtes renvoyant une table▲

N'importe quelle requête est capable de renvoyer une table, car un résultat de requête est bien une table. C'est l'essence même de la fonction d'une requête.
Or, où place-t-on une table dans une requête? Dans la clause FROM.
Autrement dit il est possible de placer une sous-requête dans la clause FROM de n'importe quelle requête à la place d'un nom de table !
Nous verrons aussi qu'il est possible de placer une sous-requête dans des prédicats très particuliers de SQL 3, à l'aide des opérateurs FOR ALL, FOR ANY et FOR SOME.

Continuons notre recherche entamée précédemment. Notre hôtelier voudrait bien éviter aux autres clients les rituelles batailles de polochons qui suivent les matchs et pénalisent le sommeil du juste.
Il cherche donc à savoir si au moins un étage de son hôtel permet de coucher les 24 personnes qui composent cette équipe (joueurs, remplaçants, entraîneurs, etc.). Il voudrait donc savoir quel est le maximum de la somme des couchages des étages.

Nous avions vu que le calcul de la totalité des places de couchage par étage est assez aisé :

Exemple 27 :

Sélectionnez

SELECT SUM(CHB_COUCHAGE) AS COUCHAGE 
FROM   T_CHAMBRE
GROUP  BY CHB_ETAGE

Sélectionnez

COUCHAGE 
----------- 
23
22
9

De ce résultat nous pourrions extraire le maximum. Il suffirait de reprendre le résultat de cette requête, et de faire :

Exemple 28 :

Sélectionnez

SELECT MAX(COUCHAGE) AS MAX_COUCHAGE
FROM (COUCHAGE 
      ----------- 
      23
      22
      9)

Sélectionnez

MAX_COUCHAGE
------------
23

En remplaçant le résultat par la requête de l'exemple 25, nous obtenons :

Exemple 29 :

Sélectionnez

SELECT MAX(COUCHAGE) AS MAX_COUCHAGE
FROM (SELECT SUM(CHB_COUCHAGE) AS COUCHAGE 
      FROM   T_CHAMBRE
      GROUP  BY CHB_ETAGE) T

Sélectionnez

MAX_COUCHAGE
------------
23

Qui est le résultat attendu !

C'est moins que la composition de l'équipe, mais je vous rassure, notre hôtelier, qui à du métier, a finalement eût l'idée de rajouter dans la plus grande chambre, un lit d'enfant.

Lorsque l'on place une sous-requête en tant que table dans la clause FROM d'une requête, il faut pouvoir donner un nom à cette table ne serait-ce que parce qu'elle peut être jointe aux autres. Il convient donc de lui donner systématiquement un surnom.

Ici le surnom choisi a été la lettre T, comme Table !

1-5. Sous-requêtes vide, non vide▲

Le principe est le suivant : si la sous-requête renvoie un résultat quelconque, alors le prédicat vaut vrai. Si la sous-requête ne renvoie aucune ligne, le prédicat vaut faux. SQL 2 a prévu deux prédicats spécialisés qui sont EXISTS et UNIQUE.

1-5-1. Dans le prédicat EXISTS▲

Le prédicat EXISTS permet de tester l'existence ou l'absence de données dans la sous-requête.
Si la sous-requête renvoie au moins une ligne, même remplie de marqueurs NULL, le prédicat est vrai. Dans le cas contraire le prédicat à valeur fausse, y compris si l'évaluation à la valeur UNKNOWN (dans le cas d'une comparaison avec un marqueur NULL).
Le prédicat EXISTS peut être combiné avec l'opérateur de négation NOT.

Nous voulons obtenir le total du couchage de l'hôtel, toutes chambres confondues, à condition qu'il y ait au moins une chambre dotée d'un couchage pour au moins trois personnes :

Exemple 30 :

Sélectionnez

SELECT SUM(CHB_COUCHAGE) AS TOTAL_COUCHAGE
FROM   T_CHAMBRE
WHERE  EXISTS (SELECT *
               FROM   T_CHAMBRE
               WHERE  CHB_COUCHAGE >= 3)

Sélectionnez

TOTAL_COUCHAGE
--------------
54

En fait l'utilisation d'un prédicat EXISTS n'a pas grand sens sans l'utilisation des sous-requêtes corrélées.

IMPORTANT :

le prédicat EXISTS est en général plus rapide que le prédicat IN ;
le comportement du prédicat EXISTS face au retour de marqueurs NULL renvoyés par la sous-requête diffère sensiblement d'un SGBDR à l'autre. En particulier DB2 et Oracle ne sont pas conforme à la norme SQL2 ;
le prédicat EXISTS n'a aucun intérêt sans une sous-requête corrélée ;
il convient de toujours utiliser l'étoile comme unique contenu de la clause SELECT de la sous-requête, car dans ce cas particulier, le moteur SQL choisit une constante la plus adaptée à un traitement performant.

1-5-2. Dans le prédicat UNIQUE▲

UNIQUE est un raffinement du prédicat EXISTS. Cette extension du fonctionnement du prédicat EXISTS porte sur le doublonnage des lignes renvoyées. En effet, UNIQUE exige qu'il n'y ait aucun doublon dans les lignes renvoyées par la sous-requête. En d'autres termes UNIQUE vaut faux si au moins deux lignes renvoyées par la sous-requête comportent les mêmes données.

Nous voulons obtenir le total du couchage de l'hôtel, toutes chambres confondues, à condition qu'il n'y ait qu'une seule chambre dotée d'un couchage pour exactement 5 personnes :

Exemple 31 :

Sélectionnez

SELECT SUM(CHB_COUCHAGE) AS TOTAL_COUCHAGE
FROM   T_CHAMBRE
WHERE  UNIQUE (SELECT CHB_COUCHAGE
               FROM   T_CHAMBRE
               WHERE  CHB_COUCHAGE = 3)

Sélectionnez

TOTAL_COUCHAGE
--------------

Il existe deux chambres (6 et 17, d'ID 6 et 16) dotées de 5 places de couchage. La sous-requête renvoie donc deux lignes possédant des données identiques. UNIQUE vaut alors faux, aucun résultat n'est donc renvoyé.
En fait l'utilisation d'un prédicat EXISTS n'a pas grand sens sans l'utilisation des sous-requêtes corrélées.

IMPORTANT

Le prédicat UNIQUE est en général beaucoup plus rapide que certaines constructions telles que le prédicat NOT EXIST associé au SELECT DISTINCT ou encore une sous-requête avec un filtre HAVING pour contrer les lignes dédoublées.
Le comportement du prédicat UNIQUE face au retour de marqueurs NULL renvoyés par la sous-requête peut différer sensiblement d'un SGBDR à l'autre.
Le prédicat UNIQUE n'a aucun intérêt sans une sous-requête corrélée.
Contrairement à EXISTS, il convient de toujours spécifier les colonnes visées dans la clause SELECT.

NOTA : ce prédicat est hélas rarement implémenté dans les SGBDR… Dommage !

2. Les sous-requêtes corrélées▲

Vous trouverez des compléments d'information sur le sujet aux pages 154 à 180 de l'ouvrage « SQL », collection « La Référence », Campus Press éditeur.

Nous allons maintenant rajouter une couche à la puissance des sous-requêtes en étudiant comment on peut corréler une sous-requête à la requête au sein de laquelle elle est imbriquée.

NOTA : On trouve parfois les mots sous-requêtes « imbriquées », sous-requêtes « liées » pour désigner la technique des sous-requêtes corrélées.

En fait une sous-requête corrélée est une sous-requête qui s'exécute pour chaque ligne de la requête principale et non une fois pour toutes. Pour arriver à un tel résultat, il suffit de faire varier une condition (en général un prédicat) en rappelant dans la sous-requête la valeur d'une colonne de la requête principale.

Il est plus facile de comprendre cette notion à l'aide d'une comparaison à un langage procédural, mais aussi en visualisant graphiquement une telle imbrication.

Le principe procédural est simple. Les sous-requêtes corrélées correspondent en fait à des boucles imbriquées. Voici un exemple de procédure permettant de lire un fichier et de scruter chaque ligne du fichier à la recherche d'un mot précis :

Sélectionnez

Procedure RechercheMot (LeMot : string, leFichier : File)
Var
   NumLigne     integer;
   Ligne        string
   NumCar       integer;
   LongueurMot  integer;
endVar
Proc
   LeFichier.open()
   LongueurMot := LeMot.length
   FOR NumLigne from 1 to LeFichier.Length()
   |  Ligne := LeFichier.readLine()
   |  FOR NumCar from 1 to Ligne.length() - LongueurMot + 1
   |  |   if leMot = Ligne.substring(NumCar, LongueurMot)
   |  |   then
   |  |      screen.prompt("Mot " + LeMot + " trouvé à la ligne : " + string(NumLigne))
   |  |   endif
   |  ENDFOR
   ENDFOR
   LeFichier.close()
endProc

Nous voyons bien que les deux boucles sont imbriquées, c'est-à-dire qu'il faut recommencer à rechercher l'occurrence du mot dans chacune des lignes du fichier que l'on « balaye ». C'est exactement comme cela que fonctionnent les requêtes imbriquées. La requête principale donne à la sous-requête de nouvelles conditions d'exécution à chaque fois que la requête principale trouve une ligne correspondant à ses propres filtres.

De manière graphique, le principe d'une sous-requête est le suivant :

Cherchons donc à trouver les clients qui ont un prénom en commun. Autrement dit pour qu'un client soit sélectionné, il faut qu'un autre client porte le même prénom. Nous appellerons cela l'homoprenymie !

Une première idée qui vient immédiatement à l'esprit consiste à faire :

Exemple 32 :

Sélectionnez

SELECT CLI_ID, CLI_NOM, CLI_PRENOM 
FROM   T_CLIENT
WHERE  CLI_PRENOM IN (SELECT CLI_PRENOM 
                      FROM   T_CLIENT)

Sélectionnez

CLI_ID  CLI_NOM     CLI_PRENOM
------- ----------- ----------
1       DUPONT      Alain
2       MARTIN      Marc
3       BOUVIER     Alain
4       DUBOIS      Paul
5       DREYFUS     Jean
6       FAURE       Alain
7       LACOMBE     Paul
8       DUHAMEL     Evelyne
9       BOYER       Martine
10      MARTIN      Martin
...

La réponse est le contenu complet de la table des clients. Tous les clients auraient-ils un homoprényme ? Non, bien entendu, mais la faute que nous avons commise est simple : un client porte bien évidemment le même prénom que lui-même !

C'est pourquoi nous devons retirer dans la sous-requête la référence au client que l'on scrute dans la requête principale.
La requête propre est donc celle-ci :

Exemple 33 :

Sélectionnez

SELECT CLI_ID, CLI_NOM, CLI_PRENOM 
FROM   T_CLIENT C1
WHERE  CLI_PRENOM IN (SELECT CLI_PRENOM 
                      FROM   T_CLIENT C2
                      WHERE  C1.CLI_ID <> C2.CLI_ID)

Sélectionnez

CLI_ID  CLI_NOM     CLI_PRENOM
------- ----------- ----------
3       BOUVIER     Alain
1       DUPONT      Alain
6       FAURE       Alain
25      LE GUILLARD Alain
69      LEI         Alain
65      NOCENTINI   Alain
77      ROUSSILLON  Alain
71      BOURA       André
13      PHILIPPE    André
38      ALBERT      Christian
32      LEBAILLIF   Christian
...

Soit 53 occurrences.

Ici, la corrélation entre la requête principale et la sous-requête se fait dans la clause WHERE et porte sur le fait que l'identifiant des clients scrutés dans la sous-requête ne doit pas être le même que dans la requête principale : C1.CLI_ID <> C2.CLI_ID.
Notons que pour obtenir cette corrélation, il faut donner des surnoms à nos tables.

Plus élégamment, nous pouvons écrire cette requête à l'aide d'une clause EXISTS qui sera notablement plus performante :

Exemple 34 :

Sélectionnez

SELECT CLI_ID, CLI_NOM, CLI_PRENOM 
FROM   T_CLIENT C1
WHERE  EXISTS (SELECT * 
               FROM   T_CLIENT C2
               WHERE  C1.CLI_ID <> C2.CLI_ID
                  AND C1.CLI_PRENOM = C2.CLI_PRENOM)

Sélectionnez

CLI_ID  CLI_NOM     CLI_PRENOM
------- ----------- ----------
3       BOUVIER     Alain
1       DUPONT      Alain
6       FAURE       Alain
25      LE GUILLARD Alain
69      LEI         Alain
65      NOCENTINI   Alain
77      ROUSSILLON  Alain
71      BOURA       André
13      PHILIPPE    André
38      ALBERT      Christian
32      LEBAILLIF   Christian
...

Ce qui, bien évidemment donne le même résultat.

Si maintenant nous exigeons qu'il n'y ait qu'un seul homoprényme de nos clients, alors le prédicat UNIQUE vient à notre secours :

Exemple 35 :

Sélectionnez

SELECT CLI_ID, CLI_NOM, CLI_PRENOM 
FROM   T_CLIENT C1
WHERE  UNIQUE (SELECT CLI_PRENOM 
               FROM   T_CLIENT C2
               WHERE  C1.CLI_ID <> C2.CLI_ID
                  AND C1.CLI_PRENOM = C2.CLI_PRENOM)

Sélectionnez

CLI_ID  CLI_NOM     CLI_PRENOM
------- ----------- ----------
13      PHILIPPE    André
71      BOURA       André
32      LEBAILLIF   Christian
38      ALBERT      Christian
90      JOLY        Christophe
87      BERTRAND    Christophe
24      CHTCHEPINE  Dominique
35      PICOT       Dominique
51      DAVID       Jacqueline
56      MOURGUES    Jacqueline
43      MONTEIL     Jean
5       DREYFUS     Jean
53      BERGER      Jean-Pierre
75      MARTIN      Jean-Pierre
66      LAYANI      Lionel
27      LECUYER     Lionel
18      FAYOLLE     Olivier
45      ORELL       Olivier
72      CARDONA     Philippe
41      PLATONOFF   Philippe

Notons qu'ils ne sont plus que 20 occurrences, et non plus 53, soit 10 paires de clients homoprénymes. En fait un nombre pair était bien évidemment attendu !

Nous n'avons présenté ici que quelques brefs exemples afin de faire toucher du doigt le concept de la corrélation des requêtes.

Bien entendu les sous-requêtes corrélées peuvent l'être dans toutes les clauses de l'ordre SELECT.

Nous pourrions passer en revue les principaux opérateurs combinés aux principales clauses capables de recevoir une sous-requête, mais cela deviendrait vite rébarbatif. C'est pourquoi je vous invite à lire les exemples que l'on trouve dans les différents papiers de ce site, tel que celui consacré à la division relationnelle, ou encore de lire la nombreuse littérature sur le sujet et en particulier le chapitre 8 de mon livre « SQL, La référence » qui donne plus de 120 exemples dont une bonne partie sont des requêtes corrélées.

3. Sous-requêtes ou jointures ?▲

Disons le tout de suite, il est faux de croire que toutes les sous-requêtes peuvent trouver leur équivalent sous forme de jointures, même si certains SGBDR par faute d'avoir implémenté les sous-requêtes ont codés quelques « trucs » afin de pallier leurs défauts.

Un premier exemple d'une simplicité extrême va vous donner la mesure de la complexité de la chose.
Soit les tables suivantes, et leur jeu de données associés :

Sélectionnez

CREATE TABLE TAB1 (COL1 INT)
CREATE TABLE TAB2 (COL2 INT)

Sélectionnez

INSERT INTO TAB1 VALUES (1)
INSERT INTO TAB1 VALUES (1)
INSERT INTO TAB1 VALUES (2)
INSERT INTO TAB2 VALUES (1)
INSERT INTO TAB2 VALUES (2)
INSERT INTO TAB2 VALUES (2)

Quel est donc l'équivalent absolu, sans sous-requête et exprimé à l'aide uniquement de jointure, de la requête :

Exemple 36 :

Sélectionnez

SELECT TAB1.COL1 AS COL
FROM   TAB1
WHERE  TAB1.COL1 IN (SELECT TAB2.COL2
                     FROM   TAB2)

Sélectionnez

COL 
---
1
1
2

Essayons quelques formulations.

Une jointure interne simple :

Exemple 37 :

Sélectionnez

SELECT TAB1.COL1 AS COL
FROM   TAB1
       INNER JOIN TAB2
             ON TAB1.COL1 = TAB2.COL2

Sélectionnez

COL 
---
1
1
2
2

PERDU : il y a une valeur 2 en trop !

Rajoutons le dédoublonnage :

Exemple 38 :

Sélectionnez

SELECT DISTINCT TAB1.COL1 AS COL
FROM   TAB1
       INNER JOIN TAB2
             ON TAB1.COL1 = TAB2.COL2

Sélectionnez

COL 
---
1
2

PERDU : il y a une valeur 1 en moins !

Voici quelques exemples de sous-requêtes ne possédant aucun équivalent sous forme de jointure :

Exemples 39, 40 et 41 :

Sélectionnez

SELECT * 
FROM   TABLE_1
WHERE  COLONNE_1 + 3 = (SELECT MAX(COLONNE_2) FROM TABLE_2)

Sélectionnez

SELECT TABLE_1.*
FROM   TABLE_1
       INNER JOIN (SELECT MAX(COLONNE_1) AS MAX_COL_1 FROM TABLE_2) TABLE_2
             ON TABLE_1.COLONNE_1 + 4 = TABLE_2.COLONNE_1

Sélectionnez

UPDATE TABLE_1 
SET COLONNE_1 = (SELECT COLONNE_1 
                 FROM TABLE_2)

Je laisse à votre sagacité le soin de rechercher comment exprimer ces requêtes sans l'imbrication de sous-requêtes. Je remercie au passage Peter GULUTZAN de ces exemples tirés de son livre « SQL-99 complete really » publié chez R&D books.

Le grand perdant de cette affaire est MySQL incapable de la moindre sous-requête et donc inapte à de nombreuses extractions de base du SQL !

4. Résumé▲

Partie en construction :

Voici les différences entre les moteurs des bases de données :

sous-requêtes	Paradox	SQLServer	MySQL
dans le SELECT	Oui	Oui	Non
dans le FROM	Non	Oui	Non
dans les filtres WHERE et HAVING	Oui	Oui	Non
opérateur IN	Oui	Oui	Non
opérateurs ALL et ANY (ou SOME)	Oui	Oui	Non
opérateur MATCH	Non	Non	Non
opérateur EXISTS	Oui	Oui	Non
opérateur UNIQUE	Non	Non	Non
corrélées dans le SELECT	Oui	Oui	Non
corrélées dans le FROM	Non	Oui	Non
corrélées dans les filtres WHERE et HAVING	Oui	Oui	Non
corrélées dans l'opérateur IN	Oui	Oui	Non
corrélées dans les opérateurs ALL / ANY (SOME)	Oui	Oui	Non
corrélées dans l'opérateur EXISTS	Oui	Oui	Non
corrélées dans l'opérateur UNIQUE	Non	Non	Non
corrélées dans l'opérateur MATCH	Non	Non	Non

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants :

Copyright © 2003 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.