AfiSarea datelor din tabele multiple

Obiective

Dupa completarea acestei lectii, veti putea face urmatoarele

Scrieti expresii SELECT pentru a accesa date din mai multe tabele folosind legaturi (join-uri) de egalitate si inegalitate

Vizualizati date care in general nu se acceseaza printr-o conditie join simpla ci folosind join-uri exterioare

Efectuarea unui join a unui tabel cu el insusi.

Scopul lectiei

Aceasta lectie cuprinde cum sa obtinem date din mai mult de un singur tabel, folosind diferite metode disponibile.

Obtinerea datelor mai multe tabele

EMP DEPT

Date din mai multe tablele

Cateodata este nevoie de folosit date din mai multe tabele. In exemplul de mai sus, rezultatul afiseaza date din doua tabele separate.

EMPNO exista in tabelul EMP

DEPTNO exista in ambele tabele EMP si DEPT

LOC exista in tabelul DEPT.

Pentru a se ajunge la rezultatul final, tabelele EMP si DEPT trebuie legate si accesa date din amandoua.

Ce este un JOIN

Folositi un join pentru a interoga datele din mai multe tabele.

Scrie conditia JOIN in clauza WHERE.

Prefixeaza numele coloanei cu numele tabelului cand acelasi nume de coloana apare in mai mult de un tabel.

Definirea JOIN-urilor

Cand trebuie obtinute datele din mai multe tablele din baza de date este folosita o conditie de join. Liniile dintr-un tabel pot fi alaturate la liniile din alt tabel conform cu valorile comune existente in coloanele corespondente, care sunt de obicei, coloane chei primare si straine.

Pentru a afisa date din doua sau mai multe tabele in relatie, scrieti o simpla conditie join in clauza WHERE.

In sintaxa:

table.column denota tabelul si coloana de unde este extrasa data

table1.column1 este conditia care alatura (leaga)

table2.column2 tabelele impreuna

Observatii

Cand scrieti o expresie SELECT care leaga tabele, precedati numele coloanei cu numele tabelului pentru claritate si pentru a mari viteza accesului la baza de date.

Daca acelasi nume de coloana apare in mai multe tabele, numele coloanei trebuie prefixat cu numele tabelului.

Pentru a alatura n tabele ampreuna, este nevoie de minim n-1 conditii de join. Asadar, pentru a alatura 4 tabele, sunt necesare 3 join-uri. Aceasta regula s-ar putea sa nu se aplice daca tabelul are o cheie primara concatenata, si astfel mai mult de o coloana este necesara pentru a identifica in mod unic fiecare linie.

Produsul Cartezian

Un produs cartezian este format cand:

- o conditie join este omisa

- o conditie join este invalida

- toate liniile in primul tabel sunt alaturate la liniile din tabelul al doilea

Pentru a evita un produs cartezian, includeti totdeauna o conditie join valida intr-o clauza WHERE

Produsul Cartezian

Cand o conditie join este valida sau complet omisa, rezultatul este un produs cartezian in care vor fi afisate toate combinatiile de linii.

Toate liniile din primul tabel sunt alaturate la toate liniile din al doilea tabel.

Un produs cartezian tinde sa genereze un numar mare de linii, si rezultatul sau este rar folositor. Trebuie inclus intotdeauna o conditie join valida intr-o clauza WHERE; cu exceptia cazului cand se doreste in mod explicit combinarea tuturor liniilor din tabele.

Generarea unui produs cartezian

Un produs cartezian este generat daca o conditie join este omisa. Exemplul alaturat afiseaza numele angajatului si numele departamentului din tabelele EMP si DEPT. Deoarece nu au fost specificate nici o clauza WHERE, toate liniile (14) din tabelul EMP sunt alaturate cu toate liniile (4) din tabelul DEPT, generand astfel 56 de linii in tabelul de iesire.

SQL > SELECT ename, dname      2 FROM emp, dept;

ENAME               DNAME ----- ----- --------- ----- ----- ----------- KING ACCOUNTING BLAKE ACCOUNTING KING RESEARCH BLAKE RESEARCH 56 rows selected.

Tipuri de Join-uri

Equijoin Non-equijoin Outer join Self join

Exista doua tipuri importante de conditii join:

Echijoin-uri

Non-echijoin-uri

