SQL

6. 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.

Préambule
1. Les sous requêtes
1.1. Sous-requêtes renvoyant une seule valeur
1.1.1. Dans la clause SELECT
1.1.2. Dans les filtres WHERE et HAVING
1.2. sous-requêtes renvoyant une liste ( Opérateurs IN, ALL et ANY(SOME) )
1.2.1. Dans le prédicat IN
1.2.2. Dans les prédicats ALL, ANY(SOME)
1.3. sous-requêtes comparant une ligne (prédicat MATCH)
1.4. Sous-requêtes renvoyant une table
1.5. Sous-requêtes vide, non vide
1.5.1. Dans le prédicat EXISTS
1.5.2. Dans le prédicat UNIQUE
2. Les sous-requêtes corrélées
3. Sous-requêtes ou jointures ?
4. Résumé


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

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 

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. 

CLI_PRENOM 
---------- 
Alain 
Marc 
Alain 
Paul 
Jean 
Alain 
Marcel 
Arsène 
André 
Daniel 
...
Une table d'une seule colonne, c'est à dire une liste. 

TIT_CODE CLI_NOM    CLI_PRENOM 
-------- ------------------------
M.       RAY        Yannick
Mme.     ROURE      Marie-Louise
M.       RECHUL     Jacques
M.       ROUSSILLON Alain
Une ligne d'une table. 

MOYENNE 
-------- 
406,74 E
Une table d'une seule ligne et d'une seule colonne, c'est à dire une valeur unique. 

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 

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é dans la clause FROM d'une autre requête. 

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.


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). 

SELECT *
FROM   Matable
WHERE  uneColonne IN
(
SELECT CLI_NOM
FROM MaTable
)


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é à une ligne valuée. 

SELECT *
FROM maTable
WHERE (valeur1, valeur2, valeur3)
MATCH
(SELECT TIT_CODE, CLI_NOM, 
CLI_PRENOM 
FROM T_CLIENT
WHERE CLI_NOM LIKE'R%'
)


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... 

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)


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. 

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.

warning 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 :


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


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 3ème ligne de la réponse. Nous pouvons l'obtenir en précisant la requête :

Exemple 2 :


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


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 :


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


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 :


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


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 :


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'


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 dûe 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 :


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'


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 :


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'


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'occurences de la table ou sont stockées les informations des réservations (TJ_CHB_PLN_CLI).

Exemple 8 :


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


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 côte mal taillée de 30 jours (référence comptable) :

Exemple 9 :


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


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 :


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(*) > 400.02
ORDER  BY ANNEE, MOIS


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 :


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


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 :


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


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 :


SELECT CHB_ID
FROM   T_CHAMBRE


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 :


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)


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 :


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


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 :


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)


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 :


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)


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 :


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)


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 :


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))


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) :

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 pourrait 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 :


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


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 :


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


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 ?
Ca y est, vous êtes sur la voie! 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 :


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)


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 :


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)


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 :


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)


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 toutes les éléments de la ligne testée par rapport aux valeurs des colonnes.

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

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


<ligne>


ROW(valeur1, valeur2, ... , valeurN)


<requête>


(SELECT colonne1, colonne2, ... colonneN 
 FROM ...)

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


<ligne> MATCH <requête>
ou (équivalent)
<ligne> MATCH SIMPLE <requête>


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>


<ligne> MATCH UNIQUE <requête>
ou (équivalent)
<ligne> MATCH UNIQUE SIMPLE <requête>


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>


<ligne> MATCH PARTIAL <requête>


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


<ligne> MATCH UNIQUE PARTIAL <requête>


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


<ligne> MATCH FULL <requête>


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


<ligne> MATCH UNIQUE FULL <requête>


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 :


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


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

Elle est équivalente à :

Exemple 26 :


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


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 compose 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 :


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


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

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

Exemple 28 :


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


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

Exemple 29 :


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


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.

warning 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 jointes aux autres. Il convient donc de lui donner systématiquement un surnom.
Ici le surnom choisit 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 le sous-requête ne renvoit 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és 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 3 personnes :

Exemple 30 :


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


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 comporte 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 :


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


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 comptre 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 techniques 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 toute. 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 :

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 apellerons cela l'homoprenymie !

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

Exemple 32 :


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


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 :


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)


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 occurences.

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 :


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)


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 :


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)


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 occurences, et non plus 53, soit 10 paires de clients homoprénymes. En fait un nombre pair était bien évidemment attendu !

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 :


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


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 :


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


COL 
---
1
1
2

Essayons quelques formulations.

Une jointure interne simple :

Exemple 37 :


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


COL 
---
1
1
2
2

PERDU : il y a une valeur 2 en trop !

Rajoutons le dédoublonnage :

Exemple 38 :


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


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 :


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


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


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 nombreuse extraction de base du SQL !


4. Résumé


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