CAPITOLUL 8

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.


 ------------------------------------------------------------------------
 |                    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;