Metode aditionale de joinuri includ urmatoarele:

Outer join

Self join

Operatori

Ce este un Echijoin ?

EMP DEPT

Cheie straina Cheie primara

Echijoin-uri

Pentru a determina numele departamentului unui angajat, trebuie comparat valoarea din coloana DEPTNO din tabelul EMP cu valorile DEPTNO din tabelul DEPT. Relatia dintre tabelele EMP si DEPT este un echijoin - aceasta insemand ca, valorile din coloana DEPTNO din ambele tabele trebuie sa fie egale. Frecvent, acest tip de join-uri implica complementele cheilor primare si straine.

Referat: Referat la informatica - retele de calculatoare Eseu: Liste circulare simplu inlantuite

Extragerea inregistrarilor cu Echijoin-uri

In exemplul de mai sus:

Clauza SELECT specifica numele de coloane de extras

- numele angajatului, numarul angajatului si numarul departamentului, care sunt coloane in tabelul EMP;

- numarul departamentului, numele departamentului si locatia, sunt coloane in tabelul DEPT.

Clauza FROM specifica cele 2 tabele pe care baza de date trebuie s-o acceseze:

tabelul EMP

tabelul DEPT

Clauza WHERE specifica felul in care tabelele vor fi alaturate

Deoarece coloana DEPTNO este comuna la ambele tabele, trebuie prefixata cu numele tabelului pentru a evita ambiguitatea.

Calificarea numelor de coloane ambigui

Folositi prefixele tabelelor pentru a califica numele coloanelor din mai multe tabele

Imbunatatiti performanta prin folosirea prefixelor de tabele

Distingeti coloanele care au nume identice in tabele diferite prin folosirea aliasurilor de coloane.

Trebuie sa calificati numele coloanelor in clauzele WHERE cu numele tabelului pentru a evita ambiguitatea. Fara prefixurile tabelelor, coloana DEPTNO ar putea fi din tabela DEPT sau EMP. Este necesar adaugarea prefixului de tabel pentru a executa interogarea.

Daca nu sunt nume de coloane comune intre cele doua tabele, nu este nevoie calificarea coloanelor. Oricum, veti castiga performanta mai mare prin folosirea prefixului de tabel deoarece se cere Serverului Oracle exact unde sa gaseasca coloanele.

Cererile de calificare a numelor de coloane ambigui sunt de asemenea aplicabile la coloanele care pot fi ambigui in alte clauze cum ar fi SELECT sau ORDER BY.

Conditii de cautare aditionale folosind opeatorul AND

EMP DEPT

In plus la join, puteti avea criterii aditionale pentru clauza WHERE. De exemplu, pentru a afita numarul de angajat al angajatului KING, numele, numarul departamentului si locatia departamentului, este nevoie de o conditie in plus in clauza WHERE.

Folosirea de Aliasuri la Tabele

Simplificati interogarile folosind aliasurile de tabele

Atasarea numelor coloanelor cu numele tabelului poate consuma mult timp, mai ales daca numele tabelului este lung. Se pot folosi aliasuri de tabele in locul numelor tabelelor. Asa cum un alias de coloana da unei coloane un alt nume, un alias de tabel ii da acestuia alt nume. Aliasurile de tabel ajuta la mentinerea codului SQL mai mic, si astfel folosirea mai putina a memoriei.

De notat faptul cum aliasurile de tabel sunt identificate in clauza FROM in exemplul de mai sus. Numele tabelului este specificat pe de-a intregul, urmat de un spatiu si apoi aliasul. Tabelul EMP i-a fost dat un alias E, iar tabelul DEPT are un alias D.

Observatii:

Aliasurile de tabel pot avea pana la 30 caractere lungime, dar cu cat sunt mai scurte cu atat mai bine;

Daca aliasul unui tabel este folosit pentru un nume de tabel particular in clauza FROM, atunci acel alias de tabel trebuie sa fie substituitentul pentru numele tabelului pe tot cuprinsul expresiei SELECT;

Aliasurile de tabel ar trebui sa aiba sens;

Aliasul de tabel este valid numai pentru SELECT-ul curent.

JOIN folosind mai mult de 2 tabele

CUSTOMER ORD

ITEM

Conditii de cautare aditionale

