TIPURI DE JOIN - Oracle

TIPURI DE JOIN - Oracle

Equi-join

Pentru a determina angajatii din departamente, vom compara valorile din coloana DEPTNO a angajatului cu aceleasi valori din DEPTNO din tabela DEPT. Relatia dintre tabela EMP si DEPT este un equi-join, in care valorile din coloana DEPTNO din ambele tabele sint egale. (Operatorul de comparatie folosit este =.)

O conditie de join este specificata in clauza WHERE:

SELECT column(s)

FROM tables

WHERE join condition is

Pentru a face join pe cele doua tabele EMP si DEPT, introducem:

SELECT ENAME, JOB, DNAME

FROM EMP, DEPT

WHERE EMP>DEPTNO + DEPT>DEPTNO;

ENAME JOB DNAME

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

CLARK MANAGER ACCOUNTING

MILLER CLERK ACCOUNTING

KING PRESIDENT ACCOUNTING

SMITH CLERK RESEARCH

SCOTT ANALYST RESEARCH

JONES MANAGER RESEARCH

ADAMS CLERK RESEARCH

FORD ANALYST RESEARCH

ALLEN SALESMAN SALES

BLAKE MANAGER SALES

TURNER SALESMAN SALES

JAMES CLERK SALES

MARTIN SALESMAN SALES

WARD SALESMAN SALES

Vom observa ca acum fiecare angajat are listat numele departamentului lui.
Liniile din EMP sint combinate cu liniile din DEPT si sint intoarse doar liniile pentru care valorile EMP.DEPTNO si DEPT.DEPTNO sint eale.

Observati ca, conditia de join specifica numele coloanei precedat de numele tabelei.Aceasta este o necesitate cind numele coloanelor sint aceleasi in ambele tabele.Este necesar sa specificam exact ce coloane sint referite.

Aceasta necesitate este de asemenea aplicata coloanelor care pot fi ambigue in clauzele SELECT sau ORDER BY.

Pentru a recunoaste diferentele dintre coloana DEPTNO din EMP si coloana DEPTNO din DEPT, introducem:

SELECT DEPT.DEPTNO, ENAME, JOB, DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO

ORDER BY DEPT.DEPTNO;

DEPTNO ENAME JOB DNAME

10 CLARK MANAGER ACCOUNTING

10 MILLER CLERK ACCOUNTING

10 KING PRESIDENT ACCOUNTING

20 SMITH CLERK RESEARCH

20 SCOTT ANALYST RESEARCH

20 JONES MANAGER RESEARCH

20 ADAMS CLERK RESEARCH

20 FORD ANALYST RESEARCH

30 ALLEN SALESMAN SALES

30 BLAKE MANAGER SALES

30 TURNER SALESMAN SALES

30 JAMES CLERK SALES

30 MARTIN SALESMAN SALES

30 WARD SALESMAN SALES

Observati ca fiecarui numar de departament din tabela DEPT i se face join pentru a se potrivi cu numerele de departament din tabela EMP.De exemplu, trei angajati lucreaza in departamentul 10 - Accounting - deci exista trei potri- viri.Prin urmare ACCOUNTING este afisat pentru fiecare angajat din acel depar- tament.

Folosirea alias-urilor de tabela

Poate fi foarte plictisitor sa tiparesti numele tabelelor repetat.Etichete temporare (sau alias-uri) pot fi folosite in clauza FROM.Aceste nume temporare sint valide doar in instructiunea SELECT curenta.Alias-urile de tabele trebuie de asemenea sa fie specificate in clauza SELECT.Aceasta creste efectiv viteza cererii, in care contextul contine informatii foarte exacte.

Alias-urile de tabela sint folosite in urmatorul context:

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

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO

ORDER BY D.DEPTNO;

Alias-urile de tabele pot fi de lungime de maxim 30 de caractere, dar mai scurte sint mai bune.De asemenea incercati sa le faceti cit mai explicite.

