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](../SQL/kitwarning.gif) |
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 (* ) & gt; 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 )
|
|
|
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
|
|
|
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)
|
|
|
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>
|
|
ROW (valeur1, valeur2, ... , valeurN)
|
|
|
(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](../SQL/kitwarning.gif) |
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)
|
|
|
Essayons quelques formulations.
Une jointure interne simple :
Exemple 37 :
SELECT TAB1.COL1 AS COL
FROM TAB1
INNER JOIN TAB2
ON TAB1.COL1 = TAB2.COL2
|
|
|
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
|
|
|
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 |