Mon Cours 1.1

Mohamed Anis

Informations

• Publié par: Sawyung
• Nombre de lectures: 119
• Langue: Français

Extrait

Chapitre 6

Les sous-requêtes

Une caractéristique très puissante de SQL est la possibilité d’imbriquer une requête SQL (SELECT) dans une autre. En effet, nous pouvons utiliser le résultat d’une requête pour formuler une condition que l’on utilise dans une autre requête dite requête principale.

Supposons qu’on veuille afficher les noms des employés qui sont mieux payés que ‘ALLEN’. Pour résoudre ce problème nous avons besoin de deux requêtes ; la première est ditesous-requête et servira à extraire le salaire de ‘ALLEN’. La deuxième est diterequête principale et servira à extraire les noms des employés en question en utilisant le résultat de la sous-requête.

Il existe deux grandes familles de sous-requêtes ;les sous-requêtes corrélées(synchronisées, ou encore ditesdépendantes). L’exécution de ce type de requêtes dépend de l’exécution de la requête principale d’où la corrélation. Ce type de requêtes est complexe, il sera traité dans une section ultérieure (don’t worry). La deuxième famille est celle dessous-requêtes non corrélées, et dont l’exécution ne dépend pas de celle de la requête principale. La sous-requête est alors exécutée la première, son résultat est utilisé séparément pour l’exécution de la requête principale.

6.1

Les sous-requêtes simples (non corrélées)

Une sous-requête simple peut être incluse dans les clauses :

-WHERE -HAVING -FROM. Dans le cas où elle est incluse dans les deux premières clauses, le résultat de la sous-requête est ème utilisé comme étant un critère de recherche. Utilisée dans la 3 clause, la sous-requête retourne une relation (table virtuelle) à partir de laquelle on pourrait extraire nos données. Lorsque la sous- requête est utilisée dans les deux premières clauses, la requête principale a la forme suivante :

SELECTcolonne(s)FROMtable WHEREexpOPERATEUR (SELECTcolonneFROMtable) ; Il est évident qu’il doit y avoir une certaine cohérence entre l’expressionexpqu’on a besoin de comparer (expression de recherche, généralement une colonne), l’opérateur de comparaison OPERATEUR et le résultat de la sous-requête. En d’autres termes, la condition de la clause WHERE doit être construite logiquement (on ne peut pas comparer une colonne à une liste de valeurs si l’opérateur est =).

Une sous-requête simple retourne une relation à une seule ligne ou à plusieurs lignes. La famille des sous-requêtes simples peut être alors divisée en deux sous-familles, les sous-requêtes produisant une seule ligne et les sous-requêtes produisant plusieurs lignes.

Chapitre 6 : Les sous-requêtes

6.1.1

Les sous-requêtes produisant une seule ligne

28

Ce type de sous-requête produit une relation à une seule ligne. L’opérateur de comparaison OPERATEUR est alors un opérateur de comparaison classique (=,>,≥,<, ≤, !=).

REQ 45

Afficher les noms et les salaires des employés mieux payés que ‘ALLEN’ ?

SELECT ENAME, SAL FROM EMP WHERE SAL>(SELECT SAL FROM EMP  WHERE ENAME=’ALLEN’) ; Ici, nous avons besoin d’une seule valeur, le salaire de Mr ‘ALLEN’. Imaginez que la sous- requête retourne plusieurs lignes (plusieurs salaires). Dans ce cas la comparaison n’aura aucun sens et la requête principale retourne une erreur.

REQ 46

Afficher l’employé le moins payé dans l’entreprise ?

SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM EMP) ; Il faut savoir qu’on peut comparer par couples de valeurs (plus généralement un uplet de valeurs). Dans ce cas, la sous-requête est autorisée à produire une relation à une seule ligne et à plusieurs colonnes, on ne peut utiliser que les opérateurs ‘=’ ou ‘!=’.

REQ 47

Afficher les employés ayant le même job et département que ‘MARTIN’ ?

