SQLite Spoj: Prirodni lijevi vanjski, unutarnji, križni s tablicama

SQLite podržava različite vrste SQL Spojevi, kao INNER JOIN, LEFT OUTER JOIN i CROSS JOIN. Svaki tip JOIN-a koristi se za različite situacije kao što ćemo vidjeti u ovom vodiču.

Uvod u SQLite JOIN Klauzula

Kada radite na bazi podataka s više tablica, često trebate dobiti podatke iz tih više tablica.

Pomoću klauzule JOIN možete povezati dvije ili više tablica ili podupita njihovim spajanjem. Također, možete definirati po kojem stupcu trebate povezati tablice i po kojim uvjetima.

Svaka JOIN klauzula mora imati sljedeću sintaksu:

Svaka klauzula spajanja sadrži:

• Tablica ili podupit koji je lijeva tablica; tablicu ili podupit prije klauzule spajanja (lijevo od nje).
• Operator JOIN – navedite vrstu spajanja (ili UNUTARNJE SPAJANJE, LIJEVO VANJSKO SPAJANJE ili KRIŽNO SPAJANJE).
• JOIN-ograničenje – nakon što ste naveli tablice ili podupite za spajanje, trebate navesti ograničenje spajanja, što će biti uvjet prema kojem će odgovarajući retci koji odgovaraju tom uvjetu biti odabrani ovisno o vrsti spajanja.

Imajte na umu da za sve sljedeće SQLite Primjeri JOIN tablica, morate pokrenuti sqlite3.exe i otvoriti vezu s oglednom bazom podataka kako teče:

Korak 1) U ovom koraku otvorite Moje računalo i idite do sljedećeg direktorija „C:\sqlite“, a zatim otvorite „sqlite3.exe“:

Korak 2) Otvorite bazu podataka „TutorialsSampleDB.db“ sljedećom naredbom:

Sada ste spremni pokrenuti bilo koju vrstu upita u bazi podataka.

SQLite INNER JOIN

INNER JOIN vraća samo retke koji odgovaraju uvjetu spajanja i eliminira sve ostale retke koji ne odgovaraju uvjetu spajanja.

Primjer

U sljedećem primjeru, spojit ćemo dvije tablice „Studenti“ i „Odjeli“ s DepartmentId kako bismo dobili naziv odjela za svakog studenta, na sljedeći način:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Objašnjenje koda

INNER JOIN radi na sljedeći način:

• U klauzuli Select možete odabrati bilo koje stupce koje želite odabrati iz dvije referentne tablice.
• Klauzula INNER JOIN napisana je nakon prve tablice na koju upućuje klauzula "Od".
• Tada se uvjet spajanja specificira s ON.
• Aliasi se mogu navesti za referentne tablice.
• Riječ INNER nije obavezna, možete samo napisati JOIN.

Izlaz

INNER JOIN generira zapise iz tablica studenata i odjela koji odgovaraju uvjetu "Students.DepartmentId = Departments.DepartmentId". Nepodudarni retci bit će zanemareni i neće biti uključeni u rezultat.

Zato je iz ovog upita vraćeno samo 8 od 10 studenata s odjelima za IT, matematiku i fiziku. Dok studenti „Jena“ i „George“ nisu uključeni jer imaju nulti ID odjela, koji se ne podudara sa stupcem departmentId iz tablice odjela. Kao što slijedi:

SQLite PRIDRUŽITE SE ... KORIŠTENJEM

INNER JOIN može se napisati korištenjem klauzule "USING" kako bi se izbjegla redundantnost, tako da umjesto pisanja "ON Students.DepartmentId = Departments.DepartmentId", možete jednostavno napisati "USING(DepartmentID)".

Možete upotrijebiti "JOIN .. USING" kad god stupci koje ćete usporediti u uvjetu spajanja imaju isti naziv. U takvim slučajevima, nema potrebe ponavljati ih korištenjem uvjeta i samo navesti nazive stupaca i SQLite otkrit će to.

Razlika između INNER JOIN-a i JOIN-a... KORIŠTENJEM:

S "JOIN ... USING" ne pišete uvjet spajanja, već samo pišete stupac spajanja koji je zajednički za dvije spojene tablice. Umjesto da pišete tablica1 "INNER JOIN tablica2 ON tablica1.cola = tablica2.cola", pišemo to kao "tablica1 JOIN tablica2 USING(cola)".

