20/21: Baze podataka IT 3

Video objašnjenje za: INNER JOIN, OUTER JOIN (FULL, LEFT, RIGHT):

Kako se koriste spajanja tabela

Mi u relacionim bazama podataka imamo nekoliko različitih JOIN naredbi, među kojima su INNER JOIN, FULL OUTER JOIN, LEFT ( OUTER ) JOIN, RIGHT ( OUTER ) JOIN, kao i neke dodatne JOIN mogućnosti koje su samo pomenute u videu, ali ih mi nećemo raditi, jer izlaze iz okvira planiranog gradiva.

Napomena: Nisu sve vrste JOIN naredbi podržane u svim verzijama SQL-a.

Pošto u ovom delu školske godine koristimo MySQL i XAMPP, napomena se odnosi na MySQL: u MySQL-u nije podržana naredba FULL OUTER JOIN, ali je objašnjena u video snimku, kao i u knjizi koja je data u linku sa prethodnog časa, na stran i sa oznakom 84 (str.88 u fajlu).

---

U ovoj lekciji će da se rade sledeće vrate spajanja: unutrašnje, spoljašnje i potpuno spajanje.

Za potrebe časa, prvo treba kreirati bazu podataka koju ćemo nazvati BAZA1:

  CREATE DATABASE BAZA1; 
  USE BAZA1;

Zatim se unutar baze kreiraju tabele koje će nam trebati:

CREATE TABLE Korisnici
( 
  user_id INT PRIMARY KEY,
  username VARCHAR(30)   UNIQUE
);

Prvu tabelu ćemo nazvati Korisnici (u video snimku je naziv „user“, ali to ne možemo da koristimo jer je u pitanju ključna reč. Sve ostalo je kao u video snimku). U tabeli imamo user_id koji je primarni ključ, i username koji je jedinstven, pa stoga pored njega treba da stoji UNIQUE.
Nakon toga u tabelu unosimo 3 reda – podatke o 3 različita korisnika:

INSERT INTO Korisnici
 VALUES	(1,'pperic'), 
        (2, 'aancic'),
        (3, 'vvesnic');

Pored toga imamo tabelu Narudzbe. U njoj imamo primarni ključ order_id, strani ključ user_id koji upućuje na tabelu Korisnici, i imamo datum kada je narudžba kreirana.
U ovoj tabeli evidentiramo koje narudžbe su napravili koji korisnici

CREATE TABLE Narudzbe 
(
   order_id INT(5) PRIMARY KEY,
   user_id INT(3),
   created_at Date,
   CONSTRAINT fk_userID FOREIGN KEY (user_id) REFERENCES Korisnici(user_id)
);

INSERT INTO Narudzbe 
 VALUES  (1, 1,'2021-01-28'),
         (2, 3, '2021-01-27'),
         (3, 1, '2021-01-26') ;

Prvu narudžbu je napravio korisnik broj 1, drugu narudžbu je napravio korisnik broj 3, a treću narudžbu je opet napravio korisnik broj 1. Nemamo nikakve narudžbe koje je napravio korisnik broj 2.

Da ponovimo od ranije: ne možete u polje stranog ključa uneti vrednost koja se ne nalazi tamo gde strani ključ pokazuje.
U našem slučaju: ako imamo user_id 1, 2, 3, ne možemo u tabelu Narudzbe za vrednost polja user_id staviti nešto drugo (na primer 4 ili 9) – sistem neće dozvoliti, javiće grešku:

Cannot add or update a child row: a foreign key constraint fails (`baza1`.`narudzbe`, CONSTRAINT `fk_userID` FOREIGN KEY (`user_id`) REFERENCES `korisnici` (`user_id`))

Samim tim, ne možete da u tabelu koja ima strani ključ stavljate podatke sa vrednostima stranog ključa, ako prethodno niste uneli podatke u tabelu na koju strani ključ pokazuje.

---

****** INNER JOIN  ********

( Pogledajte video za objašnjenje INNER JOIN-a, počev od 1:14 do 11:58 )

