SQLite Unire: Natural stânga exterior, interior, cruce cu mese

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

Introducere în SQLite Clauza JOIN

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

Cu clauza JOIN, puteți lega două sau mai multe tabele sau subinterogări prin alăturarea acestora. De asemenea, puteți defini după ce coloană trebuie să legați tabelele și după ce condiții.

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

Fiecare clauză de unire conține:

• Un tabel sau o subinterogare care este tabelul din stânga; tabelul sau subinterogarea dinaintea clauzei de îmbinare (în partea stângă a acesteia).
• Operatorul JOIN – specificați tipul de îmbinare (fie INNER JOIN, LEFT OUTER JOIN, fie CROSS JOIN).
• JOIN-constraint – după ce ați specificat tabelele sau subinterogările de alăturat, trebuie să specificați o constrângere de îmbinare, care va fi o condiție în care rândurile care se potrivesc cu acea condiție vor fi selectate în funcție de tipul de îmbinare.

Rețineți că, pentru toate următoarele SQLite Exemple de tabele JOIN, trebuie să rulați sqlite3.exe și să deschideți o conexiune la baza de date exemplu ca flux:

Pas 1) În acest pas, deschideți My Computer și navigați la următorul director „C:\sqlite”, apoi deschideți „sqlite3.exe”:

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

Acum sunteți gata să rulați orice tip de interogare în baza de date.

SQLite INNER JOIN

INNER JOIN returnează doar rândurile care corespund condiției de joncțiune și elimină toate celelalte rânduri care nu corespund condiției de joncțiune.

Exemplu

În exemplul următor, vom uni cele două tabele „Studenți” și „Departamente” cu ID-ul Departamentului pentru a obține numele departamentului pentru fiecare student, după cum urmează:

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

Explicația codului

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

• În clauza Select, puteți selecta orice coloane pe care doriți să le selectați din cele două tabele la care se face referire.
• Clauza INNER JOIN este scrisă după primul tabel la care se face referire cu clauza „From”.
• Apoi condiția de unire este specificată cu ON.
• Aliasurile pot fi specificate pentru tabelele la care se face referire.
• Cuvântul INTERIOR este opțional, poți doar să scrii JOIN.

producție

INNER JOIN produce înregistrările atât din tabelele studenților, cât și din cele ale departamentului, care îndeplinesc condiția „Students.DepartmentId = Departments.DepartmentId”. Rândurile nepotrivite vor fi ignorate și nu vor fi incluse în rezultat.

De aceea, doar 8 studenți din 10 au fost returnați din această interogare cu departamentele 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 corespunde cu coloana departmentId din tabelul de departamente. După cum urmează:

SQLite ÎNSCRIEȚI-VĂ… FOLOSIND

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 folosi „JOIN .. USING” ori de câte ori coloanele pe care le veți compara în condiția de alăturare au același nume. În astfel de cazuri, nu este nevoie să le repetați folosind condiția on și doar să specificați numele coloanelor și SQLite va detecta asta.

Diferența dintre INNER JOIN și JOIN .. UTILIZÂND:

Cu „JOIN … USING” nu scrieți o condiție de jonare, ci doar scrieți coloana de jonare care este comună între cele două tabele unite, în loc să scriem table1 „INNER JOIN table2 ON table1.cola = table2.cola” o scriem ca „table1 JOIN table2 USING(cola)”.

Exemplu

În exemplul următor, vom uni cele două tabele „Studenți” și „Departamente” cu ID-ul Departamentului pentru a obține numele departamentului pentru fiecare student, după cum urmează:

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

Explicație

• Spre deosebire de exemplul anterior, nu am scris „ON Students.DepartmentId = Departments.DepartmentId”. Am scris doar „USING(DepartmentId)”.
• SQLite deduce automat condiția de alăturare ș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 sunt cu același nume.

producție

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

SQLite UNIUNEA NATURALĂ

UN NATURAL JOIN este similar cu un JOIN...USING, 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:

• În INNER JOIN, trebuie să specificați o condiție de joncțiune pe care joncțiunea internă o folosește pentru a uni cele două tabele. În timp ce în joncțiunea naturală, nu scrieți o condiție de joncțiune. Scrieți doar numele celor două tabele fără nicio condiție. Apoi, joncțiunea naturală va testa automat egalitatea dintre valorile fiecărei coloane existente în ambele tabele. Join-ul natural deduce automat condiția de joncțiune.
• În NATURAL JOIN, toate coloanele din ambele tabele cu același nume vor fi potrivite unele cu altele. 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 din una. coloană.