SELECT ENAME, JOB, DEPTNO FROM EMP WHERE (DEPTNO,JOB)=(SELECT DEPTNO, JOB FROM EMP  WHERE ENAME=’MARTIN’) ;

6.1.2

Les sous-requêtes produisant plusieurs lignes

Ce type de sous-requête doit être utilisé avec les opérateurs convenables. Ces opérateurs sont :

-

-

-

 suivie d’une liste de valeurs, la condition est vraie si notreexpégale à l’une des est IN valeurs de la liste.

suivie d’une liste de valeurs, la condition est vraie si la comparaison (en utilisant opANY l’opérateurop) est vraie pour n’importe quelle valeur de la liste.

suivie d’une liste de valeurs, la condition est vraie si la comparaison (en utilisant opALL l’opérateurop) est vraie pour chaque valeur de la liste.

(Ici,opest un opérateur de comparaison classique).

Le langage SQL version Oracle– Document 1.1 Feedbacks à[e-mail]

Chapitre 6 : Les sous-requêtes

REQ 48

Afficher les employés les moins payés pour chaque département ?

29

SELECT ENAME, SAL, DEPTNO FROM EMP WHERE SAL IN (SELECT MIN(SAL) FROM EMP  GROUP BY DEPTNO) ; ENAME SAL DEPTNO SMITH 800 20 JAMES 950 30 MILLER 1300 10 Maintenant, supposons que l’employé ‘ADAMS’ qui travaille dans le département 20 a le salaire 1300 au lieu de 1100. ‘ADAMS’ sera affiché, car son salaire figure parmi les valeurs que retourne la sous-requête. Cette solution présente donc une faille, elle affichera tous les employés dont le salaire est 1300, et non seulement les employés du département 10 et dont le salaire est 1300 (de même pour les valeurs 950 et 800). La bonne solution doit prendre en compte le DEPTNO dans la comparaison.

REQ 49

Afficher les employés les moins payés pour chaque département ?

SELECT ENAME, SAL, DEPTNO FROM EMP WHERE (SAL,DEPTNO) IN (SELECT MIN(SAL),DEPTNO FROM EMP  GROUP BY DEPTNO) ; Les opérateurs ANY et ALL peuvent être utilisés avec tous les opérateurs de comparaison classiques si on veut comparer une colonne à plusieurs valeurs. La requête aura la forme suivante :

SELECTcolonne(s)FROMtable WHEREexpOPERATEUR_CLASSIC ANY/ALL (SELECTcolonne FROMtable) ; REQ 50

Afficher les employés qui sont mieux payés que tous les employés du département 30 ?

SELECT ENAME, SAL FROM EMP WHERE SAL > ALL (SELECT SAL FROM EMP  WHERE DEPTNO=30) ; On peut aussi comparer une liste de colonnes (uplet de colonnes) au résultat d’une sous- requête ramenant plusieurs colonnes et plusieurs lignes. La requête aura la forme suivante :

SELECTcolonne(s)FROMtable

Le langage SQL version Oracle– Document 1.1 Feedbacks à[e-mail]

Chapitre 6 : Les sous-requêtes

30

WHERE (exp 1 ,exp 2 ,…,exp n )OPERATEUR_CLASSIC ANY/ALL  (SELECTexp 1 ,exp 2 ,…,exp n FROMtable) ; Dans ce cas, l’opérateur classique ne peut être que = et !=.

Il faut noter que :

-

-

Û= ANY IN et n’on aucun sens. = ALL != ANY

Une ligne et une colonne

=, !=,>,≥,<,≤ Plusieurs lignes et une colonne

· ·

-

Û!= ALL NOT IN

Une ligne et plusieurs colonnes

=, != Plusieurs lignes et plusieurs colonnes

[NOT] IN·[NOT] IN (=, !=,>,≥,<,≤) ANY/ALL·= ANY ; !=ALL Récapitulatif des sous-requêtes simples :

