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 :

Equi-join
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

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.

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

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.

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.

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.

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

Listati doar angajatii cu gradatia 3.

     ENAME         JOB         SAL        GRADE
   ----------    -------     -------    ----------
    ALLEN        SALESMAN   1,600.00         3
    TURNER       SALESMAN   1,500.00         3

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:

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.

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

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

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

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

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

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

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

     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;

     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;