Baze3-2020: 03 i 04.februar: kreiranje tabele, popunjavanje, spojevi

********************

CREATE DATABASE TEST3;
USE TEST3;

********************


USE TEST3;
CREATE TABLE odeljenje
(
 OdeljenjeID INT,
 OdeljenjeNaziv VARCHAR(20)
);


CREATE TABLE zaposleni
(
 Prezime VARCHAR(20),
 OdeljenjeID INT
);

INSERT INTO odeljenje VALUES(31, 'Rasprodaja');
INSERT INTO odeljenje VALUES(33, 'Inženjeri');
INSERT INTO odeljenje VALUES(34, 'Sveštenici');
INSERT INTO odeljenje VALUES(35, 'Marketing');

INSERT INTO zaposleni VALUES('Raferti', 31);
INSERT INTO zaposleni VALUES('Džouns', 33);
INSERT INTO zaposleni VALUES('Haizenberg', 33);
INSERT INTO zaposleni VALUES('Robinson', 34);
INSERT INTO zaposleni VALUES('Smit', 34);
INSERT INTO zaposleni VALUES('Viliams', NULL);

****************************

CROSS JOIN - Ukršteno spajanje (ili pridruževanje)

UKRŠTENO spajanje vraća Dekartov proizvod (ili Kartezijev proizvod) redova tabele spajanja. Drugim rečima, proizvešće redove koji kombinuju svaki red prve tabele sa svakim redom druge tabele.

Primer eksplicitnog ukrštenog spajanja:

SELECT * 
FROM zaposleni CROSS JOIN odeljenje;

Primer implicitnog ukrštenog spajanja:

SELECT *
FROM zaposleni, odeljenje;

Unutrašnje spajanje

Unutrašnje spajanje zahteva da svaki podatak iz dve povezane tabele ima podatak sa kojim je povezan. Unutrašnje spajanje kreira novu rezultujuću tabelu kombinovanjem vrednosti kolona dve tabele (A i B) zasnovanim na uslovima spajanja. Upit poredi svaki red iz A sa svakim redom iz B kako bi našao sve parove redova koji zadovoljavaju uslov spajanja. Kada je uslov spajanja zadovoljen odgovarajućim ne-nula vrednostima, vrednosti kolona za svaki odgovarajući par redova A i B se kombinuju u rezultujući red.

Rezultat spajanja može biti definisan kao ishod uzimanja Dekartovog proizvoda (ili ukrštenog spajanja) svih podataka tabele (kombinovanje svakog podatka iz tabele A sa svakim podatkom iz tabele B) i zatim se vraćaju svi podaci koji zadovoljavaju uslov spajanja.

"Eksplicitna notacija spajanja" koristi JOIN ključnu reč, opciono prethodi ključnoj reči INNER, da odredi tabelu spajanja, i ON ključna reč da odredi uslove za spajanje, kao u narednom primeru:

SELECT *
FROM zaposleni
INNER JOIN odeljenje ON zaposleni.OdeljenjeID = odeljenje.OdeljenjeID;

"Implicitna notacija spajanja" jednostavno lista tabele za spajanja, u FROM klauzuli izjave SELECT, koristeći zareze da ih razdvoji i WHERE klauzula za dodatne filtere.

Naredni primer je ekvivalentan prethodnom, ali ovaj put u implicitnoj notaciji:

SELECT *
FROM zaposleni, odeljenje
WHERE zaposleni.OdeljenjeID = odeljenje.OdeljenjeID;

Upiti dati u primerima iznad će pridružiti tabele Zaposleni i Odeljenje koristeći OdeljenjeID kolonu obe tabele. Gde se OdeljenjeID ovih tabela podudara (tj. uslov spajanja je zadovoljen), upit će kombinovati Prezime, Odeljenje i OdeljenjeNaziv kolone iz dve tabele u rezultujući red. Gde se OdeljenjeID ne podudara, rezultujući red neće biti generisan.

Primetimo da se zaposleni "Viliams" i odeljenje "Marketing" ne pojavljuju u rezultatima izvršenja upita. Ni jedno od njih nema podudaranja zapisa u drugoj odgovarajućoj tabeli: "Viliams" nema povezano odeljenje, i nema zaposlenih sa odeljenjem ID 35 ("Marketing"). Zavisno od željenih rezultata, ovo ponašanje može biti suptilna greška, koja se može izbeći zamenom unutrašnjeg spajanja sa spoljašnjim.

