n7q14qp
Acest capitol prezinta diferite metode de constructie a conditiilor de join.

Join extern
Daca o linie nu satisface o conditie de join, atunci linia respectiva nu va apare in rezultatul cererii.De fapt, in conditia de equi-join pentru EMP si DEPT, departamentul 40 nu apare.Aceasta se intimpla deoarece nu este nici un angajat in departamentul 40.

------------------------------------------------------------------------

| JOIN EXTERN |

| |

| ------------------------------------------------------ |

| | EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO | |

| ------------------------------------------------------ ? |

| |======================================================|------ |

| ------------------------------------------------------ | |

| | |

| --------------------------------------------------------------| |

| | |

| | -------------------------------- |

| | | DEPTNO DNAME LOC | |

| | -------------------------------- |

| | |================================| |

| | -------------------------------- |

| |---------| 40 OPERATIONS BOSTON | |

| -------------------------------- |

| |

------------------------------------------------------------------------

Nu exista nici un angajat in departamentul 40, dar el poate fi afisat facind join intre departamentul 40 cu o linie vida.

Liniile ce lipsesc pot fi returnate daca este folosit un operator de join extern in conditia de join.Operatorul este semnul plus inclus intre paranteze (+) si este plasat in partea tabelei deficiente in informatii.Operatorul are ca efect crearea uneia sau mai multor linii vide cu care una sau mai multe linii din tabela nedeficitara in informatii poate face join.

O linie vida este creata pentru fiecare linie adaugata in tabela nedefici- tara in informatii.

SELECT E.ENAME, D.DEPTNO, D.DNAME

FROM EMP E, DEPT D

WHERE E.DEPTNO(+) = D.DEPTNO

AND D.DEPTNO IN (30, 40);

ENAME DEPTNO DNAME

--------- --------- ---------

ALLEN 30 SALES

BLAKE 30 SALES

TURNER 30 SALES

JAMES 30 SALES

MARTIN 30 SALES

WARD 40 OPERATIONS

Operatorul de join extern pote apare doar intr-o parte a expresiei si anume in partea in care lipsesc informatii.Vor fi intoarse acele linii dintr-o ta- bela care nu au corespondent direct in cealalta tabela.
Alte restrictii pentru join extern sint:

nu se poate face join extern a unei tabele cu mai mult de o tabela ce di- fera de prima intr-o singura declaratie SELECT; o conditie ce include un join extern nu poate folosi operatorul IN sau nu i se poate fi unita cu alte conditii prin operatorul OR.
Join-ul unei tabele cu ea insasi
Este posibila folosirea etichetelor de tabele (alias-urilor) pentru a face join unei tabele cu ea insasi, ca si cum ar fi doua tabele separate.Aceasta permite ca liniilor dintr-o tabela sa li se faca join cu liniile din aceeasi tabela.

Urmatoarea cerere listeaza toti angajatii care cistiga mai putin ca sefii lor :

SELECT E.ENAME EMP_NAME, E.SAL EMP_SAL,

M.ENAME MGR_NAME, M.SAL MGR_SAL

FROM EMP E, EMP M

WHERE E.MGR = E.EMPNO

AND E.SAL < M.SAL;

EMP_NAME EMP_SAL MGR_NAME MGR_SAL

----------- ----------- ----------- ----------

ALLEN 1600 BLAKE 2850

WARD 1250 BLAKE 2850

MARTIN 1250 BLAKE 2850

TURNER 1500 BLAKE 2850

JAMES 950 BLAKE 2850

MILLER 1300 CLARK 2450

ADAMS 1100 SCOTT 3000

JONES 2975 KING 5000

BLAKE 2850 KING 5000

CLARK 2450 KING 5000

SMITH 800 FORD 3000

Observati ca, clauza FROM se refera la EMP de doua ori si ca urmare EMP are asociata cite un alias pentru ambele cazuri - E si M.Este ajutator ca alias- urile asociate sa fie cu inteles, de exemplu E inseamna angajati (employees) si M inseamna sefi (managers).

Clauza join poate fi exprimata:

"unde numarul sefului angajatului este acelasi cu numarul angajatului se- fului lui".

