Una din cele mai importante facilitati ale SQL consta in includerea unei consultari in alta, pe doua sau mai multe nivele, altfel spus, utilizarea subconsultarilor. Prin subconsultari se obtin tabele temporare intermediare folosite ca "argumente" ale frazelor SELECT superioare.
7.1.1 Operatorul IN in subconsultari
Operatorul cel mai utilizat in materie de subconsultari este IN pe care l-am intalnit deja intr-un paragraf precedent intr-o cu totul alta ipostaza - testarea incadrarii valorii unui atribut intr-o lista de constante. Pentru cele ce urmeaza, domeniul de test al este alcatuit din liniile unei tabele obtinute printr-o (sub)consultare.
Revenim (pentru a doua oara) la exemplul 19 din algebra relationala: Ce facturi au fost emise in aceeasi zi cu factura 1120 ? Intr-un paragraf anterior a fost formulata o solutie bazata pe jonctiunea a doua instante ale tabelei FACTURI. Iata si o solutie mai simpla bazata pe subconsultari:
SELECT NrFact FROM facturi WHERE DataFact IN
(SELECT DataFact FROM facturi WHERE NrFact=1120)
Executia acestei interogari se deruleaza in doi timp. Mai intai, se executa sub-consultarea SELECT DataFact FROM facturi WHERE NrFact=1120 obtinandu-se o tabela intermediara cu o singura linie si o singura coloana (DataFact). In al doilea pas sunt selectate liniile tabelei FACTURI care indeplinesc conditia DataFact = '2007-08-07'. In rezultat a fost inclusa si factura de referinta - 1120. Daca se doreste excluderea acesteia, fraza SELECT se modifica astfel:
SELECT NrFact FROM facturi WHERE DataFact IN
(SELECT DataFact FROM facturi WHERE NrFact=1120)
AND NrFact <> 1120
Ce facturi au fost emise in alte zile decat factura 1120 ?
Acest exemplu necesita folosirea operatorului de negatie:
SELECT NrFact FROM facturi WHERE DataFact NOT IN
(SELECT DataFact FROM facturi WHERE NrFact=1120)
Care sunt clientii carora li s-au trimis facturi in aceeasi zi in care a fost intocmita factura 1120 ?
SELECT DenCl FROM clienti WHERE CodCl IN
(SELECT CodCl FROM facturi WHERE DataFact IN
(SELECT DataFact FROM facturi WHERE NrFact=1120) )
Rezultatul prezentat in figura 7.1 se obtine folosind trei nivele de interogare (fraza principala, o sub-consultare si o sub-sub-consultare).
Figura 7.1. Clientii pentru care exista facturi emise in aceeasi zi ca 1120
Anul II - Contabilitate si Informatica de Gestiune (zi si ID) + Informatica Economica - 2008/09 3
In ce judete s-a vandut produsul "Produs 2" ?
Am ales acest exemplu pentru a "vantura", prin subconsultari, cat mai multe tabele ale bazei:
SELECT Judet FROM judete WHERE Jud IN
(SELECT Jud FROM coduri_postale WHERE CodPost IN
(SELECT CodPost FROM clienti WHERE CodCl IN
(SELECT CodCl FROM facturi WHERE NrFact IN
(SELECT NrFact FROM liniifact WHERE CodPr IN
(SELECT CodPr FROM produse WHERE DenPr = 'Produs 2') ) ) ) )
Revenim la tabela PERSONAL2 din figura 6.22: Cati subordonati directi are ANGAJAT 2 ?
La aceasta problema (la care raspunsul este 2) formulam, pentru comparatie, doua solutii. Solutia bazata pe jonctiune este:
SELECT COUNT(*) AS NrSubordonati
FROM personal2 SUBORDONATI, personal2 SEFI
WHERE SUBORDONATI.MarcaSef=SEFI.Marca AND SEFI.NumePren='ANGAJAT 2'
A doua solutia utilizeaza o subconsultare:
SELECT COUNT(Marca) AS NrSubordonati
FROM personal2
WHERE MarcaSef IN
(SELECT Marca FROM personal2 WHERE NumePren='ANGAJAT 2')
Tot prin subconsultari putem realiza intersectia si diferenta relationala. Raportandu-ne la intersectia a doua relatii, R1 si R2, operatiunea se poate realoza in SQL si astfel:
SELECT * FROM R1 WHERE (A,B,C) IN (SELECT C,D,E FROM R2)
Un alt exemplu de intersectie, In ce zile s-au vandut si produsul cu denumirea "Produs 1" si cel cu denumirea "Produs 2" ?, se poate rezolva si astfel:
SELECT DISTINCT DataFact
FROM produse p
INNER JOIN liniifact lf ON p.CodPr = lf.CodPr
INNER JOIN facturi f ON lf.Nrfact = f.NrFact
WHERE DenPr = 'Produs 1' AND DataFact IN
(SELECT DISTINCT DataFact FROM produse p INNER JOIN liniifact lf
ON p.CodPr = lf.CodPr INNER JOIN facturi f ON lf.Nrfact = f.NrFact
WHERE DenPr = 'Produs 2')
Cat priveste diferenta relationala, cheia rezolvarii este NOT IN. Ce clienti au cumparat si "Produs 2" si "Produs 3", dar nu au cumparat "Produs 5" ?
SELECT DISTINCT DenCl
FROM produse p
INNER JOIN liniifact lf ON p.CodPr = lf.CodPr
INNER JOIN facturi f ON lf.NrFact = f.NrFact
INNER JOIN clienti c ON f.CodCl = c.CodCl
WHERE DenPr = 'Produs 2' AND f.CodCl IN
(SELECT CodCl
FROM produse p INNER JOIN liniifact lf ON p.CodPr = lf.CodPr
INNER JOIN facturi f ON lf.Nrfact = f.NrFact
Documentul este oferit gratuit,
trebuie doar să te autentifici in contul tău.
