pdf - e-book - archive

1.7  Requêtes imbriquées

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 :

1.7.1  Sous requêtes renvoyant une valeur scalaire

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

Colonne fictive

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

Conditions complexes

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


INSERT et UPDATE

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

1.7.2  Sous requêtes renvoyant une colonne

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

1.7.3  Sous requêtes non correlées renvoyant une table

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.

1.7.4  Sous requêtes correlées

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 ?