Operatori de multimi
In cadrul acestui capitol vor fi discutate reuniunea, intersectia si dife- renta.

Reuniunea, intersectia si diferenta sint folositoare in constructia cereri- lor care se refera la tabelediferite.Ele combina rezultatele a doua sau mai multe declaratii select in unul singur.O cerere poate fi formata din doua sau mai multe declaratii SQL inlantuite prin operatori de multimi.Operatorii de multimi sint numiti join-uri verticale, deoarece join-ul nu se face in raport cu liniile din cele doua tabele, ci in raport cu coloanele.

In urmatoarele trei exemple, cererile sint aceleasi, dar operatorul este diferit in fiecare caz, generindu-se rezultate diferite pentru cereri.

Reuniunea
Pentru a lista toate liniile diferite generate de fiecare din cereri, introducem:

SELECT JOB

FROM EMP

WHERE DEPTNO = 10

UNION

SELECT JOB

FROM EMP

WHERE DEPTNO = 30;

JOB

-----

CLERK

MANAGER

PRESIDENT

SALESMAN

UNION ALL
Pentru a lista toate liniile (inclusiv duplicatele) generate de fiecare din cereri, introducem:

SELECT JOB

FROM EMP

WHERE DEPTNO = 10

UNION ALL

SELECT JOB

FROM EMP

WHERE DEPTNO = 30;

JOB

-----

PRESIDENT

MANAGER

CLERK

MANAGER

SALESMAN

SALESMAN

SALESMAN

SALESMAN

CLERK

Intersectia
Pentru a lista doar liniile generate de ambele cereri, introducem:

SELECT JOB

FROM EMP

WHERE DEPTNO = 10

INTERSECT

SELECT JOB

FROM EMP

WHERE DEPTNO = 30;

JOB

-----

CLERK

MANAGER

Diferenta
Pentru a lista toate liniile generate de prima cerere care nu sint in a doua cerere, introducem:

SELECT JOB

FROM EMP

WHERE DEPTNO = 10

MINUS

SELECT JOB

FROM EMP

WHERE DEPTNO = 30;

JOB

-----

PRESIDENT

Este posibil sa se construiasca cereri cu mai multi operatori de multimi. Daca sint folositi mai multi operatori de multimi, ordinea executiei pentru declaratiile SQL este de sus in jos.Parantezele pot fi folosite pentru a face ordinea executiei alternativa.

ORDER BY
ORDER BY poate fi folosita o data intr-o cerere ce foloseste operatori de multimi.Daca este folosita, clauza ORDER BY trebuie plasata la sfirsitul cererii.Deoarece pot fi selectate coloane diferite in fiecare SELECT nu se pot numi coloanele in clauza ORDER BY.In scimb, coloanele din ORDER BY trebuie sa fie referite prin pozitiile relative din lista din SELECT.

SELECT EMPNO, ENAME, SAL

FROM EMP

UNION

SELECT ID, NAME, SALARY

FROM EMP_HISTORY

ORDER BY 2;

Observati ca in clauza ORDER BY un numar (2) este folosit pentru a indica pozitia coloanei ENAME in lista din SELECT.Aceasta inseamna ca liniile vor fi sortate in ordine ascendenta a numelui angajatilor.

Reguli pentru folosirea operatorilor de multimi
Declaratiile SELECT trebuie sa aiba acelasi numar de coloane.
Coloanele corespunzatoare trebuie sa aiba acelasi tip (corespondenta este pozitionala).
Liniile duplicate sint automat eliminate (nu poate fi folosit DISTINCT).
Numele coloanelor din prima cerere apar in rezultat.
Clauza ORDER BY apare la sfirsitul declaratiei.
ORDER BY se face doar dupa pozitia coloanei (nu dupa numele coloanei).
Operatorii de multimi pot fi folositi in subcereri.
Blocurile de cerere sint executate de sus in jos.
Operatorii de multimi multipli pot fi folositi cu paranteze, daca este necesara schimbarea secventei executiei.
Exercitii
Aceste exercitii va vor da posibilitatea sa descoperiti diferite metode de join si sa capatati experienta la folosirea operatorilor de multimi.

Tema

Afisati departamentul care nu are nici un angajat.

