Poredjenje Tabela

August 31, 2010 – Zidar

Poredjenje Tabela

 

Cesto se u praksi javlja zadatak poredjenja tabela. Date su dve tabele, istih struktura, istog broja redova. Takve dve table bice jednake ako su im svi odgovarajuci redovi jednaki. Dva reda su jednaka ako su im vrednsoti u odgovarjucim kolonama jednake.

 

Primer dve jednake tabele:

 

IF Object_ID(‘tempdb..#Original’) IS NOT NULL DROP TABLE #Original

;

CREATE TABLE #Original (ID int, Ime varchar(12), Prezime varchar(12))

;

IF Object_ID(‘tempdb..#Kopija’) IS NOT NULL DROP TABLE #Kopija

;

CREATE TABLE #Kopija (ID int, Ime varchar(12), Prezime varchar(12))

;

INSERT INTO #Original VALUES (1,‘Dusko’,‘Radovic’)

INSERT INTO #Original VALUES (2,‘Simo’,‘Esic’)

INSERT INTO #Original VALUES (3,‘Zuko’,‘Dzumhur’)

INSERT INTO #Original VALUES (4,‘Momo’,‘Kapor’)

INSERT INTO #Original VALUES (5,‘Branko’,‘Copic’)

INSERT INTO #Original VALUES (6,‘Mesa’,‘Selimovic’)

;

INSERT INTO #Kopija VALUES (1,‘Dusko’,‘Radovic’)

INSERT INTO #Kopija VALUES (2,‘Simo’,‘Esic’)

INSERT INTO #Kopija VALUES (3,‘Zuko’,‘Dzumhur’)

INSERT INTO #Kopija VALUES (4,‘Momo’,‘Kapor’)

INSERT INTO #Kopija VALUES (5,‘Branko’,‘Copic’)

INSERT INTO #Kopija VALUES (6,‘Mesa’,‘Selimovic’)

;

Za tabele u primeru znamo da su jednake jer smo ih tako napravili. Kako dokazati da su tabele jednake? Posto smo mi SQL majstori, SQL, pokusacemo da napisemo SQL kverije koji ce dokazati jednakost datih tabela.

 

Prvo sto nam prirodno pada na pamet je nesto ovako:

 

SELECT

*

FROM #Original AS O

JOIN #Kopija AS K ON O.ID = K.ID

WHERE O.Ime <> K.Ime

OR O.prezime <> K.prezime

 

(0 row(s) affected)

 

Sve deluje lepo. Da vidimo da li kveri radi i kad imamo razlike. Napravimo razliku u redu sa ID = 1, Dusko Radvic neka postane Dusko Popovic.

 

 

UPDATE #Kopija SET Prezime = ‘Popovic’ WHERE ID = 1

;

Kada izvrsimo kveri, dobijemo naravno tacan rezultat:

 

         ID Ime          Prezime               ID Ime          Prezime

———– ———— ———— ———– ———— ————

          1 Dusko        Radovic                1 Dusko        Popovic

 

(1 row(s) affected)

 

 

Uticaj NULL vrednsoti

Hajde sada da za ID = 2 stavimo da je Prezime = NULL

 

UPDATE #Kopija SET Prezime = NULL WHERE ID = 2

;

SELECT * FROM #Kopija

;

         ID Ime          Prezime

———– ———— ————

          1 Dusko        Popovic

          2 Simo         NULL

          3 Zuko         Dzumhur

          4 Momo         Kapor

          5 Branko       Copic

          6 Mesa         Selimovic

 

(6 row(s) affected)

 

Sada naravno ocekujemo da kveri vrati dva reda.Medjutim, dobijamo i dalje samo jedan red:

 

         ID Ime          Prezime               ID Ime          Prezime

———– ———— ———— ———– ———— ————

          1 Dusko        Radovic                1 Dusko        Popovic

 

(1 row(s) affected)

 

U cemu je problem? Zasto se ne vidi red sa ID =2 gde jeIme = NULL? Nas kveri pokusava da uporedi NULL vrednost sa necim sto nije NULL. NULL nije jednako nicemu, niti je NULL jednako nekom drugom NULL. Zato kveri ne radi dobro kad imamo NULL vrednosti u nekoj od tabela. Uradimo ovo;

 

UPDATE #Original SET Prezime = ‘Simo’, Ime = NULL WHERE ID = 2

UPDATE #Kopija SET Prezime = NULL, Ime = ‘Esic’ WHERE ID = 2

;

SELECT * FROM #Kopija

;

SELECT * FROM #Original

;

         ID Ime          Prezime

———– ———— ————

          1 Dusko        Popovic

          2 Esic         NULL

          3 Zuko         Dzumhur

          4 Momo         Kapor

          5 Branko       Copic

          6 Mesa         Selimovic

 

(6 row(s) affected)

 

         ID Ime          Prezime

———– ———— ————

          1 Dusko        Radovic

          2 NULL         Simo

          3 Zuko         Dzumhur

          4 Momo         Kapor

          5 Branko       Copic

          6 Mesa         Selimovic

 

Redovi za ID =1 i ID=2 se ocigledno razlikuju. Ipak, kveri vraca samo jedan red koji nije identican u dve date tabele. Da eliminisemo probleme sa NULL vrednostima, mozemo da kveri napisemo ovako:

 

SELECT

*

FROM #Original AS O

JOIN #Kopija AS K ON O.ID = K.ID

WHERE COALESCE(O.Ime,‘Ovde je NULL’) <> COALESCE(K.Ime,‘Ovde je NULL’)

OR COALESCE(O.prezime, ‘Ovde je NULL’) <> COALESCE(K.prezime, ‘Ovde je NULL’)

;

Dobijamo tacan rezultat:

         ID Ime          Prezime               ID Ime          Prezime

———– ———— ———— ———– ———— ————

          1 Dusko        Radovic                1 Dusko        Popovic

          2 NULL         Simo                   2 Esic         NULL

 

(2 row(s) affected)

 

Kveri deluje jednostavno, cak is a COALESCE. Sta ce se desiti kad budemo imali veoma mnogo kolona? WHERE uslov postaje glomazan, a i izlaz nece biti bas lep, bice mnogo kolona. Bilo bi najlepse kad ne bismo morali da pisemo sve kolone….Pokazacemo resenje i za to, nesto kasnije. Sada cemo da ubacimo jos gresaka u tabele, da proverimo kako ce se ponasati kveri.

 

NULL u JOIN koloni

Napravimo tabele tako da ID nema osobine primarnog kljuca.Neka imamo NULL u koloni ID (po kojoj vrsimo JOIN)

 

IF Object_ID(‘tempdb..#Original’) IS NOT NULL DROP TABLE #Original

;

CREATE TABLE #Original (ID int , Ime varchar(12), Prezime varchar(12))

;

IF Object_ID(‘tempdb..#Kopija’) IS NOT NULL DROP TABLE #Kopija

;

CREATE TABLE #Kopija (ID int , Ime varchar(12), Prezime varchar(12))

;

INSERT INTO #Original VALUES (1,‘Dusko’,‘Radovic’)

INSERT INTO #Original VALUES (NULL,‘Simo’,‘Esic’)

INSERT INTO #Original VALUES (3,‘Zuko’,‘Dzumhur’)

INSERT INTO #Original VALUES (4,‘Momo’,‘Kapor’)

INSERT INTO #Original VALUES (5,‘Branko’,‘Copic’)

INSERT INTO #Original VALUES (6,‘Mesa’,‘Selimovic’)

 

;

INSERT INTO #Kopija VALUES (1,‘Dusko’,‘Radovic’)

INSERT INTO #Kopija VALUES (2,‘Simo’,‘Esic’)

INSERT INTO #Kopija VALUES (3,‘Zuko’,‘Dzumhur’)

INSERT INTO #Kopija VALUES (4,‘Momo’,‘Kapor’)

INSERT INTO #Kopija VALUES (5,‘Branko’,‘Copic’)

INSERT INTO #Kopija VALUES (6,‘Mesa’,‘Selimovic’)

;

Sve je isto, osim sto imamo NULL umesto ID = 2 u tabeli #Original. Kveri kojije lepo radio za NULL vrednosti u non-join kolonama bio je ovaj

SELECT

*

FROM #Original AS O

JOIN #Kopija AS K ON O.ID = K.ID

WHERE COALESCE(O.Ime,‘Ovde je NULL’) <> COALESCE(K.Ime,‘Ovde je NULL’)

OR COALESCE(O.prezime, ‘Ovde je NULL’) <> COALESCE(K.prezime, ‘Ovde je NULL’)

;

Ponovo dobijamo netacan rezultat:

 

         ID Ime          Prezime               ID Ime          Prezime

———– ———— ———— ———– ———— ————

 

(0 row(s) affected)

 

Neka umesto NULL imamo neku vrednost, ali razlicitu od druge tabele:

 

UPDATE #Original SET ID = 99 WHERE Ime = ‘Simo’

 

Tabele izgledaju sada ovako:

 

SELECT * FROM #Kopija

;

SELECT * FROM #Original

;

         ID Ime          Prezime

———– ———— ————

          1 Dusko        Radovic

          2 Simo         Esic

          3 Zuko         Dzumhur

          4 Momo         Kapor

          5 Branko       Copic

          6 Mesa         Selimovic

 

(6 row(s) affected)

 

         ID Ime          Prezime

———– ———— ————

          1 Dusko        Radovic

         99 Simo         Esic

          3 Zuko         Dzumhur

          4 Momo         Kapor

          5 Branko       Copic

          6 Mesa         Selimovic

 

Red u kome je ‘Simo’,’Esic’ ocigledno se razlikuje u dve tabele, ID nije isti. Da li ce nas kveri to pokazati? Ponovo, ako izvrsimo

 

SELECT

*

FROM #Original AS O

JOIN #Kopija AS K ON O.ID = K.ID

WHERE COALESCE(O.Ime,‘Ovde je NULL’) <> COALESCE(K.Ime,‘Ovde je NULL’)

OR COALESCE(O.prezime, ‘Ovde je NULL’) <> COALESCE(K.prezime, ‘Ovde je NULL’)

;

ne dobijamo ni jedan red:

 

         ID Ime          Prezime               ID Ime          Prezime

———– ———— ———— ———– ———— ————

 

(0 row(s) affected)

 

Postoji jos mogucnosti da stvari krenu naopako. Neka JOIN kolona nema NULL vrednosti, neka nema razlika, ali neka imamo duplikat.

 

UPDATE #Original SET ID = 5 WHERE Ime = ‘Simo’

 

SELECT * FROM #Kopija

;

SELECT * FROM #Original

;

Sada imamo

         ID Ime          Prezime

———– ———— ————

          1 Dusko        Radovic

          2 Simo         Esic

          3 Zuko         Dzumhur

          4 Momo         Kapor

          5 Branko       Copic

          6 Mesa         Selimovic

 

(6 row(s) affected)

 

         ID Ime          Prezime

———– ———— ————

          1 Dusko        Radovic

          5 Simo         Esic

          3 Zuko         Dzumhur

          4 Momo         Kapor

          5 Branko       Copic

          6 Mesa         Selimovic

 

U donjoj tabeli, i Zuko i Branko imaju ID = 5. Izvrsimo ponovo kveri

 

SELECT

*

FROM #Original AS O

JOIN #Kopija AS K ON O.ID = K.ID

WHERE COALESCE(O.Ime,‘Ovde je NULL’) <> COALESCE(K.Ime,‘Ovde je NULL’)

OR COALESCE(O.prezime, ‘Ovde je NULL’) <> COALESCE(K.prezime, ‘Ovde je NULL’)

;

Ovog puta dobili smo rezulat:

 

         ID Ime          Prezime               ID Ime          Prezime

———– ———— ———— ———– ———— ————

          5 Simo         Esic                   5 Branko       Copic

 

(1 row(s) affected)

 

Nije bas odmah jasno sta u stvari rezultat pokazuje, ali bar vidimo da negde imamo problem, pa cemo vec istrazivati i verovatno nesto zakljuciti. Znaci, nas kveri ne propusta duplikate, barem tako za sada izgleda.

 

Vidimo da naivno napisan kveri moze da vrati tacan ili pogresan rezultat, ili delimicno tacan, zavisno od toga kakvi su podaci. Dobar kveri ne sme da se ponasa tako. Opisane situacije mogu se javiti lako u praksi. Ako su tabele velike i ne mogu se golim okom proveriti, mozemo biti u grdnoj nevolji ako napisemo naivan kveri.

 

Zasto je predlozeni kvari naivan i ne radi? Zato sto smo precutno i nesvsno ucinili pogresnu prtpostavku na pocetku. Ponasali smo se kao da je kolona ID primarni kljuc. Da jeste, ne bismo imali problem sa duplikatom i NULL vrednostima. Medjutim, vrednosti ne moraju da su iste za PK u obe kolone. Pogotovo ako je PK neki autonumber ili identity, neki broj koji sistem sam generise. Ako dokazemo na pocetku da ID u obe tabele ima svojstva primarnog kljuce (nema NULL i nema duplikata), mogli bi da razbijemo nas naivan kveri na tri. Prvo bismo pokazali redove koji se nalaze u jednoj tabeli a nema ih u drugoj, a onda bismo pokazali redove koji se nalaze u obe tabele, ali su razliciti.

 

Podesimo prvo podatke

 

UPDATE #Original SET ID = 5 WHERE Ime = ‘Simo’

 

UPDATE #Original SET ID = 99 WHERE Ime = ‘Simo’

;

UPDATE #Kopija SET ID = 55 WHERE Ime = ‘Branko’

;

UPDATE #Kopija SET Ime = ‘Zzzzuko’ WHERE ID = 3

;

Tabele sada izgledaju ovako:

 

SELECT * FROM #Kopija

;

SELECT * FROM #Original

;

         ID Ime          Prezime

———– ———— ————

          1 Dusko        Radovic

          2 Simo         Esic

          3 Zzzzuko      Dzumhur

          4 Momo         Kapor

         55 Branko       Copic

          6 Mesa         Selimovic

 

(6 row(s) affected)

 

         ID Ime          Prezime

———– ———— ————

          1 Dusko        Radovic

         99 Simo         Esic

          3 Zuko         Dzumhur

          4 Momo         Kapor

          5 Branko       Copic

          6 Mesa         Selimovic

 

(6 row(s) affected)

 

Razlike su ove:

-    u #Kopija ‘Branko’,’Copic’ ima ID = 55

-    u #Original ‘Simo’,’Esic’ ima ID = 99.

-    U #Original umesto ‘Zuko’ pise ‘ZZZuko’ za red sa ID = 3

 

Sve ostalo je isto u obe tabele. Duplikata u ID nema, nema ni NULL vrednosti u ID.

 

Pokazimo prvo deo gde su razlike u ID:

 

— Sta ima u #Original a nema u #Kopija

SELECT

O.*

FROM #Original AS O

LEFT JOIN #Kopija AS K ON O.ID = K.ID

WHERE K.ID IS NULL

;

         ID Ime          Prezime

———– ———— ————

         99 Simo         Esic

          5 Branko       Copic

 

(2 row(s) affected)

 

– Sta ima u #Kopija a nema u #Original

SELECT

K.*

FROM #Original AS O

RIGHT JOIN #Kopija AS K ON O.ID = K.ID

WHERE O.ID IS NULL

;

 

         ID Ime          Prezime

———– ———— ————

          2 Simo         Esic

         55 Branko       Copic

 

(2 row(s) affected)

 

Nista lakse, LEFT JOIN, pa RIGHT JOIN, milina jedna :-)

 

Razlike za one redove gde ID postoji u obe tabele:

 

SELECT

*

FROM #Original AS O

JOIN #Kopija AS K ON O.ID = K.ID

WHERE COALESCE(O.Ime,‘Ovde je NULL’) <> COALESCE(K.Ime,‘Ovde je NULL’)

OR COALESCE(O.prezime, ‘Ovde je NULL’) <> COALESCE(K.prezime, ‘Ovde je NULL’)

;

 

         ID Ime          Prezime               ID Ime          Prezime

———– ———— ———— ———– ———— ————

          3 Zuko         Dzumhur                3 Zzzzuko      Dzumhur

 

(1 row(s) affected)

 

Lepo, ali nije najzgodnije za prikazivanje. Imamo tri kverija, od kojih treci ima duplo vise kolona nego prva dva. Ko ce se tu snaci, sta ne valja i gde?

 

I to sve pod uslovom da je ID ili jedinstven i NOT NULL. U realnom zivotu to ne mozemo da garantujemo uvek. Za potrebe primera smo napravili takve tabele, ID je kao PK, ali to nece biti uvek slucaj.

 

Znaci, nasa strategija sa naivno napisanim kverijem se polako raspada. Treba nam nesto bolje. Sad znamo uslove to ‘nesto bolje’:

-    da resenje nije osetljovo na NULL vrednosti u bilo kojoj

-    da nije osteljivo na promene u bilo kojoj koloni (JOIN ili ne JOIN, svejedno)

-    da resenje nije osetljivo na duplikate

Da resenje „nije osetljivo na neki slucaj“ treba citati „da resenje ne promasuje posmatrane slucajeve“

 

U sledecem nastavku poci cemo drugim putem, pa dokle stignemo.

 

J

  1. 1 Trackback(s)

  2. Sep 1, 2010: » Nastavak price o poredjenju tabela

Post a Comment

Blog "Baze podataka" koristi WordPress | Tekstovi (RSS) | Komentari (RSS)