CAPITOLUL 7

EXTRAGEREA DATELOR DIN MAI MULT DE O TABELA

Join

Join-ul este folosit cind o cerere SQL necesita date din mai multe tabele din baza de date.

Liniile dintr-o tabela pot fi unite cu liniile din alta tabela in functie de valorile comune existente in coloanele corespunzatoare.

Sint doua tipuri de conditie de join :

  1. Equi-join
  2. Non-equi-join

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.

Produs

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

 |   EMPNO    ENAME    JOB       MGR   HIREDATE     SAL   COMM   DEPTNO  |

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

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

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

 |   7788     SCOTT   ANALYST   7566   05-MAR-84   3000            20    |---

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

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

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

                                                                            |

                                                                            |

                                                                            |

                                                                            |

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

       |

       |

       |

       |

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

       |        |  DEPTNO    DNAME       LOC    |

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

       |--------|  10    ACCOUNTING   NEW YORK  |

       |--------|  20    RESEARCH     DALLAS    |

       |--------|  30    SALES        CHICAO    |

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

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

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

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] {[tabela].* | expresie [alias], ...}

  FROM tabela [alias], ...

  WHERE [conditie de join] ...

  AND [conditie de linie] ...

  OR [alta conditie de linie]

  GROUP BY {expresie | coloana}

  HAVING {conditie de grup}

  ORDER BY {expresie | coloana} [ASC | DESC]

Observatii
Exercitii - Join-uri simple

Aceste exxercitii au intentia de a va capata experienta practica in extrage- rea datelor din mai mult decit o tabela si includ teme discutate in capitolele anterioare.

Tema

  1. Afisati numele tuturor angajatilor si numele departamentului lor, in ordi- nea numelui departamentelor.
    
         ENAME            DNAME
    
        --------        ---------
    
        CLARK            ACCOUNTING
    
        MILLER           ACCOUNTING
    
        KING             ACCOUNTING
    
        SMITH            RESEARCH
    
        SCOTT            RESEARCH
    
        JONES            RESEARCH
    
        ADAMS            RESEARCH
    
        FORD             RESEARCH
    
        ALLEN            SALES
    
        BLAKE            SALES
    
        TURNER           SALES
    
        JAMES            SALES
    
        MARTIN           SALES
    
        WARD             SALES
    
    
    
        Vor fi selectate 14 inregistrari.
    
    

  2. Afisati numele tuturor angajatilor, numarul si numele departamentului.
    
    
    
    
    
         ENAME        DEPTNO          DNAME
    
        --------    ---------       ---------
    
        CLARK          10           ACCOUNTING
    
        MILLER         10           ACCOUNTING
    
        KING           10           ACCOUNTING
    
        SMITH          20           RESEARCH
    
        SCOTT          20           RESEARCH
    
        JONES          20           RESEARCH
    
        ADAMS          20           RESEARCH
    
        FORD           20           RESEARCH
    
        ALLEN          30           SALES
    
        BLAKE          30           SALES
    
        TURNER         30           SALES
    
        JAMES          30           SALES
    
        MARTIN         30           SALES
    
        WARD           30           SALES
    
    
    
        Vor fi selectate 14 inregistrari.
    
    


  3. Afisati numele, localitatea si departamentul angajatilor al caror salariu lunar este mai mare ca 1500.
    
         ENAME        LOCATION        DNAME
    
        --------     ----------     ---------
    
        CLARK         NEW YORK       ACCOUNTING
    
        KING          NEW YORK       ACCOUNTING
    
        JONES         DALLAS         RESEARCH
    
        FORD          DALLAS         RESEARCH
    
        SCOTT         DALLAS         RESEARCH
    
        ALLEN         CHICAGO        SALES
    
        BLAKE         CHICAGO        SALES
    
    
    
        Vor fi selectate 7 inregistrari.
    
    

  4. Afisati lista salariilor, gradatiilor angajatilor.
    
         ENAME         JOB         SAL        GRADE
    
       ----------    -------     -------    ----------
    
        SMITH        CLERK        800.00         1
    
        ADAMS        CLERK      1,100.00         1
    
        JAMES        CLERK        950.00         1
    
        WARD         SALESMAN   1,250.00         2
    
        MARTIN       SALESMAN   1,250.00         2
    
        MILLER       CLERK      1,300.00         2
    
        ALLEN        SALESMAN   1,600.00         3
    
        TURNER       SALESMAN   1,500.00         3
    
        JONES        MANAGER    2,975.00         4
    
        BLAKE        MANAGER    2,850.00         4
    
        CLARK        MANAGER    2,450.00         4
    
        SCOTT        ANALYST    3,000.00         4
    
        FORD         ANALYST    3,000.00         4
    
        KING         PRESIDENT  5,000.00         5
    
    

  5. Listati doar angajatii cu gradatia 3.
    
         ENAME         JOB         SAL        GRADE
    
       ----------    -------     -------    ----------
    
        ALLEN        SALESMAN   1,600.00         3
    
        TURNER       SALESMAN   1,500.00         3
    
    

  6. Listati toti angajatii din Dallas.
    
         ENAME         SAL       LOCATION
    
       ----------    -------    ----------
    
        SMITH         800.00      DALLAS
    
        SCOTT       3,000.00      DALLAS
    
        JONES       2,975.00      DALLAS
    
        ADAMS       1,100.00      DALLAS
    
        FORD        3,000.00      DALLAS
    
    

    Alte exercitii daca aveti timp:

  7. Afisati numele angajatilor, functia, salariul, gradatia si numele departa- mentului pentru toti angajatii din companie in afara de functionari.Sortati dupa salariu, afisind mai intii salariul cel mai mare.
    
    
    
    
    
        ENAME      JOB        SAL        GRADE      DNAME
    
      --------  ---------   --------   ---------  ---------
    
       KING     PRESIDENT   5,000.00       5       ACCOUNTING
    
       FORD     ANALYST     3,000.00       4       RESEARCH
    
       SCOTT    ANALYST     3,000.00       4       RESEARCH
    
       JONES    MANAGER     2,975.00       4       RESEARCH
    
       BLAKE    MANAGER     2,850.00       4       SALES
    
       CLARK    MANAGER     2,450.00       4       ACCOUNTING
    
       ALLEN    SALESMAN    1,600.00       3       SALES
    
       TURNER   SALESMAN    1,500.00       3       SALES
    
       MARTIN   SALESMAN    1,250.00       2       SALES
    
       WARD     SALESMAN    1,250.00       2       SALES
    
    
    
       Vor fi selectate 10 inregistrari.
    
    

  8. Afisati urmatoarele detalii pentru angajatii care cistiga 36000$ pe an sau care sint functionari.
    
    
    
    
    
         ENAME      JOB     ANNUAL_SAL     DEPTNO     DNAME      GRADE
    
       ---------  ------- --------------  ---------  ---------  --------
    
        FORD      ANALYST    36000           20      RESEARCH      4
    
        SCOTT     ANALYST    36000           20      RESEARCH      4
    
        MILLER    CLERK      15600           10      ACCOUNTING    2
    
        JAMES     CLERK      11400           30      SALES         1
    
        ADAMS     CLERK      13200           20      RESEARCH      1
    
        SMITH     CLERK       9600           20      RESEARCH      1
    
    
    
        Vor fi selectate 6 inregistrari.
    
    