Primjer

U sljedećem primjeru, spojit ćemo dvije tablice „Studenti“ i „Odjeli“ s DepartmentId kako bismo dobili naziv odjela za svakog studenta, na sljedeći način:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments USING(DepartmentId);

Objašnjenje

• Za razliku od prethodnog primjera, nismo napisali „ON Students.DepartmentId = Departments.DepartmentId“. Samo smo napisali „USING(DepartmentId)“.
• SQLite automatski zaključuje uvjet pridruživanja i uspoređuje ID odjela iz obje tablice – Studenti i Odjeli.
• Ovu sintaksu možete koristiti kad god dva stupca koja uspoređujete imaju isti naziv.

Izlaz

Ovo će vam dati isti točan rezultat kao prethodni primjer:

SQLite PRIRODNI SPOJ

NATURAL JOIN sličan je JOIN…USING, razlika je u tome što automatski testira jednakost između vrijednosti svakog stupca koji postoji u objema tablicama.

Razlika između INNER JOIN-a i NATURAL JOIN-a:

• U INNER JOIN-u morate odrediti uvjet spajanja koji unutarnji spoj koristi za spajanje dviju tablica. Dok u prirodnom spajanju ne pišete uvjet spajanja. Samo pišete nazive dviju tablica bez ikakvog uvjeta. Tada će prirodni spoj automatski provjeriti jednakost između vrijednosti za svaki stupac koji postoji u obje tablice. Prirodno spajanje automatski zaključuje uvjet spajanja.
• U NATURAL JOIN-u, svi stupci iz obje tablice s istim imenom bit će međusobno usklađeni. Na primjer, ako imamo dvije tablice s dva zajednička imena stupaca (dva stupca postoje s istim imenom u dvije tablice), tada će prirodni spoj spojiti dvije tablice usporedbom vrijednosti oba stupca, a ne samo iz jednog stupac.

Primjer

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
Natural JOIN Departments;

Objašnjenje

• Ne moramo pisati uvjet spajanja s nazivima stupaca (kao što smo to učinili u INNER JOIN-u). Nismo čak ni trebali jednom napisati naziv stupca (kao što smo to učinili u JOIN USING-u).
• Prirodno spajanje će skenirati oba stupca iz dvije tablice. Otkrit će da bi uvjet trebao biti sastavljen od usporedbe DepartmentId-a iz obje tablice Studenti i Odjeli.

Izlaz

PRIRODNI JOIN će vam dati potpuno isti izlaz kao i izlaz koji smo dobili iz primjera INNER JOIN i JOIN USING, jer su u našem primjeru sva tri upita ekvivalentna. Ali u nekim slučajevima, izlaz će se razlikovati od unutarnjeg spajanja nego od prirodnog spajanja. Na primjer, ako postoji više tablica s istim nazivima, tada će prirodni spoj usporediti sve stupce jedne s drugima. Međutim, unutarnji spoj će usporediti samo stupce u uvjetu spajanja.

SQLite LIJEVI VANJSKI SPOJ

SQL standard definira tri vrste OUTER JOIN-ova: LEFT, RIGHT i FULL, ali SQLite podržava samo prirodni LIJEVI VANJSKI SPOJ.

U LEFT OUTER JOIN-u, sve vrijednosti stupaca koje odaberete iz lijeve tablice bit će uključene u rezultat upita, pa će bez obzira na to odgovara li vrijednost uvjetu spajanja ili ne, biti uključena u rezultat.

Dakle, ako lijeva tablica ima 'n' redaka, rezultati upita imat će 'n' redaka. Međutim, za vrijednosti stupaca koji dolaze iz desne tablice, ako bilo koja vrijednost ne odgovara uvjetu spajanja, sadržavat će vrijednost "null".

Dakle, dobit ćete broj redaka ekvivalentan broju redaka u lijevom spoju. Tako da ćete dobiti odgovarajuće retke iz obje tablice (poput rezultata INNER JOIN), plus retke koji se ne podudaraju iz lijeve tablice.

Primjer

