In acest capitol se vor prezenta alte caracteristici ale declaratiei SELECT, cum ar fi cereri continute in clauza WHERE sau HAVING ale altei decla- ratii SQL.
O subcerere este o declaratie SELECT care este ascunsa in interiorul altei
declaratii SELECT si care intoarce rezultate intermediare.
De exemplu:
SELECT column1, column2, ...
FROM table
WHERE column =
(SELECT column
FROM table
WHERE condition)
Subcererea este adesea referita ca un subselect sau ca un SELECT interior; in general, subcererea se executa prima si rezultatul sau este folosit pentru a completa conditia cererii principale sau a cererii externe.Folosirea sub- cererilor permite consruirea de comenzi puternice pornind de la unele simple. Cererea ascunsa poate fi foarte folositoare cind este nevoie sa fie selectate linii dintr-o tabela printr-o conditie ce depinde de date din propria tabela.
Pentru a gasi angajatul care cistiga salariul minim din companie (salariul minim este o cantitate necunoscuta), trebuie parcursi doi pasi:
SELECT MIN(SAL)
FROM EMP;
MIN(SAL)
----------
800
SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL = (cel mai mic salariu care este cunoscut)
Putem combina cele doua cereri ca o subcerere ascunsa:
SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL = (SELECT MIN(SAL)
FROM EMP);
ENAME JOB SAL
------- ------ -----
SMITH CLERK 800
O declarartie SELECT poate fi considerata ca un bloc de cerere.Exemplul de mai sus consta din doua blocuri de cerere - cererea principala si cererea in- terioara.
Declaratia SELECT interioara sau blocul de cerere este executata prima, producind un rezultat al cererii: 800.Blocul de cerere principal este apoi prelucrat si foloseste valoarea intoarsa de cererea interioara pentru a com- pleta conditia de cautare.Deci, cererea principala in final va arata in felul urmator:
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL = 800;
In exemplul de mai sus, 800 este o valoare unica.Subcererea care a intors valoarea 800 se numeste subcerere ce intoarce o singura linie.Cind o subcerere intoarce doar o linie, va fi folosit un operator logic sau un operator de com- paratie.De exemplu: =, <, >, <=, etc.
Pentru a gasi toti angajatii ce au aceeasi functie ca BLAKE, vom introduce:
SELECT ENAME, JOB
FROM EMP
WHERE JOB = (SELECT JOB
FROM EMP
WHERE ENAME = 'BLAKE');
ENAME JOB
------- -----
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
Cererea interioara intoarce functia lui BLAKE, care este folosita in partea dreapta a clauzei WHERE di cererea principala (dupa operatorul de comparatie).
Urmatoarea cerere gaseste angajatii care au salariul egal cu salariul minim din fiecare departament:
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
Observati ca, cererea interioara are clauza GROUP BY.Aceasta inseamna ca va intoarce mai mult decit o valoare.Deci este nevoie sa folosim un operator de comparatie multi linie.In acest caz, operatorul IN trebuie sa fie folosit, deoarece rezulta o lista de valori.
Rezultatul obtinut nu arata si departamentul in care lucreaza angajatul.
Deoarece se compara doar valorile salariilor, cererea interioara poate intoar-
ce o valoare simpla, pentru ca el cauta cel mai mic salariu pentru unul din
departamente, nu in mod necesar departamentul propriu al angajatului.Prin
urmare, cererea poate fi rescrisa pentru a gasi combinatia salariul angaja-
tului si numarul departamentului cu salariul minim si numarul departamentului:
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE (SAL, DEPTNO) IN
(SELECT MIN(SAL), DEPTNO
FROM EMP
GROUP BY DEPTNO);
ENAME SAL DEPTNO
------- ----- ---------
SMITH 800 20
JAMES 950 30
MILLER 1300 10
Cererea de mai sus compara o pereche de coloane.
Observatie : coloanele din partea stinga a conditiei de cautare sint in pa- ranteze si fiecare coloana este separata printr-o virggula.
Coloanele listate in clauza SELECT a subcererii trebuie sa fie in aceeasi ordine ca si lista coloanelor dintre paranteze din clauza WHERE a cererii externe.
Coloanele intoarse de cererea interioara trebuie, de asemenea sa se potri- veasca in numar si tip de date cu coloanele cu care ele sint comparate in cererea externa.
De exemplu :
...WHERE (numcolumn, charcolumn) =
(SELECT datecolumn, numcolumn, charcolumn ...
Cind o subcerere intoarce mai mult decit o linie si este folosit un opera- tor de comparatie pt o singura linie, SQL*Plus da urmatorul mesaj de eroare:
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL = (SELECT MIN(SAL)
FROM EMP
GROUP BY DEPTNO);
Eroare : subcerere ce trebuie sa intoarca o singura linie intoarce mai mult decit o linie.
Nu este selectata nici o inregistrare.
Daca cererea interioara nu intoarce nici o linie, va fi dat urmatorul mesaj de eroare :
SELECT ENAME, JOB
FROM EMP
WHERE JOB = (SELECT JOB
FROM EMP
WHERE ENAME = 'SMITHE');
Eroare : subcerere ce trebuie sa intoarca o singura linie nu intoarce nici o linie.
Nu este selectata nici o inregistrare.
SELECT ENAME, SAL, JOB, DEPTNO
FROM EMP
WHERE SAL > SOME (SELECT DISTINCT SAL
FROM EMP
WHERE DEPTNO = 30)
ORDER BY SAL DESC;
ENAME SAL JOB DEPTNO
------- ----- ----- --------
KING 5000 PRESIDENT 10
SCOTT 3000 ANALYST 20
FORD 3000 ANALYST 20
JONES 2975 MANAGER 20
BLAKE 2850 MANAGER 30
CLARK 2450 MANAGER 10
ALLEN 1600 SALESMAN 30
MILLER 1300 CLERK 10
WARD 1250 SALESMAN 30
ADAMS 1100 CLERK 20
Cel mai mic salariu din departamentul 30 este 950$ (a lui James).Cererea principala intoarce angajatii care cistiga un salariu mai mare ca salariul minim din departamentul 30.Asa ca '> ANY' inseamna mai mare ca minim.'=ANY' este echivalent cu IN.
Cind se foloseste SOME/ANY, DISTINCT este frecvent folosit pentru a impie-
dica sa se selecteze linniile de mai multe ori.
ALL compara o valoare cu fiecare valoare intoarsa de o subcerere.
Urmatoarea cerere gaseste angajatii care cistiga mai mult ca fiecare angajat
din departamentul 30 :
SELECT ENAME, SAL, JOB, DEPTNO
FROM EMP
WHERE SAL > ALL (SELECT DISTINCT SAL
FROM EMP
WHERE DEPTNO = 30)
ORDER BY SAL DESC;
ENAME SAL JOB DEPTNO
------- ----- ----- ---------
KING 5000 PRESIDENT 10
SCOTT 3000 ANALYST 20
FORD 3000 ANALYST 20
JONES 2975 MANAGER 20
Cel mai mare salariu din departamentul 30 este 250$ (a lui Blake), asa ca cererea intoarce acei angajati ai caror salariu este mai mare ca salariul maxim din departamentul 30, prin urmare mai mare ca fiecare salariu din de- partament.
Operatorul NOT poate fi folosit cu IN, ANY sau ALL.
Subcererile ascunse pot fi folosite de asemenea in clauza HAVING.
(Observatie : clauza WHERE se refera la o singura linie si clauza HAVING la grupuri de linii specificate in clauza GROUP BY.)
De exemplu, pentru a afisa departamentul(ele) care au un salariu mediu mai mare ca departamentul 30, introducem :
SELECT DEPTNO, AVG(SAL)
FROM EMP
HAVING AVG(SAL) > (SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = 30)
GROUP BY DEPTNO;
DEPTNO AVG(SAL)
-------- ----------
10 2916.66667
20 2175
Pentru a construi o cerere care gaseste functia cu cel mai mare salariu mediu, introducem :
SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) = (SELECT MAX(AVG(SAL))
FROM EMP
GROUP BY JOB);
JOB AVG(SAL)
----- ----------
PRESIDENT 5000
Mai intii cererea interioara gaseste salariul mediu pentru fiecare grup de functii diferit si functia MAX alege cel mai mare salariu mediu.Acea valoare (5000) este folosita in clauza HAVING.Clauza GROUP BY din cererea principala este necesara pentru ca lista ce urmeaza dupa SELECT-ul din cererea principala contine atit o coloana agregat cit si o coloana non-agragat.
Nu poate exista o clauza ORDER BY intr-o subcerere.
Regula este ca poate exista doar o singura clauza ORDER BY pentru o decla- ratie SELECT si, daca este specificata, trebuie sa fie ultima clauza din co- manda SELECT.
Subcererile pot fi ascunse (folosite in interiorul unei subcereri) :
Afisati numele, functia si data angajarii pentru angajatii al caror salariu este mai mare ca cel mai mare salariu din orice departament de vinzari.
SELECT ENAME, JOB, HIREDATE, SAL
FROM EMP
WHERE SAL > (SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE DNAME = 'SALES'));
ENAME JOB HIREDATE SAL
------- ----- ---------- -----
JONES MANAGER 02-APR-81 2975
SCOTT ANALYST 09-DEC-82 3000
KING PRESIDENT 17-NOV-81 5000
FORD ANALYST 03-DEC-81 3000
Limita nivelelor de imbricare pentru o subcerere este 255.
Reguli de scriere a cererilor
O subcerere corelata este o subcerere care este executata o data pentru fiecare linie candidat considerata de cererea principala si care la executie foloseste o valoare dintr-o coloana din cererea exterioara.Aceasta determina ca subcererea corelata sa fie prelucrata intr-un mod diferit de subcererea ascunsa obisnuita.
O subcerere corelata este identificata prin folosirea unei coloane a cererii exterioare in clauza predicatului cererii interioare.
Cu o subcerere ascunsa obisnuita, SELECT-ul interior ruleaza primul si se executa o data, intorcind valori ce vor fi folosite de cererea principala.Pe de alta parte, o subcerere corelata se executa o data pentru fiecare linie candidat considerata de cererea externa.Cererea interioara este dirijata de cererea externa.
Pasii de executie ai unei subcereri corelate :
Desi subcererea corelata se executa repetat, o data pentru fiecare linie in cererea principala, aceasta nu inseamna ca subcererile corelate sint mai putin eficiente ca subcererile necorelate obisnuite.Se va vorbi despre eficienta mai tirziu in acest capitol.
Putem folosi o subcerere corelata pentru a gasi angajatii care cistiga un salariu mai mare ca salariul mediu al departamentului lor :
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP E
WHERE SAL > (SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = E.DEPTNO)
ORDER BY DEPTNO;
EMPNO ENAME SAL DEPTNO
------- ------- ------ --------
7839 KING 5000 10
7566 JONES 2975 20
7788 SCOTT 3000 20
7902 FORD 3000 20
7499 ALLEN 1600 30
7698 BLAKE 2850 30
Putem observa imediat ca este o cerere corelata pentru ca am folosit o co- loana din SELECT-ul extern in clauza WHERE din SELECT-ul interior.
Observati ca alias-ul este necesar doar pentru a indeparta ambiguitatea pentru numele coloanelor.
Sa analizam exemplul de mai sus folosind tabela EMP :
Cererea principala
Selectia liniilor candidat continua cu verificarea conditiei ce apare in re- zultatul cererii.
Observatie : o subcerere corelata este semnalata de un nume de coloana, un nume de tabela sau un alias de tabela in clauza WHERE, care se refera la va- loarea unei coloane in fiecare linie candidat din SELECT-ul exterior.De aseme- nea subcererea corelata se executa repetat pentru fiecare linie candidat in cererea principala.
Comenzile UPDATE pot contine subcereri corelate :
UPDATE EMP E
SET (SAL, COMM) = (SELECT AVG(SAL) * 1.1, AVG(COMM)
FROM EMP
WHERE DEPTNO = E.DEPTNO)
HIREDATE = '11-JUN-85';
Cind se folosesc declaratii SELECT ascunse, operatorii logici, precum si ANY si ALL sint toti valizi.In plus poate fi folosit operatorul EXISTS.
Operatorul EXISTS este frecvent folosit cu subcererile corelate.El testeaza daca o valoare exista (NOT EXISTS specifica daca ceva nu exista).Daca valoarea exista se intoarce TRUE; daca valoarea nu exista se intoarce FALSE.
Pentru a gasi angajatii ce au cel putin un subordonat, introducem :
SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP E
WHERE EXISTS (SELECT EMPNO
FROM EMP
WHERE EMP.MGR = E.EMPNO)
ORDER BY EMPNO;
EMPNO ENAME JOB DEPTNO
------- ------- ----- --------
7566 JONES MANAGER 20
7698 BLAKE MANAGER 30
7782 CLARK MANAGER 10
7788 SCOTT ANALYST 20
7839 KING PRESIDENT 10
7902 FORD ANALYST 20
Sa gasim toti angajatii al caror departament nu este in tabela DEPT :
SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE NOT EXISTS (SELECT DEPTNO
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO);
Nu va fi selectata nici o inregistrare.
Alt mod de a gasi departamentul care nu are nici un angajat este :
SELECT DEPTNO, DNAME
FROM DEPT D
WHERE NOT EXISTS (SELECT 1
FROM EMP E
WHERE E.DEPTNO = D.DEPTNO);
DEPTNO DNAME
-------- -------
40 OPERATIONS
Observati ca SELECT-ul interior nu este necesar sa intoarca o valoare spe- cifica, asa ca poate fi selectata o cifra.
Subcererea corelata este un mod de a 'citi' fiecare linie din tabela si de a compara valorile din fiecare linie cu datele inrudite.Aceasta este folosita oricind o subcerere trebuie sa intoarca un rezultat diferit sau o multime de rezultate pentru fiecare linie candidat considerata de cererea principala.Cu alte cuvinte, o subcerere corelata este folosita pentru a raspunde la intre- bari cu mai multe subpuncte al caror raspuns depinde de valoarea din fiecare linie din cererea parinte.
SELECT-ul interior este executat normal o data pentru fiecare linie candi- dat.
Vom examina cele doua tipuri de subcereri.Trebuie mentionat ca subcererea corelata (cu EXISTS) poate fi cel mai performant mod pentru unele cereri.
Performanta va depinde de folosirea indexarilor, de numarul liniilor in- toarse de cereri, de dimensiunea tabelelor si daca sint necesare tabelele temporare pentru a evalua rezultatele temporare.Tabelele temporare generate de ORACLE nu sint indexate si acest lucru conduce la degradarea performantelor pentru subcererile ce folosesc IN, ANY si ALL.
Cele de mai sus sint puncte de vedere generale.Performantele sint discutate mai in detaliu in alte cursuri.
Desi intr-o subcerere o operatie NOT IN poate fi la fel de eficienta ca si NOT EXISTS, NOT EXISTS este mult mai sigur daca subcererea intoarce niste valori NULL, fata de de NOT IN pentru care conditia se evalueaza la FALSE cind in lista de comparatii sint incluse valori NULL.
Considerind urmatoarea cerere care gaseste angajatii ce nu au nici un su- bordonat :
SELECT ENAME, JOB
FROM EMP
WHERE EMPNO NOT IN (SELECT MGR
FROM EMP);
Nici o linie nu va fi intoarsa de cererea de mai sus, deoarece coloana MGR contine o valoare NULL.
Cererea corecta este :
SELECT ENAME, JOB
FROM EMP E
WHERE NOT EXISTS (SELECT MGR
FROM EMP
WHERE MGR = E.EMPNO);
ENAME JOB
------- -----
SMITH CLERK
ADAMS CLERK
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
TURNER SALESMAN
JAMES CLERK
MILLER CLERK
Aceste exercitii va vor permite sa scrieti cereri complete folosind SELECT- uri ascunse si SELECT-uri corelate.
JOB ENAME SAL
----- ------- ------
PRESIDENT KING 5,000.00
ANALYST SCOTT 3,000.00
ANALYST FORD 3,000.00
MANAGER JONES 2,975.00
SALESMAN ALLEN 1,600.00
CLERK MILLER 1,300.00
Vor fi 6 inregistarri selectate.
ENAME JOB SAL
------- ----- -----
SMITH CLERK 800.00
WARD SALESMAN 1,250.00
MARTIN SALESMAN 1,250.00
CLARK MANAGER 2,450.00
SCOTT ANALYST 3,000.00
FORD ANALYST 3,000.00
KING PRESIDENT 5,000.00
Vor fi 7 inregistari selectate.
DEPTNO ENAME HIREDATE
-------- ------- ----------
20 ADAMS 04-JUN-84
10 KING 09-JUL-84
30 JAMES 23-JUL-B4
ENAME SALARY DEPTNO
------- -------- --------
KING 5000 10
JONES 2975 20
SCOTT 3000 20
FORD 3000 20
ALLEN 1600 30
BLAKE 2850 30
Vor fi selectate 6 inregistrari.
DEPTNO DNAME
-------- -------
40 OPERATIONS
DEPTNO COMPENSATION
-------- ---------------
20 130500
ENAME SAL
------- -----
SCOTT 3,000.00
KING 5,000.00
FORD 3,000.00
YEAR NUMBER OF EMPS
------ ----------------
1984 8
ENAME SAL DEPTNO DEPTAVG
------- ----- -------- ----------
ALLEN 1600 30 1566.66667
BLAKE 2850 30 1566.66667
JONES 2975 20 2175
FORD 3000 20 2175
SCOTT 3000 20 2175
KING 5000 10 2916.66667
Vor fi selectate 6 inregistrari.
ENAME HIREDATE MAXDATE
------- ---------- ---------
SMITH 13-JUN-83
ADAMS 04-JUN-84
ALLEN 15-AUG-83
BLAKE 11-JUN-84
CLARK 14-MAY-84
FORD 05-DEC-83
JAMES 23-JUL-84 *
JONES 31-OCT-83
KING 09-JUL-84
MARTIN 05-DEC-83
MILLER 21-NOV-83
SCOTT 05-MAR-84
TURNER 04-JUN-84
WARD 26-MAR-84
Vor fi selectate 14 inregistrari.
SELECT JOB, ENAME, SAL
FROM EMP
WHERE (SAL, JOB) IN (SELECT MAX(SAL), JOB
FROM EMP
GROUP BY JOB)
ORDER BY SAL DESC;
SELECT ENAME, JOB, SAL
FROM EMP
WHERE (SAL, JOB) IN (SELECT MIN(SAL), JOB
FROM EMP
GROUP BY JOB)
ORDER BY SAL;
SELECT DEPTNO, ENAME, HIREDATE
FROM EMP
WHERE (HIREDATE, DEPTNO) IN (SELECT MAX(HIREDATE), DEPTNO
FROM EMP
GROUP BY DEPTNO)
ORDER BY HIREDATE;
SELECT ENAME, SAL SALARY, DEPTNO
FROM EMP E
WHERE SAL > (SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = E.DEPTNO)
ORDER BY DEPTNO;
SELECT DEPTNO, DNAME
FROM DEPT D
WHERE NOT EXISTS (SELECT 'anything'
FROM EMP
WHERE DEPTNO = D.DEPTNO);
DEFINE REM = SAL * 12 + NVL(COMM, 0)
SELECT DEPTNO, SUM(&REM) COMPENSATION
FROM EMP
GROUP BY DEPTNO
HAVING SUM(&REM) = (SELECT MAX(SUM(&REM))
FROM EMP
GROUP BY DEPTNO);
SELECT ENAME, SAL
FROM EMP E
WHERE 3 > (SELECT COUNT(*)
FROM EMP
WHERE E.SAL < SAL);
SELECT TO_CHAR(HIREDATE, 'YYYY') YEAR,
COUNT(EMPNO) NUMBER_OF_EMPS
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY')
HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO))
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY'));
SELECT E.ENAME ENAME, E.SAL, E.DEPTNO, AVG(A.SAL) DEPT_AVG
FROM EMP A, EMP E
WHERE E.DEPTNO = A.DEPTNO
AND E.SAL > (SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = E.DEPTNO)
GROUP BY E.ENAME, E.SAL, E.DEPTNO
ORDER BY AVG(A.SAL);
SELECT ENAME, HIREDATE, '*' MAXDATE
FROM EMP
WHERE HIREDATE = (SELECT MAX(HIREDATE) FROM EMP)
UNION
SELECT ENAME, HIREDATE, ' '
FROM EMP
WHERE HIREDATE <> (SELECT MAX(HIREDATE) FROM EMP)