SQLite Join Tables: interior, natural, stânga exterior, încrucișat (exemple)

SQLite acceptă diferite tipuri de îmbinări SQL, cum ar fi INNER JOIN, LEFT OUTER JOIN și CROSS JOIN. Fiecare tip de JOIN este utilizat pentru o situație diferită, așa cum vom vedea în acest tutorial.

În acest tutorial, veți învăța-

Introducere în clauza SQLITE JOIN
INTERIOR ÎNSCRIEȚI-VĂ
ÎNSCRIEȚI-VĂ ... FOLOSIND
ÎNREGISTRARE NATURALĂ
ÎNREGISTRARE ÎN EXTERIOR
CROSS JOIN

Introducere în clauza SQLITE JOIN

Când lucrați la o bază de date cu mai multe tabele, trebuie adesea să obțineți date din aceste tabele multiple.

Cu clauza JOIN, puteți lega două sau mai multe tabele sau subinterogări, alăturându-le. De asemenea, puteți defini prin ce coloană trebuie să legați tabelele și prin ce condiții.

Orice clauză JOIN trebuie să aibă următoarea sintaxă:

Fiecare clauză de asociere conține:

Un tabel sau o subinterogare care este tabelul din stânga; tabelul sau interogarea dinaintea clauzei de asociere (în stânga acesteia).
Operator JOIN - specificați tipul de join (fie INNER JOIN, LEFT OUTER JOIN sau CROSS JOIN).
JOIN-constraint - după ce ați specificat tabelele sau subinterogările de asociere, trebuie să specificați o constrângere de asociere, care va fi o condiție pe care rândurile potrivite care se potrivesc acelei condiții vor fi selectate în funcție de tipul de asociere.

Rețineți că, pentru toate exemplele următoare, trebuie să rulați sqlite3.exe și să deschideți o conexiune la baza de date eșantion, în curând:

Pasul 1) În acest pas,

Deschideți Computerul meu și navigați la următorul director „ C: \ sqlite ” și
Apoi deschideți „ sqlite3.exe ”:

Pasul 2) Deschideți baza de date „ TutorialsSampleDB.db ” prin următoarea comandă:

Acum sunteți gata să executați orice tip de interogare pe baza de date.

SQLite INNER JOIN

INNER JOIN returnează numai rândurile care corespund condiției de asociere și elimină toate celelalte rânduri care nu se potrivesc condiției de asociere.

Exemplu

În exemplul următor, vom alătura celor două tabele „ Studenți ” și „ Departamente ” cu DepartmentId pentru a obține numele departamentului pentru fiecare student, după cum urmează:

SELECTAȚIStudents.StudentName,Departamente. Numele departamentuluiDE LA StudențiINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Explicația codului:

INNER JOIN funcționează după cum urmează:

În clauza Selectați, puteți selecta orice coloane doriți să selectați din cele două tabele de referință.
Clauza INNER JOIN este scrisă după primul tabel la care se face referire cu clauza „De la”.
Apoi condiția de asociere este specificată cu ON.
Aliasurile pot fi specificate pentru tabelele de referință.
Cuvântul INNER este opțional, puteți scrie doar ÎNREGISTRARE.

Ieșire:

INNER JOIN produce înregistrări de la ambele - studenți și tabelele departamentului care corespund condiției care este „ S tudents.DepartmentId = Departments.DepartmentId ”. Rândurile neegalate vor fi ignorate și nu vor fi incluse în rezultat.
De aceea, doar 8 studenți din 10 studenți au fost returnați de la această interogare cu departamente de IT, matematică și fizică. În timp ce studenții „Jena” și „George” nu au fost incluși, deoarece au un ID de departament nul, care nu se potrivește cu coloana departmentId din tabelul departamentelor. Ca următorul:

SQLite ÎNREGISTRARE ... UTILIZARE

INNER JOIN poate fi scris folosind clauza „USING” pentru a evita redundanța, așa că în loc să scrieți „ON Students.DepartmentId = Departments.DepartmentId”, puteți scrie doar „USING (DepartmentID)”.

Puteți utiliza „ÎNREGISTRARE ... UTILIZARE” ori de câte ori coloanele pe care le veți compara în condiția de unire sunt cu același nume. În astfel de cazuri, nu este nevoie să le repetați folosind condiția on și specificați doar numele coloanelor și SQLite va detecta acest lucru.

Diferența dintre INNER JOIN și JOIN ... FOLOSIND:

Cu „ÎNSCRIEȚI-VĂ

… FOLOSIND „nu scrieți o condiție de asociere, ci doar scrieți coloana de asociere care este comună între cele două tabele îmbinate, în loc să scrieți tabelul1„ INNER JOIN table2 ON table1.cola = table2.cola „o scriem ca„ tabel1 ÎNSCRIEȚI la tabelul2 UTILIZAND (cola) ".

Exemplu

SELECTAȚIStudents.StudentName,Departamente. Numele departamentuluiDE LA StudențiINNER JOIN Departamente UTILIZARE (DepartmentId);

Explicaţie

Spre deosebire de exemplul anterior, nu am scris „ ON Students.DepartmentId = Departments.DepartmentId ”. Tocmai am scris „ UTILIZARE (DepartmentId) ”.
SQLite deduce automat condiția de asociere și compară DepartmentId din ambele tabele - Studenți și Departamente.
Puteți utiliza această sintaxă ori de câte ori cele două coloane pe care le comparați au același nume.

Ieșire

Acest lucru vă va oferi același rezultat exact ca în exemplul anterior:

SQLite NATURAL JOIN

O ÎNREGISTRARE NATURALĂ este similară unei ÎNREGISTRARE ... UTILIZARE, diferența este că testează automat egalitatea între valorile fiecărei coloane care există în ambele tabele.

Diferența dintre INNER JOIN și NATURAL JOIN:

Eu n INNER JOIN, trebuie să specificați o condiție care se alăture interior se alăture utilizări să se alăture celor două tabele. În timp ce în asocierea naturală, nu scrieți o condiție de asociere. Trebuie doar să scrieți numele celor două tabele fără nicio condiție. Apoi îmbinarea naturală va testa automat egalitatea dintre valorile pentru fiecare coloană care există în ambele tabele. Alăturarea naturală deduce automat condiția de alăturare.
În NATURAL JOIN, toate coloanele din ambele tabele cu același nume vor fi potrivite una cu cealaltă. De exemplu, dacă avem două tabele cu două nume de coloane în comun (cele două coloane există cu același nume în cele două tabele), atunci îmbinarea naturală va uni cele două tabele comparând valorile ambelor coloane și nu doar dintr-o singură coloană.

Exemplu

SELECTAȚIStudents.StudentName,Departamente. Numele departamentuluiDE LA StudențiDepartamente naturale ÎNREGISTRARE;

Explicaţie

Nu este nevoie să scriem o condiție de asociere cu nume de coloane (așa cum am făcut-o în INNER JOIN). Nici măcar nu a trebuit să scriem numele coloanei o dată (așa cum am făcut-o în ÎNSCRIEȚI-VĂ LA UTILIZARE).
Îmbinarea naturală va scana ambele coloane din cele două tabele. Acesta va detecta că condiția ar trebui să fie compusă din compararea DepartmentId din ambele tabele Studenți și Departamente.

Ieșire

Natural JOIN vă va oferi aceeași ieșire exactă ca și ieșirea obținută de la INNER JOIN și exemplele JOIN USING. Deoarece în exemplul nostru toate cele trei interogări sunt echivalente. Dar, în unele cazuri, ieșirea va fi diferită de îmbinarea interioară, apoi într-o îmbinare naturală. De exemplu, dacă există mai multe tabele cu aceleași nume, atunci îmbinarea naturală se va potrivi cu toate coloanele una cu cealaltă. Cu toate acestea, îmbinarea interioară se va potrivi doar cu coloanele din condiția de îmbinare (mai multe detalii în secțiunea următoare; diferența dintre îmbinarea interioară și îmbinarea naturală).

SQLite STÂNGA ÎN EXTERIOR

Standardul SQL definește trei tipuri de OUTER JOIN-uri: STÂNGA, DREAPTA și FULL, dar SQLite acceptă doar LEFT OUTER JOIN.

În LEFT OUTER JOIN, toate valorile coloanelor pe care le selectați din tabelul din stânga vor fi incluse în rezultatul interogării, astfel încât, indiferent de valoarea care se potrivește sau nu cu condiția de asociere, acestea vor fi incluse în rezultat.

Deci, dacă tabelul din stânga are „n” rânduri, rezultatele interogării vor avea „n” rânduri. Cu toate acestea, pentru valorile coloanelor care provin din tabelul din dreapta, dacă orice valoare care nu corespunde condiției de asociere, aceasta va conține o valoare „nulă”.

Deci, veți obține un număr de rânduri echivalent cu numărul de rânduri din îmbinarea din stânga. Astfel veți obține rândurile potrivite din ambele tabele (cum ar fi rezultatele INNER JOIN), plus rândurile care nu se potrivesc din tabelul din stânga.

Exemplu

În exemplul următor, vom încerca „STÂNGA ÎNREGISTRARE” pentru a uni cele două tabele „Studenți” și „Departamente”:

SELECTAȚIStudents.StudentName,Departamente. Numele departamentuluiDE LA Studenți - acesta este tabelul din stângaLEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Explicaţie

Sintaxa LEFT JOIN este aceeași cu INNER JOIN; scrieți ÎNCHIDEREA STÂNGA între cele două tabele, iar apoi condiția de îmbinare vine după clauza ON.
Primul tabel după clauza from este tabelul din stânga. În timp ce al doilea tabel specificat după îmbinarea din stânga este tabelul din dreapta.
Clauza OUTER este opțională; LEFT OUTER JOIN este același cu LEFT JOIN.

Ieșire

După cum puteți vedea, sunt incluse toate rândurile din tabelul studenților, care sunt în total 10 studenți. Chiar dacă cel de-al patrulea și ultimul student, Jena și George, departamentele ID-urilor nu există în tabelul Departamente, sunt incluse și ele.
Și în aceste cazuri, valoarea departmentName atât pentru Jena, cât și pentru George va fi „nulă”, deoarece tabelul departamentelor nu are un nume de departament care să corespundă valorii lor departmentId.

Să oferim interogării anterioare folosind unirea din stânga o explicație mai profundă folosind diagrame Van:

ÎNREGISTRAREA STÂNGA va da tuturor studenților nume din tabelul studenților, chiar dacă studentul are un ID de departament care nu există în tabelul de departamente. Deci, interogarea nu vă va oferi doar rândurile potrivite ca INNER JOIN, ci vă va oferi partea suplimentară care are rândurile de neegalat din tabelul din stânga, care este tabelul studenților.

Rețineți că orice nume de student care nu are niciun departament de potrivire va avea o valoare „nulă” pentru numele departamentului, deoarece nu există o valoare de potrivire pentru acesta, iar acele valori sunt valorile din rândurile care nu se potrivesc.

SQLite CROSS JOIN

A CROSS JOIN oferă produsului cartezian pentru coloanele selectate ale celor două tabele unite, prin potrivirea tuturor valorilor din primul tabel cu toate valorile din al doilea tabel.

Deci, pentru fiecare valoare din primul tabel, veți obține potriviri „n” din cel de-al doilea tabel unde n este numărul de rânduri de tabel secund.

Spre deosebire de INNER JOIN și LEFT OUTER JOIN, cu CROSS JOIN, nu este necesar să specificați o condiție de join, deoarece SQLite nu are nevoie de aceasta pentru CROSS JOIN.

SQLite va rezulta în rezultate logice setate prin combinarea tuturor valorilor din primul tabel cu toate valorile din al doilea tabel.

De exemplu, dacă ați selectat o coloană din primul tabel (colA) și o altă coloană din al doilea tabel (colB). ColA conține două valori (1,2), iar colB conține, de asemenea, două valori (3,4).

Apoi, rezultatul CROSS JOIN va fi de patru rânduri:

Două rânduri prin combinarea primei valori din colA care este 1 cu cele două valori ale colB (3,4) care vor fi (1,3), (1,4).
La fel, două rânduri prin combinarea celei de-a doua valori din colA care este 2 cu cele două valori ale colB (3,4) care sunt (2,3), (2,4).

Exemplu

În următoarea interogare vom încerca CROSS JOIN între tabelele Studenți și Departamente:

SELECTAȚIStudents.StudentName,Departamente. Numele departamentuluiDE LA StudențiCROSS JOIN Departamente;

Explicaţie

În clauza de selectare, tocmai am selectat două coloane „studentname” din tabelul studenților și „departmentName” din tabelul departamentelor.
Pentru îmbinarea încrucișată, nu am specificat nicio condiție de asociere doar cele două tabele combinate cu CROSS JOIN în mijlocul acestora.

Ieșire:

După cum puteți vedea, rezultatul este de 40 de rânduri; 10 valori din tabelul studenților s-au potrivit cu cele 4 departamente din tabelul departamentelor. Ca următorul:

Patru valori pentru cele patru departamente din tabelul departamentelor s-au potrivit cu primul student Michel.
Patru valori pentru cele patru departamente din tabelul departamentelor s-au potrivit cu cel de-al doilea student John.
Patru valori pentru cele patru departamente din tabelul departamentelor s-au potrivit cu al treilea student Jack.
… și așa mai departe.

rezumat

Folosind SQLite JOINs, puteți lega unul sau mai multe tabele sau subinterogări împreună pentru a selecta coloane din ambele tabele sau subinterogări.