Uneori este nevoie de alaturat (join) mai mult de doua tabele. De exemplu, pentru a afisa numele, plasarea in ordine, numerele item-urilor, totalul fiecarui item, si totalul pentru fiecare ordin pentru clientul TKB SPORT SHOP, va trebui alaturate (join) tabelele CUSTOMER, ORD si ITEM.

Non-echijoin-uri

EMP

'salarul din tabelul EMP este intre salarul

minim si maxim in tabelul SALGRADE'

Relatia dintre tabelul EMP si SALGRADE este un non-echijoin, adica nici o coloana din tabelul EMP nu corespunde direct unei coloane in tabelul SALGRADE. Relatia dintre cele doua tabele este astfel: coloana SAL din EMP este intre coloanele LOSAL si HISAL din tabelul SALGRADE. Relatia (legatura) se obtine folosind un operator, altul decat egal (=).

Extragerea inregistrarilor cu Non-echijoin-uri

Exemplul de sus creeaza un non-echijoin pentru a evalua gradul salarului unui angajat. Salarul trebuie sa fie intre orice pereche 'cel mai mic' - 'cel mai mare' a intervalelor salariale.

Este important de remarcat ca toti angajatii apar exact o data cand aceasta interogare este executata. Nici un angajat nu este repetat in lista. Sunt doua motive pentru aceasta:

Nici o linie din tabelul cu gradele salariale nu contine grade care se suprapun (cu valorile pentru alte grade). Astfel, valoarea salarului unui angajat poate oscila numai intre salariul minim si maxim din una din linii in tabelul gradelor salariale.

Toate salariile angajatilor sunt intre limitele date de tabelul gradelor salariale. Astfel, nici un angajat nu castiga mai putin decat cea mai mica valoare din coloana LOSAL sau mai mult decat cea mai mare valoare continuta in coloana HISAL.

Observatie: Alti operatori, cum ar fi <= si >= pot fi folositi, dar este mai simplu de folosit BETWEEN. Amintiti-va sa specificati valoarea minima intai si valoarea maxima atunci cand folositi BETWEEN. Aliasurile de tabele au fost specificate din motive de performanta, si nu datorita unei posibile ambiguitati.

Join-uri externe

EMP DEPT

Nici un angajat in departamentul OPERATIONS

Returnarea inregistrarilor cu NoDirectMatch (Portivire Indirecta) cu Joinuri externe.

Daca o linie (inregistrare) nu satisface conditia de join, acea linie nu va aparea in rezultatul interogarii. De exemplu, in conditia de echijoin a tabelelor EMP si DEPT, departamentul OPERATIONS nu va aparea pentru ca nimeni nu lucreaza in acel departament.

	ENAME DEPTNO DNAME KING ACCOUNTING BLAKE SALES CLARK ACCOUNTING JONES RESEARCH ALLEN SALES TURNER SALES JAMES SALES 14 rows selected

Outer Joins

Folositi un join extern pentru a vedea de asemenea linii care nu indeplinesc conditia de join

Operatorul join extern este semnul plus (+).

Returnarea inregistrarilor cu potrivire indirecta cu ajutorul joinului extern

Linia (liniile) lipsa pot fi returnate daca un join extern este folosit in conditia join. Operatorul este un semn 'plus' intre paranteze (+), si este plasat in 'aria' lui join ceea ce e deficient in informatie. Acest operator are efectul crearii a unei sau mai multe linii goale (nule), pentru fiecare linie din tabelul nondeficient care poate fi facut join.

In sintaxa:

table1.column = este conditia care face join la tabele

table2.column(+) este simbolul pentru join extern; poate fi plasat in

oricare parte a conditiei clauza WHERE, dar nu in ambele parti deodata. Plasati simbolul join extern dupa numele coloanei din tabelul deficitar in informatii.

Folosirea Join-urilor Externe

Exemplul de mai sus afiseaza numere si nume pentru toate departamentele. Departamentul OPERATIONS, care nu are nici un angajat este de asemenea afisat.

Restrictii la Join extern

Operatorul join extern poate aparea numai intr-o singura parte a unei expresii - partea care are informatia care lipseste. El returneaza acele linii din tabel care nu au corespondent direct in celalalt tabel.

O conditie implicata in join extern nu poate fi operatorul IN sau sa fie lagata unei alte conditii de operatorul OR.

Join al aceluiasi tabel

