CHAPITRE II : TRAITEMENT ET ORGANISATION
II. 2.2.2.5. Jointure de tables sans clause WHERE
II.6. Sous-requêtes et requêtes imbriquées
Dans le traitement et l’organisation des grandes bases de données, il est très fréquent d’être confronté à problèmes nécessitant l’élaboration des sous requêtes et les requêtes imbriquées.
Une sous-requête est une requête spécifiée dans la clause WHERE d’une requête principale. Tandis que construire des requêtes imbriquées c’est utiliser les résultats d'une requête pour faire une autre requête. Les résultats de cette même requête sont utilisés pour réaliser une autre requête. Et ainsi de suite.
Les sous-requêtes et le requêtes imbriquées sont spécifiées généralement dans la clause WHERE ou la clause HAVING.
Pour introduire les notions de sous-requêtes et de requêtes, partons d’abord d’un cas simple.
Supposons un épicier-vendeur de produits exotiques disposant d’une base de données constituée de deux tables: la table ACHATS_PRODUITS qui fournit la description de tous les achats effectués par l’épicier auprès de ses fournisseurs (ex : numéro achat, code produit, nom produit, prix d’achat unitaire, quantité achetée, montant des achats, date achat) et la table VENTES_PRODUITS qui contient les informations sur les ventes au clients de chaque produit (numéro vente, code produit, nom produit, prix de vente unitaire, quantité vendue, montant des vente, date vente, etc.).
Intéressons-nous maintenant au produit « palme » et supposons que son prix-fournisseur (c’est à dire prix d’achat) est de 10 à l’unité. En tant que gestionnaire de base de données, cet épicier vous fait la requête suivante : « Afficher les informations sur les ventes de tous les produits dont le prix de vente à l’unité est supérieur au prix fournisseur du palme »
Dès lors, puisque vous connaissez à priori (de tête) le prix fournisseur du palme, alors vous écrirez la requête suivante :
PROC SQL ; SELECT *
FROM VENTES_PRODUITS WHERE PRIX_UNIT_VENTE>10 ;
Dans cette requête, on sélectionne simplement toutes les informations sur toutes les ventes de la table VENTES_PRODUITS pour lesquelles les prix unitaires sont supérieurs à 10. Dans cette formulation, il n’y a pas besoin de sous-requêtes ni de requêtes imbriquées car le prix d’achat du palme est directement connu (10).
Par contre, lorsque le prix d’achat du palme n’était pas connu d’avance, il faut alors interroger la table ACHATS_PRODUITS pour connaitre le prix unitaire, ensuite utiliser cette valeur dans la clause WHERE de la requête principale.
II.6 .1. Elaboration d’une sous -requête
En considérant l’exemple ci-dessous, pour pouvoir à la répondre à la requête de l’épicier « Afficher les informations sur les ventes de tous les produits dont le prix de vente à l’unité est supérieur au prix fournisseur du palme », nous allons élaborer une sous-requête dans la première étape.
Ainsi, lorsque le prix d’achat du palme n’est pas connu d’avance, on adopte la formulation suivante :
PROC SQL ; SELECT *
FROM VENTES_PRODUITS WHERE PRIX_UNIT_VENTE GT (SELECT PRIX_UNIT_ACHAT FROM ACHATS_PRODUITS
WHERE UPCASE(NOM_PRODUIT) LIKE "%PALME%" /* formulation moins risquée que NOM_PRODUIT= "Palme", possibilité de différence d’orthographe */
; QUIT ;
Ainsi à la différence de la première requête la valeur 10 est remplacée par une sous requête : SELECT PRIX_UNIT_ACHAT FROM ACHATS_PRODUITS WHERE
UPCASE(NOM_PRODUIT) LIKE "%PALME%".
Remarque :
Utiliser une sous-requête c'est définir une requête pour traduire la condition spécifiée la clause WHERE de la principale requête.
Il faut noter qu’il est possible que la sous requête renvoie plusieurs valeurs au lieu d’une valeur unique. Par exemple s’il se trouve que la table contient plusieurs variétés
de palmes enregistrées avec des noms différents, alors la condition WHERE UPCASE(NOM_PRODUIT) LIKE "%PALME%" renverra plusieurs valeurs. Dès lors échouera. C’est pourquoi, dans une telle éventualité, on ajoute des opérateurs logiques spécifiques aux sous-ensembles dont certaines sont décrites ci-dessous : ANY : qui signifie qu’au moins un des éléments du sous-ensemble de valeurs obtenu à partir d'une sous-requête doit satisfaire à une condition donnée : ex :
……
WHERE POPULATION > ANY (SELECT POPULATION FROM COUNTRIES)
ALL : qui indique que toute les valeurs obtenues à partir d'une sous-requête doit satisfaire à une condition donnée. Ex :
……
WHERE POPULATION > ALL (SELECT POPULATION FROM COUNTRIES)
BETWEEN-AND : qui indique les valeurs renvoyées par la sous-requête doivent être comprise entre deux valeurs particulières v1 et v2 de manière inclusive. Ex :
……
WHERE POPULATION 100000 AND 150000
EXISTS : qui indique qu’il existe une valeur dans le sous-ensemble renvoyé par la sous-requête. Ex :
……
WHERE EXISTS(SELECT * FROM ACHATS_PRODUITS
WHERE UPCASE(NOM_PRODUIT) LIKE "%PALME%")
IN : qui indique que la valeur doit figurer dans la liste des éléments fournis par la sous-requête. Ex :
…
Ou
……
WHERE NOMBRE IN (1, 2 , 8 , 20)
IS NULL ou (IS MISSING) : qui indique la valeur est manquante. Ex :
……
WHERE POPULATION IS MISSING
……
WHERE POPULATION IS NULL
LIKE : qui indique que la valeur spécifiée doit ressembler à un mot clé (voir la section II.4.1 pour plus de détails sur la fonction like. Ex :
……
WHERE CONTINENT LIKE 'A%'
CONTAINS : qui indique que les valeurs des chaînes de caractère renvoyée doit contenir le mot en question. Ex :
…
WHERE CONTINENT CONTAINS 'America'
NB : Tous ces opérateurs peuvent précédés de l’opérateur NOT pour traduire la négative.
II.6.2. Structure des requêtes imbriquées
L’exemple ci-dessous illustre la structure générale des requêtes imbriquées : PROC SQL;
SELECT * FROM TABLE1 WHERE COUNTRY IN
(SELECT COUNTRY FROM TABLE2 WHERE TABLE2.COUNTRY IN
(SELECT NAME FROM TABLE3.COUNTRIES WHERE TABLE3.CONTINENT='AFRICA'));
QUIT;
On constate que dans chaque clause WHERE, le critère est le résultat d’une requête, qui, elle-même, est définie de sorte que sa clause WHERE est définie à partir d’une requête.
Au final selon les règles de bonnes pratiques, il faut toujours penser à utiliser JOIN ou une sous-requête quand on travaille sur plusieurs tables à la fois. Il arrive d’ailleurs très souvent qu'on combine les deux dans une même requête. L’exemple ci-dessous en est une illustration.
PROC SQL ;
SELECT A.CITY , A.STATE,
A.LATITUDE , A.LONGITUDE , B.CITY , B.STATE,
B.LATITUDE , B.LONGITUDE,
SQRT(((B.LATITUDE-A.LATITUDE)**2) + ((B.LONGITUDE-A.LONGITUDE)**2)) AS DIST
FROM USCITYCOORDS A, USCITYCOORDS B
WHERE A.CITY NE B.CITY AND CALCULATED DIST =
(SELECT MIN(SQRT(((D.LATITUDE-C.LATITUDE)**2) +((D.LONGITUDE-C.LONGITUDE)**2)))
FROM MYTAB.USCITYCOORDS C, MYTAB.USCITYCOORDS D
WHERE C.CITY = A.CITY AND .STATE = A.STATE AND D.CITY NE C.CITY) ORDER BY A.CITY;
QUIT;