Napomena: Programeri bi trebalo posebno da obrate pažnju kada tabele spajanja u kolonama mogu da sadrže NULL vrednosti. NULL neće nikada odgovarati bilo kojoj drugoj vrednosti (ni samoj nuli), osim ako uslov spajanja eksplicitno koristi kombinaciju uslova koji prvo proveravaju da li su polja spajanja NOT NULL pre primene preostalih uslova. Unutrašnje spajanje može jedino biti sigurno iskorišćen u bazama podataka koje sprovode Referencijalni integritet ili gde polja spajanja garantovano nisu NULL.

Dalje se unutrašnja spajanja mogu podeliti na izjednačeno spajanje (EQUIJOIN), kao prirodno spajanjE (NATURAL JOIN), ili kao ukršteno spajanje (CROSS JOIN).

Izjednačeno spajanje ILI spajanje po jednakosti (EQUIJOIN)

Spajanje po jednakosti je specijalan tip spajanja baziranog na komparatoru =, koji koristi samo jednakost poređenja u uslovu spajanja. Primer:

SELECT *
FROM zaposleni JOIN odeljenje
  ON zaposleni.OdeljenjeID = odeljenje.OdeljenjeID;

To možemo pisati i kao ispod,

SELECT *
FROM zaposleni, odeljenje
WHERE zaposleni.OdeljenjeID = odeljenje.OdeljenjeID;

Ako kolone u spajanju po jednakosti imaju isto ime, SQL-92 dopušta opcionu skraćenu notaciju za izražavanje izjednačenog spajanja, putem USING konstrukta:

SELECT *
FROM zaposleni INNER JOIN odeljenje USING (OdeljenjeID);

Bilo koje kolone pomenute u USING listi će se pojaviti samo jednom, sa nekvalifikovanim imenom, a po jednom za svaku tabelu u spajanju. U slučaju iznad, biće samo jedna OdeljenjeID kolona a ne zaposleni.OdeljenjeID ili odeljenje.OdeljenjeID.

( USING klauzula nije podržana od strane MS SQL Server i Sybase. )

Prirodno spajanje (NATURAL JOIN)

Prirodno spajanje je vrsta spajanja gde join uslov nastaje implicitno poređenjem svih kolona u obe tabele koje imaju isti naziv kolone u povezanoj tabeli. Rezultujuća povezana tabela sadrži samo jednu kolonu za svaki par kolona sa istim nazivom. U slučaju da nema kolona sa istim nazivom onda se koristi ukršteno spajanje (CROSS JOIN).

Gornji primer upita za unutrašnje spajanje može se objasniti kao prirodno spajanje na sledeći način:

SELECT *
FROM zaposleni NATURAL JOIN odeljenje;

Kao i kod eksplicitnog korišćenja USING klauzule, samo jedna OdeljenjeID kolona se javlja u pridruženoj tabeli.

PostgreSQL, MySQL i Oracle podržavaju prirodno spajanje; Microsoft T-SQL i IBM DB2 ne podržavaju.

Spoljašnje spajanje (OUTER JOIN)

Spoljašnje spajanje se dalje deli na levo spoljašnje, desno spoljašnje spajanje i potpuno spoljašnje spajanje, što zavisi koji redovi tabele su zadržani (levi, desni, ili oba).

(U slučaju levo i desno odnosi se na dve strane JOIN ključne reči.)

Ne postoji implicitna notacija za spoljašnje pridruižvanje u standardu SQL.

Levo spoljašnje spajanje

Rezultat levog spoljašnjeg spajanja (ili jednostavno levo spajanje) za tabele A i B uvek sadrži sve podatke o “levoj” tabeli (A), čak iako se po uslovu-spajanja ne poklapa sa podacima u “desnoj” tabeli (B). Ovo znači i ako klauzula ON pronađe 0 podataka u B (za zadati podatak iz A), spajanje će svakako vratiti red kao rezultat (za taj podatak) - ali sa NULL u svakoj koloni za B. Levo spoljašnje spajanje vraća sve vrednosti iz unutrašnjeg spajanjaa plus sve vrednosti iz leve tabele koje nisu povezane sa desnom tabelom, uključujući redove sa NULL (prazne) vrednostima.

Na primer to nam omogućava da pronađemo odeljenje zaposlenih, ali i dalje prikazuje zaposlene koji nisu pridruženi odeljenju (za razliku od unutrašnjeg spajanja primer gore, gde nepridruženi nisu bili u rezultatu).

Primer levog spoljašnjeg spajanja Primer levog spoljašnjeg spajanja ( ključna reč OUTER je opciona), sa dodatnim rezultatima reda (u poređenju sa unutrašnjim spajanjem):

SELECT *
FROM zaposleni 
LEFT OUTER JOIN odeljenje ON zaposleni.OdeljenjeID = odeljenje.OdeljenjeID;

Desno spoljašnje spajanje