REQ 51

Opérateurs utilisés selon le nombre de colonnes et de lignes retournées

Afficher les départements ayant une moyenne de salaires supérieure à celle du département 30 ?

SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>(SELECT AVG(SAL) FROM EMP  WHERE DEPTNO=30) ; REQ 52

Afficher le job ayant la plus grande moyenne de salaires ?

SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING AVG(SAL)=(SELECT MAX(AVG(SAL)) FROM EMP  GROUP BY JOB) ; Il est à noter qu’on ne peut pas trier une sous-requête. On ne peut utiliser la clause ORDER BY qu’à la fin de la requête principale pour trier le résultat final.

6.2

Les sous-requêtes corrélées (synchronisées)

Une sous-requête corrélée est une sous-requête dont l’évaluation se fait pour chaque ligne de la requête principale. Supposons qu’on veut afficher les employés dont le salaire est supérieur à la moyenne des salaires de leurs départements. La condition de la clause WHERE doit être

Le langage SQL version Oracle– Document 1.1 Feedbacks à[e-mail]

Chapitre 6 : Les sous-requêtes

31

spécifique à chaque employé, dans la mesure ou chaque employé a un département, et chaque département a une moyenne de salaires différente.

REQ 53

Afficher les employés dont le salaire est supérieur à la moyenne de salaires dans leurs départements ?

SELECT * FROM EMP E WHERE SAL>(SELECT AVG(SAL) FROM EMP  WHERE E.DEPTNO=DEPTNO) ; L’exécution de cette requête se produit ainsi :

1-La requête principale fixe une ligne de la table EMP.

2-Ayant E.DEPTNO, la sous-requête est évaluée.

3-La condition de la clause WHERE de la requête principale est évaluée, la ligne est retournée ou non suivant la valeur de la condition.

4-Itération des étapes 1, 2 et 3 pour les lignes restantes de la table EMP.

6.3

L’opérateur EXISTS

L’opérateur EXISTS est généralement suivi d’une sous-requête corrélée. Cet opérateur prend la valeur VRAI si la sous-requête ramène au moins une ligne, et la valeur FAUX si la sous-requête ne ramène aucune ligne. Dans le cas où cet opérateur est suivi d’une sous-requête simple, il y aura exécution de celle-ci et dès qu’elle ramène une ligne EXISTS prend la valeur VRAI et la requête principale affiche toutes les lignes. Si la sous-requête ne ramène aucune ligne, alors la requête principale ne retourne aucune ligne. Il en découle l’inutilité d’une sous-requête simple venant après l’opérateur EXISTS, car dans ce cas il y aura affichage de toute les lignes (si EXISTS est VRAI) ou d’aucune (si EXISTS est FAUX).

REQ 54

Afficher les employés qui dirigent au moins un autre employé ?

SELECT * FROM EMP M WHERE EXISTS (SELECT * FROM EMP E WHERE M.EMPNO=E.MGR); Ici, pour chaque employé de la table EMP, la sous-requête est exécutée. Pour ‘SMITH’, la sous-requête (SELECT * FROM EMP WHERE MGR=7369 ) ne retourne aucune ligne, EXISTS prend la valeur FAUX et notre employé ne sera pas affiché.

Il faut noter qu’en général dans la sous-requête, la clause SELECT peut contenir n’importe quelle colonne, expression, fonction ou même littéral (constante) car ce qui importe pour notre opérateur, c’est le nombre de lignes retournées (0 ou au moins 1).

Le langage SQL version Oracle– Document 1.1 Feedbacks à[e-mail]

Chapitre 6 : Les sous-requêtes

La requête précédente peut être réalisée par la jointure ou par l’opérateur IN :

REQ 55

SELECT DISTINCT M.EMPNO, M.ENAME, M.JOB, M.DEPTNO FROM EMP E, EMP M WHERE E.MGR=M.EMPNO ; REQ 56

SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP WHERE EMPNO IN (SELECT DISTINCT MGR FROM EMP) ;

REQ 57

EMPNO 7566 7698 7782 7788 7839 7902

ENAME JONES BLAKE CLARK SCOTT KING FORD

JOB DEPTNO MANAGER 20 MANAGER 30 MANAGER 10 ANALYST 20 PRESIDENT 10 ANALYST 20

Afficher les employés qui n’existent dans aucun département de DEPT ?

SELECT * FROM EMP WHERE NOT EXISTS (SELECT DEPTNO FROM DEPT  WHERE EMP.DEPTNO=DEPT.DEPTNO); Aucune ligne sélectionnée.

REQ 58

Afficher les départements dans lesquelles n’existe aucun employé ?

SELECT * FROM DEPT WHERE NOT EXISTS (SELECT * FROM EMP  WHERE EMP.DEPTNO=DEPT.DEPTNO); REQ 59

32

Afficher les départements qui ont au moins un employé ayant un salaire supérieur à 10.000 ?

SELECT DNAME FROM DEPT WHERE EXISTS(SELECT * FROM EMP

Le langage SQL version Oracle– Document 1.1 Feedbacks à[e-mail]

Chapitre 6 : Les sous-requêtes

6.4

WHERE DEPTNO=DEPT.DEPTNO AND SAL>1000);

NOT EXISTS vs NOT IN

33

Les deux opérateurs peuvent être utilisés pour résoudre la même interrogation, mais il existe une différence entre les deux.

REQ 60

Afficher les employés qui ne dirigent aucun autre employé ?

SELECT M.EMPNO, M.ENAME FROM EMP M WHERE NOT EXISTS (SELECT * FROM EMP E  WHERE E.MGR=M.EMPNO); Cette solution nous affiche huit employés dont l’ ne figure pas dans la colonne EMPNO MGR (ne sont donc pas des managers).

REQ 61

SELECT EMPNO, ENAME FROM EMP WHERE EMPNO NOT IN (SELECT MGR FROM EMP) ; Cette solution, apparemment correcte, n’affiche aucune ligne, car la sous-requête simple retourne la valeur NULL.

Ecrivons la même requête en développant l’opérateur NOT IN :

SELECT EMPNO, ENAME FROM EMP WHERE EMPNO!=7566ANDEMPNO!=7566AND…ANDEMPNO!=NULL; Or toute expression logique comparant la valeur NULL à une colonne est mise à FAUX, et puisque les expressions logiques de la clause WHERE sont connectées par des AND, alors c’est toute la condition qui portera la valeur FAUX.

Le problème peut être corrigé en utilisant la fonction NVL, ou en restreignant les valeurs NULL via la clause WHERE, ou tout simplement en utilisant l’opérateur NOT EXISTS.

6.5

La division et le NOT EXISTS

C’est une opération algébrique dont on n’a pas spécifié d’opérateur. Il s’agit de diviser une relation R par une autre relation S sur les colonnes qui sont communes. Le résultat est une relation D composé par les colonnes non communes entre R et S.

La division de R par S sur l’attribut B est la relation D définie par :

D = {a R[A]/b S, (a,b)

R} = {a R[A]/b S, (a,b)

R}

Le langage SQL version Oracle– Document 1.1 Feedbacks à[e-mail]

Chapitre 6 : Les sous-requêtes

R A B a1 b1 a1 b2 a2 b1

/ B

S B b1 b2

=

D A a1

34

Remarquez que le produit cartésien de S et D est inclus dans la relation R. Ici, D contient les a i qui sont en relation avec tous les b de S. i

D est alors le résultat de la question suivante : Donnez les a de R qui sont en relation avec i tous les b de S ? i Mais on peut reformuler cette question d’une autre manière : Donnez chaque a de R tel qu’il i n’existe aucun b de S qui ne soit pas en relation avec a ? i i Ainsi la traduction de la division dans le langage SQL se fait ainsi :

REQ 62 SELECT A FROM R R1 WHERE NOT EXISTS(SELECT B FROM S  WHERE NOT EXISTS(SELECT A, B FROM R R2  WHERE R2.A=R1.A AND R2.B=S.B); L’exécution de cette requête est la suivante :

Requête principale

Exécution

ère Fixer la 1 valeur de A qui est a1. Pour retourner a1, nous sommes obligés de passer à ère la 1 sous-requête ère pour évaluer la 1 clause WHERE. a1 sera retourné s’il n’existe aucune ligne dans cette sous-requête.

Résultat de l’exécution

ère 1 sous-requête

Exécution

Résultat de l’exécution

ème 2 sous-requête

Exécution

Le langage SQL version Oracle– Document 1.1 Feedbacks à[e-mail]

Résultat de l’exécution

Chapitre 6 : Les sous-requêtes

Requête principale

Exécution

ère Fixer la 1 valeur de B qui est b1. Pour retourner b1, ème il faut que la 2 sous requête ne retourne aucune ligne, cette dernière est (SELECT * FROM R WHERE A=a1 AND B=b1).

Exécution

Le langage SQL version Oracle– Document 1.1 Feedbacks à[e-mail]

ème 2 sous-requête

Résultat de l’exécution

ère 1 sous-requête

Exécution

(a1,b2)

Celle-ci retourne une ligne.

Celle-ci retourne une ligne.

(a1,b1)

aucune ligne

Tous les b i sont vérifiés. Retour à la requête principale.

Résultat de l’exécution

ème Fixer la 2 valeur de B qui est b2. Pour retourner b2, ème il faut que la 2 sous requête ne retourne aucune ligne, cette dernière est (SELECT * FROM R WHERE A=a1 AND B=b2).

b1 ne sera pas retournée.

aucune ligne

aucune ligne

Résultat de l’exécution

b1 ne sera pas retournée.

35

ème Fixer la 2 valeur de B qui est b2.Pour retourner b2, il faut que la ème 2 sous requête ne retourne aucune ligne, cette dernière est (SELECT * FROM R WHERE A=a2 AND B=b2).

Résultat de l’exécution

Celle-ci retourne une ligne.

a1

Requête principale

Exécution

Chapitre 6 : Les sous-requêtes

ème Fixer le 2 A qui est a2

NOT EXISTS est vrai pour a1

36

Exécution

Le langage SQL version Oracle– Document 1.1 Feedbacks à[e-mail]

Résultat de l’exécution

aucune ligne.

Celle-ci ne retourne aucune ligne.

(a2,b1)

Résultat de l’exécution

aucune ligne

ère Fixer la 1 valeur de B qui est b1.Pour retourner b1, il faut que la ème 2 sous requête ne retourne aucune ligne, cette dernière est (SELECT * FROM R WHERE A=a2 AND B=b1).

b1 ne sera pas retournée.

Exécution

ère 1 sous-requête

ème 2 sous-requête

Le langage SQL version Oracle– Document 1.1 Feedbacks à[e-mail]

Requête principale

Résultat de l’exécution

il existe une ligne dans la 1ère sous- requête. Donc a2 sera rejetée.

37

REQ 63 SELECT A FROM R MINUS SELECT A FROM (SELECT R.A, S.B FROM R, S  MINUS  SELECT A, B FROM R) On peut aussi calculer pour chaque a , la différence entre (tous les b de S) et (les b avec qui il i i i est en relation). Si cette différence donne l’ensemble vide, c’est que notre a est en relation avec i tous les b de S : i

ère 1 sous-requête

b2

Le même résultat peut être obtenu en utilisant le langage algébrique :

Exécution

Résultat de l’exécution

Exécution

R[A] MINUS ((R[A] TIMES S[B]) MINUS R[A,B])[A] La traduction en langage SQL est la suivante :

Exécution

Résultat final D={a1}

b2 sera alors retournée

Chapitre 6 : Les sous-requêtes

ème 2 sous-requête

Résultat de l’exécution