EMP (WORKER) EMP (MANAGER)

'MGR in tabelul WORKER este egal cu EMPNO

in tabelul MANAGER

Uneori este nevoie de alaturat (join) un tabel cu el insusi. Pentru a gasi numele fiecarui angajat al unui manager este nevoie de alaturat (join) insusi tabelul EMP. De exemplu, pentru a gasi numele managerului lui Blake, este nevoie sa:

- gasiti pe Blake in tabelul EMP uitandu-va la coloana ENAME

- gasiti numarul managerului pentru Blake uitandu-va la coloana

MGR. Numarul managerului lui Blake este 7839.

gasiti numele managerului cu EMPNO 7893 uitandu-va la coloana ENAME. Numarul angajatului King este 7839. Deci, King este managerul lui Blake.

In acest proces, va uitati (cautati) in tabel de doua ori. Prima data va uitati in tabel pentru a-l gasi pe Blake in coloana ENAME si valoarea MGR este 7839. A doua oara va uitatt in coloana EMP pentru a gasi 7839 si in coloana ENAME pentru a gasi King.

Alaturarea unui tabel cu el insusi

Exemplul de mai sus alatura EMP cu el insusi. Pentru a simula cele doua tabele in clauze FROM, se folosesc doua aliasuri, numite WORKER si MANAGER, pentru acelasi tabel, EMP.

In acest exemplu, clauza WHERE contine join-ul care inseamna 'unde numarul unui lucrator al unui manager gaseste numarul angajatului pentru manager'.

Cuprins

Equijoin Non-equijoin Outer join Self join

Exista feluri multiple de a alatura tabele. Firul comun, este legarea lor printr-o conditie in clauza WHERE. Metoda pe care o veti alege se va baza pe rezultatele cerute si structurate de date folosite.

	SELECT   table1.column, table2.column FROM      table1, table2 WHERE   table1.column1 = table2.column2;

Exercitii - rezumat

Alaturarea tabelelor folosind echijoin

Folosirea join-urilor externe si interne

Adaugarea conditiilor suplimentare

Exercitii 4

Scrieti o interogare care sa afiseze numele, numarul departamentului si numele departamentului pentru toti angajatii.

2. Creeati un listing unic pentru toate meseriile (jobs) din departamentul 30.

Scrieti o interogare care afiseaza numele angajatului, numele departamentului si locatia tuturor angajatilor care castiga un comision.

Afisati numele angajatului si numele departamentului pentru toti angajatii care au un A in numele lor. Salvati tabelul SQL intr-un fisier numit p4q4.sql.

Scrieti o interogare care afiseaza numele, meseria, numarul departamentului, si numele departamentului pentru toti angajatii care lucreaza in DALLAS.

Afisati numele angajatului si numarul lui impreuna cu numele managerului si numarul acestuia. Etichetati coloanele Employee, Emp#, Manager, Mgr#, respectiv. Salvati tabelul SQL intr-un fisier numit p4q6.sql.

Modificati p4q6.sql pentru a afisa toti angajatii incluzand pe King care nu are manager.

Creati o interogare care va afisa numele angajatului, numarul departamentului, si toti angajatii care lucreaza in acelasi departament cu un angajat dat. Dati fiecarei coloane o eticheta expresiva.

9. Aratati structura tabelului SALGRADE. Creeati o interogare care va afisa numele, meseria, numele departamentului, salarul, si gradul pentru toti angajatii.

Creeati o interogare care afiseaza numele si data angajarii pentru lucratorii angajati dupa data de angajare a lui Blake.

Afisati toate numele angajatilor si datele de angajare impreuna cu numele managerilor si data lor de angajare, pentru toti cei care au fost angajati inaintea managerilor lor. Etichetati coloanele Employee, respectiv Emp, Hiredate, Manager, si Mgr Hiredate.

12. Creeati o interogare care afiseaza numele angajatilor si salariile indicate prin asteriscuri. Fiecare asterisc inseamna 1000 $. Sortati datele in ordine descendenta a salariilor. Etichetati coloana EMPLOYEE_AND_THEIR_SALARIES.

	ENAME EMPLOYEE_AND_THEIR_SALARIES KING FORD SCOTT JONES BLAKE CLARK ALLEN TURNER MILLER MARTIN WARD ADAMS JAMES SMITH 14 rows selected.