Oracle permet d’imbriquer les requêtes, c’est-à-dire de placer des requêtes dans les requêtes. Une requête imbriquée peut renvoyer trois types de résultats :
Le résultat d’une requête est dit scalaire s’il comporte une seule ligne et une seule colonne. Par exemple :
SELECT COUNT(*) FROM PRODUIT COUNT(*) 4
On peut placer dans une requête une sous-requête calculant un résultat scalaire. Un tel type de sous-requête se place soit comme une colonne supplémentaire, soit comme une valeur servant à évaluer des conditions (WHERE ou HAVING).
On peut ajouter une colonne dans une requête, et choisir comme valeurs pour cette colonne le résultat d’une requête. Ce type de requête est souvent une alternative à GROUP BY. Par exemple, la requête suivante nous renvoie, pour tout produit, le nombre de fournisseurs proposant ce produit :
SELECT nomprod, (SELECT COUNT(*) FROM PROPOSER PR WHERE PR.numprod = P.numprod) AS NB_FOURNISSEURS FROM PRODUIT P nomprod NB_FOURNISSEURS Bocal de cornichons 2 Tube de dentifrice 1 Flacon de lotion anti-escarres 1 Déodorant fraîcheur 96 heures 0
On peut construire une condition en utilisant le résultat d’une requête. Pour notre exemple, déclarons d’abord une vue contenant le nombre d’articles proposés par chaque fournisseur,
CREATE VIEW NB_PROD_PAR_FOU AS
SELECT numfou, (SELECT COUNT(*)
FROM PROPOSER P
WHERE P.numfou = F.numfou) AS NB_PROD
FROM FOURNISSEUR F
Ensuite, recherchons les noms des fournisseurs proposant le plus de produits :
SELECT nomfou FROM FOURNISSEUR F, NB_PROD_PAR_FOU N WHERE F.numfou = N.numfou AND NB_PROD = (SELECT MAX(NB_PROD) FROM NB_PROD_PAR_FOU) nomfou Paramédical Gisèle
La requête SELECT MAX(NB_PROD) FROM NB_PROD_PAR_FOU est évaluée avant, et son résultat lui est substitué dans l’expression de la requête. Comme on a
SELECT MAX(NB_PROD) FROM NB_PROD_PAR_FOU; MAX(NB_PROD) 3
Alors la requête précédente, dans ce contexte, est équivalente à
SELECT nomfou FROM FOURNISSEUR F, NB_PROD_PAR_FOU N WHERE F.numfou = N.numfou AND NB_PROD = 2
On peut placer dans des instructions de mises à jour ou d’insertions des requêtes imbriquées. Par exemple,
INSERT INTO PERSONNE (numpers, nom, prenom) VALUES ((SELECT MAX(numpers) + 1 FROM PERSONNE), 'Darth', 'Vador');
On considère une colonne comme une liste de valeurs, on peut tester l’appartance d’un élément à cette liste à l’aide de l’opérateur IN. On peut s’en servir comme une alternative aux jointures, par exemple, réécrivons la requête de la section précédente. La requête suivante nous renvoie le nombre de produits proposés par les fournisseurs proposant le plus de produits :
SELECT MAX(NB_PROD) FROM NB_PROD_PAR_FOU MAX(NB_PROD) 3
Maintenant, recherchons les numéros des fournisseurs proposant un tel nombre de produits :
SELECT N.numfou FROM NB_PROD_PAR_FOU N WHERE NB_PROD = (SELECT MAX(NB_PROD) FROM NB_PROD_PAR_FOU) numfou 2
Notons que s’il existe plusieurs fournisseurs proposant 2 produits, cette requête renverra plusieurs lignes. C’est donc par hasard qu’elle ne retourne qu’une ligne. Le numéro du fournisseur proposant le plus de produits est donc le 1. Cherchons ce fournisseur :
SELECT nomfou FROM FOURNISSEUR F WHERE F.numfou IN (1) nomfou Bocaux Gérard
Il suffit donc dans la requête ci-dessous de remplacer le 1 par la requête qui a retourné 1. On a finalement :
SELECT nomfou
FROM FOURNISSEUR F
WHERE F.numfou IN (SELECT N.numfou
FROM NB_PROD_PAR_FOU N
WHERE NB_PROD = (SELECT MAX(NB_PROD)
FROM NB_PROD_PAR_FOU))
nomfou
Paramédical Gisèle
On peut remplacer le nom d’une table dans la clause FROM par une sous-requête. Par exemple, la requête suivante renvoie une table.
SQL> SELECT
2 (SELECT COUNT(*)
3 FROM PROPOSER PR
4 WHERE PR.numfou = F.numfou
5 ) AS NB_PROD
6 FROM FOURNISSEUR F;
NB_PROD
----------
2
1
1
0
Cette table contient, pour chaque fournisseur, le nombre de produits proposés. Si l’on souhaite connaître le plus grand nombre de produits proposés, on se sert du résultat de la requête ci-dessus comme d’une table :
SQL> SELECT MAX(NB_PROD) AS MAX_NB_PROD
2 FROM
3 (SELECT
4 (SELECT COUNT(*)
5 FROM PROPOSER PR
6 WHERE PR.numfou = F.numfou
7 ) AS NB_PROD
8 FROM FOURNISSEUR F
9 );
MAX_NB_PROD
-----------
2
Ce type de requête est une alternative aux vues. Récupérons maintenant les noms des fournisseurs proposant le plus de produits (sans jointure et sans vue !) :
SQL> SELECT nomfou 2 FROM FOURNISSEUR 3 WHERE numfou IN 4 (SELECT numfou 5 FROM 6 (SELECT numfou, 7 (SELECT COUNT(*) 8 FROM PROPOSER PR 9 WHERE PR.numfou = F.numfou 10 ) AS NB_PROD 11 FROM FOURNISSEUR F 12 ) N 13 WHERE NB_PROD = 14 (SELECT MAX(NB_PROD) 15 FROM 16 (SELECT numfou, 17 (SELECT COUNT(*) 18 FROM PROPOSER PR 19 WHERE PR.numfou = F.numfou 20 ) AS NB_PROD 21 FROM FOURNISSEUR F 22 ) N 23 ) 24 ); NOMFOU ------------------------------ f1
Vous constatez que la solution utilisant les vues est nettement plus simple.
Une sous-requête peut être de deux types :
Par exemple, la requête suivante renvoie le nombre de produits livrés pour chaque fournisseur. Elle contient une sous-requête correlée.
SQL> SELECT numfou,
2 (SELECT SUM(qte)
3 FROM DETAILLIVRAISON D
4 WHERE D.numfou = F.numfou
5 ) NB_PROD_L
6 FROM FOURNISSEUR F;
NUMFOU NB_PROD_L
---------- ----------
1 45
2
3 10
4
Cette même requête, une fois évaluée, peut server de requête non correlée si on souhaite connaître les noms de ces fournisseurs :
SQL> SELECT nomfou, NB_PROD_L 2 FROM FOURNISSEUR F, 3 (SELECT numfou, 4 (SELECT SUM(qte) 5 FROM DETAILLIVRAISON D 6 WHERE D.numfou = F.numfou 7 ) NB_PROD_L 8 FROM FOURNISSEUR F 9 ) L 10 WHERE F.numfou = L.numfou; NOMFOU NB_PROD_L ------------------------------ ---------- f1 45 f2 f3 10 f4
Amusons-nous : quel sont, pour chaque fournisseur, les produits qui ont été les plus livrés ?
SQL> SELECT nomfou, nomprod 2 FROM FOURNISSEUR F, PRODUIT P, 3 (SELECT FF.numfou, PP.numprod 4 FROM FOURNISSEUR FF, PRODUIT PP 5 WHERE 6 (SELECT SUM(qte) 7 FROM DETAILLIVRAISON L 8 WHERE L.numfou = FF.numfou 9 AND L.numprod = PP.numprod 10 ) 11 = 12 (SELECT MAX(NB_PROD_L) 13 FROM 14 (SELECT numfou, SUM(qte) AS NB_PROD_L 15 FROM DETAILLIVRAISON L 16 GROUP BY numprod, numfou 17 ) Q 18 WHERE Q.numfou = FF.numfou 19 ) 20 GROUP BY numfou, numprod 21 ) M 22 WHERE M.numprod = P.numprod 23 AND M.numfou = F.numfou; NOMFOU NOMPROD ------------------------------ ------------------------------ f1 Roue de secours f3 Cotons tiges
Dans la requête précédente, quelles sous-requêtes sont correlées et lesquelles ne le sont pas ?