Exemplu

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

Explicație

• Nu trebuie să scriem o condiție de joncțiune cu nume de coloane (așa cum am făcut în INNER JOIN). Nici măcar nu a fost nevoie să scriem numele coloanei o singură dată (așa cum am făcut în JOIN USING).
• Asocierea 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.

producție

JOIN-UL NATURAL va oferi exact același rezultat ca rezultatul obținut din exemplele INNER JOIN și JOIN USING, deoarece în exemplul nostru toate cele trei interogări sunt echivalente. Dar, în unele cazuri, rezultatul va fi diferit de o joncțiune internă decât de o joncțiune naturală. De exemplu, dacă există mai multe tabele cu aceleași nume, atunci joncțiunea naturală va potrivi toate coloanele una cu cealaltă. Cu toate acestea, joncțiunea internă se va potrivi doar cu coloanele aflate în condiția de joncțiune.

SQLite ÎNTREPRINDERE EXTERIOR STÂNGA

Standardul SQL definește trei tipuri de OUTER JOIN-uri: LEFT, RIGHT și FULL, dar SQLite suportă doar LEGĂTURA EXTERIORĂ STÂNGĂ naturală.

În LEFT OUTER JOIN, toate valorile coloanelor selectate din tabelul din stânga vor fi incluse în rezultatul interogării, deci indiferent dacă valoarea corespunde sau nu condiției de joncțiune, aceasta va fi inclusă î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 provenite din tabelul din dreapta, dacă există o valoare care nu corespunde condiției de joncțiune, 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 unirea din stânga. Astfel, veți obține rândurile care se potrivesc 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 „LEFT JOIN” pentru a uni cele două tabele „Studenti” și „Departamente”:

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

Explicație

• SQLite Sintaxa LEFT JOIN este aceeași cu INNER JOIN; scrieți LEFT JOIN î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ă LEFT JOIN natural este tabelul din dreapta.
• Clauza OUTER este opțională; LEFT natural OUTER JOIN este la fel cu LEFT JOIN.

producție

După cum puteți vedea, sunt incluse toate rândurile din tabelul studenților, adică 10 studenți în total. Chiar dacă al patrulea și ultimul student, Jena și George, au ID-uri de departament care nu există în tabelul Departamente, și aceștia sunt incluși.

Și în aceste cazuri, valoarea departmentName atât pentru Jena, cât și pentru George va fi „null” deoarece tabela departments nu are un departmentName care să corespundă valorii lor departmentId.

Să oferim o explicație mai profundă interogării anterioare care folosește left join-ul folosind diagrame Venn:

JOIN-UL DIN STÂNGĂ va oferi numele tuturor studenților din tabelul studenți, chiar dacă studentul are un ID de departament care nu există în tabelul departamente. Așadar, interogarea nu va oferi doar rândurile corespondente ca JOIN-UL INTERN, ci va oferi partea suplimentară care conține rândurile necorespondente din tabelul din stânga, adică tabelul studenți.

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

SQLite CROS JOIN

O CROSS JOIN oferă produsul 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 „n” potriviri din al doilea tabel, unde n este numărul de rânduri ale celui de-al doilea tabel.

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

SQLite va avea ca rezultat un set logic de rezultate 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).
• De asemenea, două rânduri combinând a doua valoare 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:

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

Explicație

• În SQLite selectați din mai multe tabele, am selectat doar două coloane „studentname” din tabelul studenți și „departmentName” din tabelul departamentelor.
• Pentru joncțiunea încrucișată, nu am specificat nicio condiție de joncțiune, ci doar cele două tabele combinate cu CROSS JOIN în mijlocul lor.

producție

După cum puteți vedea, rezultatul este de 40 de rânduri; 10 valori din tabelul studenților comparate cu cele 4 departamente din tabelul departamentelor. După cum urmează:

• Patru valori pentru cele patru departamente din tabelul de departamente potrivite cu primul student Michel.
• Patru valori pentru cele patru departamente din tabelul de departamente s-au potrivit cu al doilea student, John.
• Patru valori pentru cele patru departamente din tabelul de departamente corespunzătoare cu al treilea student, Jack... și așa mai departe.

Întrebări frecvente