Daca un alias de tabela este folosit pentru un nume particular de tabela in clauza FROM, atunci acel alias trebuie sa fie substituit pentru numele tabelei in contextul din SELECT.

In absenta unei conditii WHERE, fiecare linie din EMP este unita in ordine cu fiecare linie din DEPT.

Se vor afisa 53 de linii.

Produs CARTEZIAN

Cind o conditie de join este invalida sau este omisa, rezultatul este omis si toate combinatiile de linii vor fi listate.

Un produs tinde sa genereze un numar mare de linii si rezultatul sau este rar folosit.Trebuie intotdeauna inclusa o conditie de join intr-o clauza WHERE, in afara de cazul in care este necesara combinarea tuturor liniilor din toate tabelele.

Non-Equi-Join

Relatia dintre tabelele EMP si SALGRADE este un non-equi-join, in care nici o coloana din EMP nu corespunde direct cu o coloana din SALGRADE.Relatia este obtinuta folosind un operator, altul decit operatorul de egalitate (=).Pentru a evalua gradatia unui anajat, salariul lui trebuie sa fie intre salariul minim si salariul maxim.
Operatorul BETWEEN este folosit pentru a construi conditia, introducem:

SELECT E.ENAME, E.SAL, S.GRADE

FROM EMP E, SALGRADE S

WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

ENAME SAL GRADE

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

SMITH 800.00 1

ADAMS 1,100.00 1

JAMES 950.00 1

WARD 1,250.00 2

MARTIN 1,250.00 2

MILLER 1,300.00 2

ALLEN 1,600.00 3

TURNER 1,500.00 3

JONES 2,975.00 4

BLAKE 2,850.00 4

CLARK 2,450.00 4

SCOTT 3,000.00 4

FORD 3,000.00 4

KING 5,000.00 5

Alti operatori cum ar fi <= si >= pot fi folositi, oricum BETWEEN este cel mai simplu.Nu uitati sa specificati mai intii valoarea minima si ultima va- loarea maxima cind folositi BETWEEN.Din nou sint folosite alias-uri de tabele, nu din cauza posibilelor ambiguitati, dar din motive de performanta.

Reguli pentru join-ul tabelelor

Pentru a face join pe cele trei tabele este necesar sa construim doua condi- tii de join.Pentru a face join pe patru tabele sint necesare minim trei condi- tii de join.

O regula simpla este:

numarul minim de conditii de join = numarul de tabele - 1

Aceasta regula nu poate fi aplicata daca tabela are o cheie primara, care identifica in mod unic fiecare linie (cheile primare sint explicate mai tirziu in manual).

Sintaxa

SELECT [DISTINCT]

FROM tabela [alias]

WHERE [conditie de join

AND [conditie de linie]

OR [alta conditie de linie]

GROUP BY

HAVING

ORDER BY [ASC | DESC]

Observatii

• Se pot specifica conditii de join impreuna cu alte conditii (non join);
• De asemenea trebuie sa fiti atenti la precedenta operatorilor cind folo- siti predicatul OR.

ALTE METODE DE JOIN

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.

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 SI se va numi left JOINdaca + e in dreapta si right JOIN invers.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

1. Declaratiile SELECT trebuie sa aiba acelasi numar de coloane.
2. Coloanele corespunzatoare trebuie sa aiba acelasi tip (corespondenta este pozitionala).
3. Liniile duplicate sint automat eliminate (nu poate fi folosit DISTINCT).
4. Numele coloanelor din prima cerere apar in rezultat.
5. Clauza ORDER BY apare la sfirsitul declaratiei.
6. ORDER BY se face doar dupa pozitia coloanei (nu dupa numele coloanei).
7. Operatorii de multimi pot fi folositi in subcereri.
8. Blocurile de cerere sint executate de sus in jos.
9. Operatorii de multimi multipli pot fi folositi cu paranteze, daca este necesara schimbarea secventei executiei.