Poredjenje Tabela
Tuesday, 31.08.2010 – ZidarPoredjenje 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 Trackback(s)