U sljedećem primjeru pokušat ćemo "LEFT JOIN" spojiti dvije tablice "Studenti" i "Odjeli":

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students             -- this is the left table
LEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Objašnjenje

• SQLite LEFT JOIN sintaksa je ista kao INNER JOIN; napišete LEFT JOIN između dvije tablice, a zatim uvjet spajanja dolazi nakon klauzule ON.
• Prva tablica nakon klauzule from je lijeva tablica. Dok je druga tablica navedena nakon prirodnog LIJEVOG JOIN-a desna tablica.
• OUTER klauzula nije obavezna; LEFT natural OUTER JOIN je isto što i LEFT JOIN.

Izlaz

Kao što vidite, uključeni su svi retci iz tablice studenti, što je ukupno 10 studenata. Čak i ako četvrti i posljednji student, Jena i George, imaju ID-ove odjela koji ne postoje u tablici Odjeli, oni su također uključeni.

U tim slučajevima, vrijednost departmentName i za Jenu i za Georgea bit će "null" jer tablica departments nema departmentName koji odgovara njihovoj vrijednosti departmentId.

Dajmo prethodnom upitu korištenjem lijevog spoja detaljnije objašnjenje koristeći Vennove dijagrame:

LEFT JOIN će dati sva imena studenata iz tablice studenata čak i ako student ima ID odjela koji ne postoji u tablici odjela. Dakle, upit neće dati samo odgovarajuće retke kao INNER JOIN, već će vam dati dodatni dio koji ima nepodudarne retke iz lijeve tablice, a to je tablica studenata.

Imajte na umu da će svako ime učenika koje nema odgovarajući odjel imati "null" vrijednost za naziv odjela, jer za njega ne postoji odgovarajuća vrijednost, a te vrijednosti su vrijednosti u recima koji se ne podudaraju.

SQLite CROSS PRIDRUŽITE se

CROSS JOIN daje kartezijanski produkt za odabrane stupce dviju spojenih tablica, spajanjem svih vrijednosti iz prve tablice sa svim vrijednostima iz druge tablice.

Dakle, za svaku vrijednost u prvoj tablici, dobit ćete 'n' podudaranja iz druge tablice gdje je n broj redaka druge tablice.

Za razliku od INNER JOIN-a i LEFT OUTER JOIN-a, kod CROSS JOIN-a ne morate specificirati uvjet spajanja, jer SQLite ne treba ga za CROSS JOIN.

The SQLite rezultirat će logičnim skupom rezultata kombiniranjem svih vrijednosti iz prve tablice sa svim vrijednostima iz druge tablice.

Na primjer, ako ste odabrali stupac iz prve tablice (colA) i drugi stupac iz druge tablice (colB). ColA sadrži dvije vrijednosti (1,2), a colB također sadrži dvije vrijednosti (3,4).

Tada će rezultat CROSS JOIN biti četiri retka:

• Dva retka kombiniranjem prve vrijednosti iz colA koja je 1 s dvije vrijednosti colB (3,4) koje će biti (1,3), (1,4).
• Isto tako, dva retka kombiniranjem druge vrijednosti iz colA koja je 2 s dvije vrijednosti colB (3,4) koje su (2,3), (2,4).

Primjer

U sljedećem upitu pokušat ćemo CROSS JOIN između tablica Students i Departments:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
CROSS JOIN Departments;

Objašnjenje

• u SQLite odaberite iz više tablica, upravo smo odabrali dva stupca "ime studenta" iz tablice studenata i "naziv odjela" iz tablice odjela.
• Za unakrsno spajanje nismo naveli nikakav uvjet spajanja, samo dvije tablice kombinirane s CROSS JOIN-om u sredini između njih.

Izlaz

Kao što vidite, rezultat je 40 redaka; 10 vrijednosti iz tablice učenika podudara se s 4 odjela iz tablice odjela. Kao što slijedi:

• Četiri vrijednosti za četiri odjela iz tablice odjela podudaraju se s prvim učenikom Michelom.
• Četiri vrijednosti za četiri odjela iz tablice odjela podudarale su se s drugim studentom Johnom.
• Četiri vrijednosti za četiri odjela iz tablice odjela podudarale su se s trećim studentom Jackom… i tako dalje.

Pitanja i odgovori