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

NULLS, LEFT JOIN, EXISTS i NOT IN

Friday, 04.07.2008 – Zidar

Kada zelimo da uporedimo dve tabele A i B, i da pokazemo koji su redovi prisutni u A, a nema ih u B (Accessov ‘unmatched query’), to generalno mozemo da uradimo na nekoliko nacina. Mozemo da upotrebimo LEFT JOIN, mozemo da upotrebimo NOT IN ili NOT EXISTS. Da li su svi nacini podjednako dobri i pouzdani? Podjednako dobri znaci podjednako brzi, a pouzdani znaci da li daju tacan rezultat. Zanimljivo pitanje, da li daju tacan rezultat.

Pogledajmo primer. Kreirajmo dve jednostavne tabele i napunimo ih test podacima.

CREATE TABLE A (Broj smallint, ime VARCHAR(5))
GO
INSERT INTO A (Broj, ime) VALUES (1,‘Aca’)
INSERT INTO A (Broj, ime) VALUES (2,‘Maca’)
INSERT INTO A (Broj, ime) VALUES (3,‘Caca’)
INSERT INTO A (Broj, ime) VALUES (5,‘Aca’)
INSERT INTO A (Broj, ime) VALUES (6,‘Aca’)
GO
CREATE TABLE B (Broj smallint, ime VARCHAR(5))
GO
INSERT INTO B (Broj, ime) VALUES (1,‘Aca’)
INSERT INTO B (Broj, ime) VALUES (NULL,‘Maca’)
INSERT INTO B (Broj, ime) VALUES (5,‘Caca’)
GO   

Pokusajmo da upotrebimo NOT IN da pokazemo, koji brojevi su u tabeli A, a nisu u tabeli B:

SELECT A.* 
FROM A
WHERE Broj NOT IN (SELECT Broj FROM B) 

Sledi rezultat:

  Broj  ime
-----  ------
(0 row(s) affected)

Ocigledno je da rezultat nije tacan. Pokusajmo sa NOT EXISTS:

SELECT A.* 
FROM A 
WHERE NOT EXISTS (SELECT Broj FROM B WHERE B.Broj = A.Broj)  

Rezultat je ovaj put tacan:

 Broj  ime
----- -----    
     2  Maca      
     3  Caca      
     6  Aca 

(3 row(s) affected)

Da probamo i LEFT JOIN:

SELECT A.* FROM A
LEFT JOIN B ON A.Broj = B.Broj
WHERE B.Broj IS NULL 

Dobijamo ponovo tacan rezultat:

 Broj  ime
----- -----
     2  Maca      
     3  Caca      
     6  Aca 

(3 row(s) affected)

Zasto je ovo ovako? Primetite da u tabeli B imamo na jednom mestu NULL. E, taj jedan NULL cini da ono sto imamo u IN (SELECT…) vraca NULL. Onda <nesto> IN NULL vraca NULL.

Sta bi bilo kad bi i prva tabela A imala neki NULL u koloni Broj?

UPDATE A SET Broj =  NULL WHERE ime = ‘Caca’
 

 

SELECT * FROM A


    Broj  ime
-------  ------
      1   Aca
      2   Maca
NULL  Caca
      5   Aca
      6   Aca

(5 row(s) affected)

‘ajmo sve ponovo:

SELECT A.* FROM A
WHERE Broj NOT IN (SELECT Broj FROM B)

  Broj ime
-----  -------
(0 row(s) affected)

SELECT A.* 
FROM A
WHERE NOT EXISTS (SELECT Broj FROM B WHERE B.Broj = A.Broj)

  Broj ime
—— —–
     2 Maca
  NULL Caca
     6 Aca
(3 row(s) affected)


SELECT A.*
FROM A
LEFT JOIN B ON A.Broj = B.Broj
WHERE B.Broj IS NULL

  Broj ime
—— —–
     2 Maca
  NULL Caca
     6 Aca
(3 row(s) affected) 

Kao sto se moglo ocekivati, NOT IN gresi, dok NOT EXISTS i LEFT JOIN rade.

Na nesrecu, NOT IN se mnogo lakse pise nego NOT EXISTS ili LEFT JOIN. Da stvar bude gora, u ranijim verzijama MS SQL, NOT EXISTS je radio znatno brze nego NOT IN.
E, MS SQL 2005 je ‘ispravio gresku’, te sada NOT IN radi isto tako brzo kao NOT EXISTS ili LEFT JOIN.
I to Microsoft oglasava kao ‘major improvement’, kao lakse se pise kod, zaboravite komplikovane sintakse itd.

Da se razumemo, ako nema NULL, onda sva tri nacina daju tacan rezultat. Znaci, dobar dizajn baze sa izbegavanjem NULL koiko god moze, pomaze da vas neki suptilni bagovi ne udare po glavi.

Ovaj “Write Post” editor me ubija. Dodaje razmake kad mu se prohte, prelama red gde mu padne napamet. Mislio sam da 30 godina od kako su kopjuteri izmisljeni mogu da naprave ljudski editor za web. Izgleda da nema nista od toga. Sve sto nije plain text mozes da zaboravis… Tuga jedna i zalost.

🙂