Subinterogari de coloane multiple

Obiective :

Dupa parcurgerea acestei prelegeri veti fi capabili pentru a realiza urmatoarele :

Sa scrieti subinterogari de coloane multiple

Sa descrieti si sa explicati subinterogarile atunci cand sunt intoarse valori nulle

Sa scrieti subinterogari in clauze FROM

Obs.: In acesta prelegere, veti invata cum sa scrieti subinterogari de coloane multiple si subinterogari in clauza FROM a secventei SELECT

Subinterogari de coloane multiple

Interogare principala

MANAGER 10

Subinterogare

SALESMAN 30

MANAGER 10

CLERK 20

Interogarea principala comparata cu valorile provenite de la subinterogari de linii multiple si de

coloane multiple.

Pana acum ati scris subinterogari folosind o singura linie si linii multiple , unde o singura coloana a fost comparata in clauza WHERE sau clauza HAVING a instructiunii SELECT.

Daca vreti sa comparati una sau mai multe coloane, trebuie sa scrieti clauza WHERE ,folosind operatorii logici.

Acest tip de subinterogari va da posibilitatea sa realizati din doua clauze WHERE una singura.

Sintaxa :

SELECT coloana,coloana, .

FROM tabela

WHERE (coloana,coloana, . ) IN

(SELECT colana,coloana .

FROM tabela

WHERE conditie);

Folosirea acestui tip de subinterogari

Afisati numele ,numarul departamentului,salariul si comisionul oricarui angajat , a carui salariu si comision se potrivesc cu salariul si comisionul oricarui angajat din departamentul 30.

SQL> SELECT ename, deptno, sal, comm

2 FROM emp

3 WHERE (sal,NVL(comm,-1)) IN

4 (SELECT sal, NVL(comm,-1)

5 FROM emp

6 WHERE deptno=30);

Rezultatul cererii de mai sus:

In exemplul de mai sus sunt folosite subinterogari de coloane multiple care returneaza mai mult de o coloana. Aceasta compara coloana SAL si coloana COMM.

Selectia afiseaza numele, numarul departamentului, salariul, comisionul oricarui angajat a carui salariu si comision se potrivesc cu salariul si comisionul oricarui angajat din departamentul 30.

Compararea coloanelor

Comparatiile coloanelor intr-o subinterogare de coloane multiple, poate fi in perechi sau in neperechi . In exemplul anterior, o comparatie pereche a fost executata in clauza WHERE . Fiecare linie returnata de comanda SELECT trebuie sa aiba accelasi salar si comision cu a unui angajat din departamentul 30 .

Daca vreti o comparatie nepereche (complexa) , trebuie sa folositi clauza WHERE cu multiple conditii .

Exemplul urmator foloseste comparatia nepereche (complexa) pentru a afisa numele, numarul departamentului, salarul si comisionul oricarui angajat a carui salariu si comision se potrivesc cu salariul si comisionul oricarui angajat din departamentul 30 :

SQL>SELECT ename, deptno, sal, comm

2 FROM emp

3 WHERE sal IN (SELECT sal

4 FROM emp

5 WHERE deptno=30)

6 AND

7 NVL(comm, -1) IN (SELECT NVL(comm, -1)

8 FROM emp

9 WHERE deptno=30);

Rezultatul cererii de mai sus :

Modificarea tabelei EMP

Se presupune ca salariul si comisionul lui Clark se modifica

Salariul se schimba in 1500$ si comisionul in 300$

Se presupune ca salariul si comisionul angajatului Clark se modifica astfel incat acesta are accelasi salariu ca al oricarui angajat din departamentul 30 si accelasi comision ca al altui angajat din departamentul 30 .

Salariul lui Clark este acum egal cu al lui Turner (1500$) si comisionul cu cel al lui Allan (300$) .

Acum rulati o comparatie pereche si una nepereche pentru a determina numarul liniilor returnate de fiecare interogare .

Subinterogare pereche

SQL> SELECT ename , deptno , sal , comm

2 FROM emp

3 WHERE (sal,NVL(comm, -1)) IN

4 (SELECT sal, NVL(comm, -1)

5 FROM emp

6 WHERE deptno=30);

Subinterogare nepereche

SQL> SELECT ename , deptno , sal , comm

FROM emp

3 WHERE sal IN (SELECT sal

FROM emp

WHERE deptno=30)

AND

7 NVL(comm,-1) IN (SELECT NVL(comm,-1)

8 FROM emp

WHERE deptno=30);

Rezultatul interogarii de mai sus include si angajatul Clark. Salariul acestuia este accelasi cu al lui Turner si comisionul este accelasi cu al lui Allen .

Returnarea valorilor nulle in rezultatul subinterogarii

SQL> SELECT employee.ename

2 FROM emp employee

3 WHERE employee.empno NOT IN

4 (SELECT manager.mgr

5 FROM emp manager);

no rows selected.

Comanda SQL de mai sus afiseaza toti angajatii care nu au nici un subordonat. Logic ,acesta interogare SQL ar trebui sa returneze 8 linii . Dar SQL nu returneaza nici una . Una din valoarea returnata de interogare este o valoare nulla si prin urmare intreaga interogare nu returneaza nici o linie.

Motivul este ca toate aceste conditii care compara rezultatul unei valori nulle se transforma in null . Cand valorile nulle exista in rezultatul subinterogarii , nu folositi operatorul NOT IN .

Operatorul NOT IN este echivalent cu !=ALL . Aveti in vedere ca valorile nulle ale rezultatului subinterogarii nu vor constitui o problema daca folositi operatorul IN . Operatorul IN este echivalent cu operatorul ANY . De exemplu,pentru afisarea angajatilor care au subordonati , folositi urmatoarea exprimare SQL :

SQL> SELECT employee.ename

2 FROM emp employee

3 WHERE employee.empno IN

4 (SELECT manager.mgr

5 FROM emp manager);

Folosirea unei subinterogari in clauza FROM

Se poate folosi o subinterogare in caluza FROM a exprimarii SELECT .

Exemplul urmator afiseaza numele angajatilor , salariile , numarul departamentelor si media salariilor pentru toti angajatii care realizeaza mai mult decat media salariilor din departamentul in care lucreaza.

SQL> SELECT a.ename , a.sal , a.deptno , b.salavg

FROM emp a , (SELECT deptno , AVG(sal) salavg

FROM emp

GROUP BY deptno ) b

WHERE a.deptno = b.deptno

AND a.sal > b.salavg;

SUMAR

O subinterogare de coloane multiple returneaza mai mult de o coloana

Compararea coloanelor pot fi in pereche sau in nepereche (complexe)

O subinterogare de coloane multiple poate fi de folos in clauza FROM a exprimarii SELECT .

Exercitii

Scrieti o interogare care sa afiseze numele , numarul departamentului si salariul oricarui angajat al carui numar de departament si salariu sa se potriveasca cu numarul departamentului si salariul oricarui angajat care percepe comision.

Afisati numele , numele departamentului si salariul oricarui angajat al carui salariu si comision se potrivesc cu salariul si comisionul oricarui angajat localizat in Dallas.

Scrieti o interogare care sa afiseze numele , data angajarii si salariul pentru toti angajatii care au acelasi salariu si comision ca al lui Scott.

Scrieti o interogare care sa afiseze angajatii care castiga un salariu mai mare ca al oricarui angajat functionar (CLERKS) . Afisati salariile de la cel mai mare la cel mai mic.