Solutii
  1. 
         SELECT ENAME, DNAME
    
         FROM EMP, DEPT
    
         WHERE EMP.DEPTNO = DEPT.DEPTNO;
    
    

  2. 
         SELECT ENAME, E.DEPTNO, DNAME
    
         FROM EMP E, DEPT D
    
         WHERE E.DEPTNO = D.DEPTNO;
    
    

  3. 
         SELECT ENAME, LOC LOCATION, DNAME
    
         FROM EMP, DEPT
    
         WHERE EMP.DEPTNO = DEPT.DEPTNO
    
         AND SAL > 1500;
    
    

  4. 
         SELECT ENAME, JOB, SAL, GRADE
    
         FROM EMP, SALGRADE
    
         WHERE SAL BETWEEN LOSAL AND HISAL;
    
    

  5. 
         SELECT ENAME, JOB, SAL, GRADE
    
         FROM EMP, SALGRADE
    
         WHERE SAL BETWEEN LOSAL AND HISAL
    
         AND GRADE = 3;
    
    

  6. 
         SELECT ENAME, SAL, LOC LOCATION
    
         FROM EMP, DEPT
    
         WHERE EMP.DEPTNO = DEPT.DEPTNO
    
         AND LOC = 'DALLAS';
    
    

  7. 
         SELECT ENAME, JOB, SAL, GRADE, DNAME
    
         FROM EMP, SALGRADE, DEPT
    
         WHERE EMP.DEPTNO = DEPT.DEPTNO
    
         AND SAL BETWEEN LOSAL AND HISAL
    
         AND JOB != 'CLERK'
    
         ORDER BY SAL DESC;
    
    

  8. 
         SELECT ENAME, JOB, SAL * 12 ANNUAL_SAL, D.DEPTNO, DNAME, GRADE
    
         FROM EMP E, SALGRADE, DEPT D
    
         WHERE E.DEPTNO = D.DEPTNO
    
         AND SAL BETWEEN LOSAL AND HISAL
    
         AND (SAL * 12 + NVL(COMM, 0) = 3600 OR E.JOB = 'CLERK')
    
         ORDER BY E.JOB;