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

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

November 24, 2007 – Zidar

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.