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...
NOTA : 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 :
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,74F) AS MOYENNE_DES_COMMANDES
FROM MaTable
ou
SELECT *
FROM MaTable
WHERE MaColonne =
(MOYENNE
--------
406,74F)
ou
SELECTCOUNT(*), MaColonne
FROM MaTable
GROUP BY MaColonne
HAVINGCOUNT(*) = (MOYENNE
--------
406,74 F)
ou
SELECT *
FROM MaTable T1
JOIN AutreTable T2
ON T1.colonne1 =
T2.colonne2 - (MOYENNE
--------
406,74 F)
Etc...
Une requête renvoyant une valeur unique peut être imbriquée partout ou une constante peut figurer...
SELECT *,
(SELECTAVG(colonne)
FROM UneTable) AS MOYENNE_DES_COMMANDES
FROM MaTable
ou
SELECT *
FROM MaTable
WHERE MaColonne =
(SELECTAVG(colonne)
FROM UneTable)
ou
SELECTCOUNT(*), MaColonne
FROM MaTable
GROUP BY MaColonne
HAVINGCOUNT(*) = (SELECTAVG(colonne)
FROM UneTable)
ou
SELECT *
FROM MaTable T1
JOIN AutreTable T2
ON
T1.colonne1 =
T2.colonne2 - (SELECTAVG(colonne)
FROM UneTable)
Une requête renvoyant des valeurs ou pas peut être imbriquée dans un prédicat EXISTS, UNIQUE et MATCH.
SELECT *
FROM MaTable
WHEREEXISTS
(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.
REMARQUE IMPORTANTE : 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
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) - (SELECTAVG(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
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 F 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'
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
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'
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 - (SELECTAVG(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'
Notre hôtelier désire savoir quels sont les mois pour lesquels le taux d'occupation de son hôtel à dépassé les 2/3. Calculer le taux d'occupation (c'est à dire le nombre de chambre occupé 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)
ORDERBY ANNEE, MOIS
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
SELECTCOUNT(*) * 30 * 0.6667 AS TAUX_OCCUPATION_MOYEN
FROM T_CHAMBRE
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)
HAVINGCOUNT(*) > 400.02
ORDERBY ANNEE, MOIS
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)
HAVINGCOUNT(*) > (SELECTCOUNT(*) * 30 * 0.6667
FROM T_CHAMBRE)
ORDERBY ANNEE, MOIS
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
SELECTDISTINCT 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 NOTIN (SELECTDISTINCT 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
SELECTDISTINCT H.CHB_ID
FROM T_CHAMBRE H
LEFTOUTERJOIN TJ_CHB_PLN_CLI J
ON H.CHB_ID = J.CHB_ID
LEFTOUTERJOIN 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
HAVINGCOUNT(C.CLI_ID) = 0
ORDERBY H.CHB_ID
CHB_ID
------
5
8
Second exemple :
Le gérant de l'hôtel voudrais 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
SELECTCOUNT (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)
Étendre le comptage à tous les mois de toutes les années n'est pas plus difficile :
Exemple 17
SELECTCOUNT (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)
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
SELECTCOUNT (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)
HAVINGCOUNT (CHB_PLN_CLI_NB_PERS) IN (404, 415, 418)
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
SELECTCOUNT (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)
HAVINGCOUNT (CHB_PLN_CLI_NB_PERS) IN (SELECTCOUNT (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))
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 ensmebles 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
SELECTMAX(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
SELECTSUM(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
HAVINGSUM(CHB_COUCHAGE) >= ALL (SELECTSUM(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
HAVINGSUM(CHB_COUCHAGE) < ANY (SELECTSUM(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
HAVINGSUM(CHB_COUCHAGE) > ANY (SELECTSUM(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
SELECTDISTINCT EXTRACT(YEAR FROM PLN_JOUR) AS ANNEE,
EXTRACT(MONTH FROM PLN_JOUR) AS MOIS,
FROM TJ_CHB_PLN_CLI
WHERENOTROW(CHB_PLN_CLI_NB_PERS, CHB_PLN_CLI_RESERVE)
MATCH FULL (SELECTMAX(CHB_PLN_CLI_NB_PERS), 1
FROM TJ_CHB_PLN_CLI)
ORDERBY ANNEE , MOIS
ANNEE MOIS
----------- ----
1999 2
Elle est équivalente à :
Exemple 26
SELECTDISTINCT EXTRACT(YEAR FROM PLN_JOUR) AS ANNEE,
EXTRACT(MONTH FROM PLN_JOUR) AS MOIS
FROM TJ_CHB_PLN_CLI T1
WHERENOTEXISTS (SELECTDISTINCT 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 = (SELECTMAX(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))
ORDERBY 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 matches 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...). 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
SELECTSUM(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
SELECTMAX(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
SELECTMAX(COUCHAGE) AS MAX_COUCHAGE
FROM (SELECTSUM(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...
ATTENTION : 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 à prévu deux prédicats spécillisé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 la cas d'un 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
SELECTSUM(CHB_COUCHAGE) AS TOTAL_COUCHAGE
FROM T_CHAMBRE
WHEREEXISTS (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
SELECTSUM(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 oue 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égament, 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
WHEREEXISTS (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 !
REMARQUE : 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 capable 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 relationelle, 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 palier 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 :
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
INNERJOIN 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
SELECTDISTINCT TAB1.COL1 AS COL
FROM TAB1
INNERJOIN 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 = (SELECTMAX(COLONNE_2) FROM TABLE_2)
SELECT TABLE_1.*
FROM TABLE_1
INNERJOIN (SELECTMAX(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é
partie en construction
Voici les différences entre les moteurs des bases de données :