Desno spoljašnje spajanje (ili desno spajanje) liči na levo spoljašnje spajanje, osim što tabele tretira obrnuto. Svaki red iz "desne" tabele (B) će se pojaviti u pridruženoj tabeli barem jednom. Ukoliko postoje nepovezani redovi iz "leve" tabele (A), NULL će se pojaviti u koloni iz A za one podatke koji nisu povezani sa B.

Desno spoljašnje spajanje vraća sve vrednosti iz desne tabele i povezane vrednosti iz leve tabele (NULL u slučaju da nije povezan pridružuje uslov). Na primer, ovo nam omogućava da nađemo zaposlene i njegovo ili njeno odeljenje, ali i dalje pokazuje odeljenja koje nemaju zaposlene.

Ispod je primer desno spoljašnjeg spajanja (ključna reč OUTER je opciona), sa dodatnim redom rezultata koji je iskošen:

SELECT *
FROM zaposleni RIGHT OUTER JOIN odeljenje
  ON zaposleni.OdeljenjeID = odeljenje.OdeljenjeID;

Desno i levo spoljašnje spajanje su funkcionalno jednaki. Ni jedan nema neku funkciju koju drugi nema, tako da desno i levo spoljašnje spajanje mogu da zamene jedno drugo sve dok je redosled u tabeli obrnut.

Potpuno spoljašnje spajanje

Konceptualno, potpuno spoljašnje spajanje kombinuje efekte od primene oba i levog i desnog spoljašnjeg spajanja. Gde podaci u tabeli potpunog spoljašnjeg spajanja nisu povezani, rezultat će imati NULL vrednost za svaku kolonu u tabeli gde nedostsaje povezani red. Za te podatke koji se poklapaju, rezultata će biti jedna linija (sadrži polja iz obe tabele).

Na primer, ovo nam omogućava da vidimo svakog zaposlenog koji je u odeljenju i svako odeljenje koje ima nekog zaposlenog, ali i da vidimo svakog zaposlenog koji nije deo odeljenja i svako odeljenje koje nema zaposlenog.

Primer totalnog spoljašnjeg spajanja ( ključna reč OUTER je opciona):

SELECT *
FROM zaposleni FULL OUTER JOIN odeljenje
  ON zaposleni.OdeljenjeID = odeljenje.OdeljenjeID;
!** ovo nije podržano u MySQL-u ! **

Samospajanje (SELF JOIN)

Samospajanje pridružuje tabelu sebi.

Primer

Ukoliko imamo dve odvojene tabele za zaposlene i pitanje koje zahteva zaposlenog u prvoj tabeli koji ima istu državu kao i zaposlen u drugoj tabeli, normalno spajanje može da se koristi za nalaženje odgovora. Svakako, sve informacije za zaposlenog se nalaze u jednoj velikoj tabeli.

Razmislite o modifikovanju Zaposleni tabele kao u narednom delu:

ALTER TABLE zaposleni
add column ZaposleniId INT(3);

ALTER TABLE zaposleni  
add column Drzava  VARCHAR(33);  
           
UPDATE zaposleni  
SET ZaposleniId=123, Drzava='Australija'  
WHERE Prezime='Raferti';

UPDATE zaposleni  
SET ZaposleniId=124, Drzava='Australija'  
WHERE Prezime='Džouns';

UPDATE zaposleni
SET ZaposleniId=145, Drzava='Australija' 
WHERE Prezime='Haizenberg';

UPDATE zaposleni
SET ZaposleniId=201, Drzava='Sijedinjene Države' 
WHERE Prezime='Robinson';

UPDATE zaposleni
SET ZaposleniId=305, Drzava='Nemačka' 
WHERE Prezime='Smit';

UPDATE zaposleni
SET ZaposleniId=306, Drzava='Nemačka' 
WHERE Prezime='Viliams';

Primer rešenja upita može biti kao u nastavku:

SELECT F.ZaposleniID, F.Prezime, S.ZaposleniID, S.Prezime, F.Drzava
FROM Zaposleni F INNER JOIN Zaposleni S ON F.Drzava = S.Drzava
WHERE F.ZaposleniID < S.ZaposleniID
ORDER BY F.ZaposleniID, S.ZaposleniID;

Za ovaj primer:

F i S su pseudonimi za prvu i drugu kopiju tabele zaposlenih.
Uslov F.Država = S.Država isključuje uparivanje između zaposlenih iz suprotnih država. Zadato pitanje želi parove zaposlenih iz iste države.
Uslov F.ZaposleniID < S.ZaposleniID isključuje uparivanje gde je ZaposleniID od prvog zaposlenog veći ili jednak ZaposleniID sa drugim zaposlenim. Drugim rečima, efekat ovog uslova je da isključi duplo uparivanje i samouparivanje.

Last modified: Tuesday, 9 February 2021, 11:40 PM