DEPTNO DNAME

-------- --------

40 OPERATIONS

Afisati toti angajatii (nume si numar) impreuna cu numele si numarul se- fului.

EMPNO ENAME MGRNO MGR_NAME

-------- --------- -------- ------------

7788 SCOTT 7566 JONES

7902 FORD 7566 JONES

7499 ALLEN 7698 BLAKE

7521 WARD 7698 BLAKE

7654 MARTIN 7698 BLAKE

7844 TURNER 7698 BLAKE

7900 JAMES 7698 BLAKE

7934 MILLER 7782 CLARK

7876 ADAMS 7788 SCOTT

7566 JONES 7839 KING

7698 BLAKE 7839 KING

7782 CLARK 7839 KING

7369 SMITH 7902 FORD

Vor fi selectate 13 inregistrari.

Modificati solutia de la intrebarea 2 pentru a afisa KING care nu are sef.

EMPNO ENAME MGRNO MGR_NAME

-------- --------- -------- ------------

7839 KING

7788 SCOTT 7566 JONES

7902 FORD 7566 JONES

7499 ALLEN 7698 BLAKE

7521 WARD 7698 BLAKE

7654 MARTIN 7698 BLAKE

7844 TURNER 7698 BLAKE

7900 JAMES 7698 BLAKE

7934 MILLER 7782 CLARK

7876 ADAMS 7788 SCOTT

7566 JONES 7839 KING

7698 BLAKE 7839 KING

7782 CLARK 7839 KING

7369 SMITH 7902 FORD

Vor fi selectate 14 inregistrari.

Gasiti functia care a fost ocupata in prima jumatate a anului 1983 si aceeasi functie a fost ocupata in timpul aceleiasi perioade in 1984.

JOB

------

CLERK

Va fi selectata o inregistrare.

Incercati si urmatoarele daca aveti timp:

Gasiti toti angajatii care au venit in companie inaintea sefilor lor.

EMPLOYEE HIREDATE MANAGER HIREDATE

----------- ----------- ---------- -----------

ALLEN 15-AUG-83 BLAKE 11-JUN-84

WARD 26-MAR-84 BLAKE 11-JUN-84

MARTIN 05-DEC-83 BLAKE 11-JUN-84

TURNER 04-JUN-84 BLAKE 11-JUN-84

MILLER 21-NOV-83 CLARK 14-MAY-84

JONES 31-OCT-83 KING 09-JUL-84

BLAKE 11-JUN-84 KING 09-JUL-84

CLARK 14-MAY-84 KING 09-JUL-84

SMITH 13-JUN-83 FORD 05-DEC-83

Vor fi selectate 9 inregistrari.

Gasiti alta cerere pentru intrebarea 1.

DEPTNO DNAME

--------- --------

40 OPERATIONS

Solutii

SELECT D.DEPTNO, DNAME

FROM EMP E, DEPT D

WHERE E.DEPTNO(+) = D.DEPTNO

AND E.EMPNO IS NULL;

SELECT EMPS.EMPNO, EMPS.ENAME,

MGRS.EMPNO MGRNO, MGRS.ENAME MGR_NAME

FROM EMP EMPS, EMP MGRS

WHERE EMPS.MGR = MGRS.EMPNO;

SELECT EMPS.EMPNO, EMPS.ENAME,

MGRS.EMPNO MGRNO, MGRS.ENAME MGR_NAME

FROM EMP EMPS, EMP MGRS

WHERE EMPS.MGR = MGRS.EMPNO(+);

SELECT JOB

FROM EMP

WHERE HIREDATE BETWEEN '01-JAN-83' AND '30-JUN-83'

INTERSECT

SELECT JOB

FROM EMP

WHERE HIREDATE BETWEEN '01-JAN-84' AND '30-JUN-84';

SELECT E.ENAME EMPLOYEE, E.HIREDATE,

M.ENAME MANAGER, M.HIREDATE

FROM EMP E, EMP M

WHERE E.MGR = M.MGRNO

AND E.HIREDATE < M.HIREDATE;

SELECT DEPTNO, DNAME

FROM DEPT

MINUS

SELECT EMP.DEPTNO, DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO;