Mon Cours 1.1
12
pages
Français
Documents
Le téléchargement nécessite un accès à la bibliothèque YouScribe Tout savoir sur nos offres
12
pages
Français
Documents
Le téléchargement nécessite un accès à la bibliothèque YouScribe Tout savoir sur nos offres
Publié par
Langue
Français
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 dite sous-requête et servira à extraire le salaire de ‘ALLEN’. La deuxième est dite requê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 dites dé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 des sous-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 ...
Voir
Publié par
Langue
Français
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êteet 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)FROMtableWHEREexpOPERATEUR (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 àanis.bach@isg.rnu.tn
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)FROMtableWHEREexpOPERATEUR_CLASSIC ANY/ALL (SELECTcolonneFROMtable) ; 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 àanis.bach@isg.rnu.tn
Chapitre 6 : Les sous-requêtes
30
WHERE (exp1,exp2,…,expn)OPERATEUR_CLASSIC ANY/ALL (SELECTexp1,exp2,…,expnFROMtable) ; 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 àanis.bach@isg.rnu.tn
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 àanis.bach@isg.rnu.tn
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 àanis.bach@isg.rnu.tn
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 àanis.bach@isg.rnu.tn
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 àanis.bach@isg.rnu.tn
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 àanis.bach@isg.rnu.tn
è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 bisont 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 àanis.bach@isg.rnu.tn
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 àanis.bach@isg.rnu.tn
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