SELECT korisnici.user_id, korisnici.username, narudzbe.order_id, narudzbe.created_at 
FROM korisnici
INNER JOIN narudzbe ON korisnici.user_id = narudzbe.user_id 
ORDER BY korisnici.user_id;

Uzimamo iz tabele Korisnici: user_id i username, a zatim iz tabele Narudzbe: order_id i created_at.
Sve to uzimamo počevši od tabele Korisnici. Zatim na tabelu Korisnici INNER JOIN tabelu Narudzbe prema relaciji koja kaže da polje user_id iz tabele Narudzbe mora da bude isto kao i user_id u tabeli Korisnici.

( Pomoću INNER JOIN mehanizma povezujemo ove dve tabele).

Na kraju, uradićemo ORDER BY user_id, da bismo ih ispisali u rastućem redosledu.

Napomena: Pošto user_id postoji u obe tabele, neophodno je navesti i iz koje tabele se gleda polje na osnovu kojeg se vrši ORDER BY. S obzirom da je u pitanju INNER JOIN sa uslovom da je vrednost u tom polju ista, isti rezultat bi se dobio i ako bismo upotrebili drugu tabelu Narudzbe.user_id.

Rezultat će imati 4 kolone koje smo tražili u SELECT-u: Iz tabele Korisnici: user_id i username, a iz tabele Narudzbe: order_id i created_at.

Ovaj INNER JOIN mehanizam će krenuti od svega što se nalazi u tabeli Korisnici, upoređujući user_id:

---

Ako bismo pogledali u obliku skupova kako naš INNER JOIN funkcioniše

Prvo imamo skup Korisnici i u tom skupu imamo user_id {1, 2, 3} (ključ po kojem se radi spajanje tabela, nalazi se u delu posle ON). Sa druge strane, imamo skup Narudzbe. Ključ po kojem se vrši poređenje iz ON dela je takođe user_id, i to treba da se nalazi u skupu za Narudžbe user_id{1, 3, 1}

...

INNER JOIN pravi presek ključeva koji se nalaze i u jednoj i u drugoj tabeli (1 i 3 su zajednički) i na kraju će selektovati u konačnom rezultatu samo one zapise koji odgovaraju ovim 1 i 3 user_id-jevima.

...

Zbog toga, u konačnom rezultatu imamo samo zapise za user_id=1 i za user_id=3.

Na ovaj način funkcioniše INNER JOIN.

---

***   FULL OUTER JOIN   ****

U knjizi, koja je data u linku sa prethodnog časa, se objašnjenje nalazi na strani sa oznakom 84 (str.88 u fajlu).

---

***  LEFT JOIN  ***

Pogledajte objašnjenje za LEFT JOIN u video materijalu, počev od 11:59 do 15:37

Za naše tabele, SQL upiti su dati u nastavku:

SELECT korisnici.user_id, korisnici.username, narudzbe.order_id, narudzbe.created_at
FROM korisnici
LEFT JOIN narudzbe ON korisnici.user_id = narudzbe.user_id 
ORDER BY korisnici.user_id;

U knjizi, koja je data u linku sa prethodnog časa, se objašnjenje nalazi na strani sa oznakom 84 (str.88 u fajlu).

---

***  RIGHT JOIN  ***

Pogledajte objašnjenje za LEFT JOIN u video materijalu, počev od 15:38 do 18:09

Za naše tabele, SQL upiti su dati u nastavku:

SELECT korisnici.user_id, korisnici.username, narudzbe.order_id, narudzbe.created_at 
FROM korisnici 
RIGHT JOIN narudzbe ON korisnici.user_id = narudzbe.user_id 
ORDER BY korisnici.user_id;

U knjizi, koja je data u linku sa prethodnog časa, se objašnjenje nalazi na strani sa oznakom 84 (str.88 u fajlu).

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

ZA REGISTROVANE UČENIKE: AKO JE POTREBNO DODATNO OBJAŠNJENJE, POŠALJITE PORUKU ILI PREKO PP NA PLATFORMI, ILI PUTEM VIBER-A

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