Baze podataka – MySQL, Oracle, PostgreSQL, MS SQL Server, Access, modelovanje podataka …

Ne volite fiziku i biologiju? A možda SQL?

Kompanija za koju radim prikuplja podatke o učenicima srednjih i osnovnih škola u Ontariju. Podaci koje prikupljamo menjaju se od godine do godine. Da ne bi menjali bazu svaki čas, imamo dve tabele:

CREATE TABLE Osobe

 (Ime varchar(25) NOT NULL PRIMARY KEY)

GO

CREATE TABLE Predmeti

 (Ime varchar(25) NOT NULL

 , Predmet varchar(25) NOT NULL

 PRIMARY KEY (Ime,Predmet)

 )

GO  
U tabelama imamo ovo:



SELECT * FROM Osobe;

Ime
--------
Cica   
Goca   
Laza   
Maca   
Melanija   
Pera   
Verica   
Zika   
  
(8 row(s) affected)  

Za svaku osobu imamo podatke koje skupljamo, u ovom slučaju školske predmete:



SELECT Ime, Predmet

FROM Predmeti;



Ime        Predmet

———- ——-

Cica       Bio

Cica       Fiz

Laza       Bio

Laza       Geo

Laza       Math

Maca       Bio

Maca       Fiz

Maca       Geo

Maca       Math

Melanija   Geo

Pera       Bio

Pera       Fiz

Pera       Geo

Pera       Math

Verica     Bio

Verica     Math

Zika       Bio

Zika       Fiz

Zika       Math

Postavljeno je pitanje Ko NEMA predmet ‘Fiz’?
Klasično rešenje problema ide ovako:



SELECT Ime

FROM Osobe

WHERE Ime NOT IN(SELECT Ime

                   FROM Predmeti

                  WHERE Predmet = ‘Fiz’);


i daje korektno rešenje:


Ime

———

Goca

Laza

Melanija

Verica

(4 row(s) affected)
Neko je predložio da probamo da pokažemo sve osobe na levoj strani, a na desnoj njihove predmete, ali samo ‘Fiz’ i pogledamo gde je Predmet NULL. Probali smo prvo ovako:


SELECT A.Ime, B.Predmet

FROM Osobe AS A

LEFT JOIN Predmeti AS B

 ON A.Ime = B.Ime

WHERE B.Predmet = ‘Fiz’

AND B.predmet IS NULL;

što naravno nije vratilo ni jedan red. Ne može nešto da bude istovremeno ‘Fiz’ i NULL. Šteta, a delovalo je zaista obećavajuće. Onda se neko drugi setio da napiše upit ovako:


SELECT A.Ime, B.Predmet

FROM Osobe AS A

LEFT JOIN Predmeti AS B

ON A.Ime = B.Ime

AND B.Predmet = ‘Fiz’

WHERE B.predmet IS NULL;

Deo WHERE prebacili smo u deo JOIN ON. I dobili tačan rezultat iz cuga. Ništa subquery, ništa IN. Upravo smo otkrili način da zadamo WHERE i na desnoj strani LEFT JOINa.
Isti efekat mogli smo postići bez upotrebe temp tabela ovako:


WITH A AS

(SELECT Ime, Predmet = ‘Fiz’

   FROM Osobe

)

SELECT A.Ime, B.Predmet

FROM A

LEFT JOIN Predmeti AS B

ON A.Ime = B.Ime AND A.Predmet = B.Predmet

WHERE B.Predmet IS NULL

Problem je što ovo podrazumeva da znamo da upotrebimo CTE (common table expressions), pa da se setimo da ‘pomnožimo’ tabelu Osobe konstantom ‘Fiz’. Ostalo je lako. 🙂
Zato smo ostali kod našeg ‘proširenog JOINa’.
Onda smo proširili pitanje. Rekli smo ‘Ko NEMA ni jedan od predmeta (Fiz, Bio), dakle ni jedan od ova dva?


SELECT A.Ime, B.Predmet

FROM Osobe AS A

LEFT JOIN Predmeti AS B

 ON A.Ime = B.Ime

 AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)

WHERE B.predmet IS NULL

Ime       Predmet
--------- -------
Goca      NULL
Melanija  NULL

(2 row(s) affected)

Elegantno, zar ne?
Pitali smo dalje Ko ima bar jedan od ova dva predmeta, (Fiz,Bio)?
Pokušali smo i dalje istim putem i za malo promašili:


SELECT A.Ime, B.Predmet

FROM Osobe AS A

LEFT JOIN Predmeti AS B

 ON A.Ime = B.Ime

 AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)

WHERE B.predmet IS NOT NULL;

To nam je dalo zaista sve osobe koje imaju bar jedan od dva predmeta, ali su osobe koje imaju oba predmeta prikazane dva puta u izlaznom skupu:
Ime       Predmet
--------- -------
Cica      Bio
Cica      Fiz
Laza      Bio
Maca      Bio
Maca      Fiz
Pera      Bio
Pera      Fiz
Verica    Bio
Zika      Bio
Zika      Fiz

(10 row(s) affected)

Resili smo se duplikata na jednostavan način (minimum rada), ovako:



SELECT DISTINCT A.Ime —-, B.Predmet

FROM Osobe AS A

LEFT JOIN Predmeti AS B

 ON A.Ime = B.Ime

 AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)

WHERE B.predmet IS NOT NULL;

Ubaciš DISTINCT, komentuješ Predmet i dobije se:
Ime
---------
Cica
Laza
Maca
Pera
Verica
Zika
   
(6 row(s) affected)

Postoji bolji način za eliminisanje duplikata. Zahteva malčice više rada u ovom momentu, ali donosi značajnu korist u sledećem koraku. Elem, ovako:


SELECT A.Ime , KolikoPredmeta = COUNT(*)

FROM Osobe AS A

LEFT JOIN Predmeti AS B

 ON A.Ime = B.Ime

 AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)

WHERE B.predmet IS NOT NULL

GROUP BY A.Ime;

Ime    BrojPredmeta
------ -------------
Cica               2
Laza               1
Maca               2
Pera               2
Verica             1
Zika               2

(6 row(s) affected)

Dobili smo i broj predmeta, ko ima koliko od traženih dva predmeta. To može lepo da se iskoristi, ako se postavi pitanje Ko ima oba tražena predmeta?
Pa svi oni iz poslednjeg kverija koji imaju broj predmeta jednak 2. Ovako:


SELECT A.Ime , KolikoPredmeta = COUNT(*)

FROM Osobe AS A

LEFT JOIN Predmeti AS B

 ON A.Ime = B.Ime

 AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)

WHERE B.predmet IS NOT NULL

GROUP BY A.Ime

HAVING COUNT(*)=2;

Ime   KolikoPredmeta
----- -------------- 
Cica               2
Maca               2
Pera               2
Zika               2

(4 row(s) affected)

Dopao mi se ovakav pristup, jer kad jednom u glavi svarite proširivanje JOIN filtera delom WHERE uslova, mnoga nezgodna pitanja postaju jednostavna. Međutim, najveća dobit jeste mogućnost da radite LEFT JOIN i da na desnoj strani ne dobijete baš sve redove, nego samo one koji vam trebaju. Ne sve predmete za svaku osobu, nego tačno onaj predmet koji vas u datom trenutku interesuje. Inače vam ne ginu temp tabele ili upotreba WITH.
Ako ovo nije bilo suviše suvoparno i zamorno, za koji dan pozabavićemo se upotrebom WITH, sa i bez rekurzije.