Tabele za združevanje SQLite: notranja, naravna, leva zunanja, križna (primeri)

Kazalo:

Anonim

SQLite podpira različne vrste pridružitev SQL, kot so INNER JOIN, LEFT OUTER JOIN in CROSS JOIN. Kot bomo videli v tej vadnici, se vsaka vrsta JOIN uporablja za drugačno situacijo.

V tej vadnici boste izvedeli-

  • Uvod v klavzulo o pridružitvi SQLite
  • INNER JOIN
  • PRIDRUŽITE SE… UPORABI
  • NARAVNI PRIDRUŽITEV
  • LEVO ZUNANJE PRIDRUŽITEV
  • PRESEŽI SE

Uvod v klavzulo o pridružitvi SQLite

Ko delate na zbirki podatkov z več tabelami, morate pogosto dobiti podatke iz teh več tabel.

S stavkom JOIN lahko povežete dve ali več tabel ali podpoizvedb tako, da jih združite. Določite lahko tudi, s katerim stolpcem morate povezati tabele in pod katerimi pogoji.

Vsak stavek JOIN mora imeti naslednjo skladnjo:

Vsaka klavzula o združevanju vsebuje:

  • Tabela ali podpoizvedba, ki je leva tabela; tabelo ali podpoizvedbo pred stavkom za združevanje (na levi strani).
  • Operator JOIN - določite vrsto združevanja (bodisi INNER JOIN, LEFT OUTER JOIN ali CROSS JOIN).
  • JOIN-constraint - ko ste določili tabele ali podpoizvedbe, ki jih želite združiti, morate podati omejitev pridružitve, ki bo pogoj, v katerem bodo izbrane ujemajoče se vrstice, ki se ujemajo s tem pogojem, odvisno od vrste združitve.

Za vse naslednje primere morate zagnati sqlite3.exe in odpreti povezavo do vzorčne baze podatkov kot tekočo:

Korak 1) V tem koraku

  1. Odprite Moj računalnik in se pomaknite do naslednjega imenika " C: \ sqlite " in
  2. Nato odprite " sqlite3.exe ":

Korak 2) Z naslednjim ukazom odprite bazo podatkov " TutorialsSampleDB.db ":

Zdaj ste pripravljeni zagnati katero koli vrsto poizvedbe v zbirki podatkov.

SQLite INNER JOIN

INNER JOIN vrne samo vrstice, ki se ujemajo s pogojem združevanja, in odpravi vse druge vrstice, ki se ne ujemajo s pogojem združevanja.

Primer

V naslednjem primeru bomo združili dve tabeli " Študenti " in " Oddelki " z DepartmentId, da bomo dobili ime oddelka za vsakega študenta, kot sledi:

IZBERIŠtudenti.StudentName,Departments.DepartmentNameOD ŠTUDENTOVINNER JOIN Oddelki za študente.DepartmentId = Departments.DepartmentId;

Pojasnilo kode:

INNER JOIN deluje na naslednji način:

  • V klavzuli Izberite lahko med obema referenčnima tabelama izberete vse stolpce, ki jih želite izbrati.
  • Stavek INNER JOIN je zapisan za prvo tabelo, na katero se sklicuje stavek "Od".
  • Potem je pogoj združevanja določen z ON.
  • Za referenčne tabele lahko določite vzdevke.
  • Beseda INNER ni obvezna, lahko samo napišete PRIDRUŽI SE.

Izhod:

  • INNER JOIN ustvari zapise iz obeh - študentov in tabel oddelkov, ki ustrezajo pogoju, ki je " S tudents.DepartmentId = Departments.DepartmentId ". Neprimerljive vrstice bodo prezrte in ne bodo vključene v rezultat.
  • Zato je bilo le 8 študentov od 10 študentov vrnjenih iz te poizvedbe z oddelki za IT, matematiko in fiziko. Medtem ko študenta "Jena" in "George" nista bila vključena, ker imata ničelni ID oddelka, ki se ne ujema s stolpcem departmentId iz tabele oddelkov. Kot sledeče:

SQLite PRIDRUŽITE SE ... UPORABO

INNER JOIN lahko zapišete s klavzulo "USING", da se izognete odvečnosti, zato namesto da pišete "ON Students.DepartmentId = Departments.DepartmentId", lahko samo napišete "USING (DepartmentID)".

"PRIDRUŽI SE ... UPORABI" lahko uporabite kadar koli so stolpci, ki jih boste primerjali v pogoju združevanja, enaki. V takih primerih jih ni treba ponavljati z uporabo pogoja on in navedite samo imena stolpcev in SQLite bo to zaznal.

Razlika med INNER JOIN in JOIN ... UPORABO:

Z »PRIDRUŽITE SE

… Z UPORABO "ne napišete pogoja združevanja, samo napišete stolpec združevanja, ki je skupen med obema združenima tabelama, namesto da bi zapisali table1" INNER JOIN table2 ON table1.cola = table2.cola "to zapišemo kot" tabela1 PRIDRUŽI se tabeli2 UPORABA (kola) ".

Primer

V naslednjem primeru bomo združili dve tabeli " Študenti " in " Oddelki " z DepartmentId, da bomo dobili ime oddelka za vsakega študenta, kot sledi:

IZBERIŠtudenti.StudentName,Departments.DepartmentNameOD ŠTUDENTOVINNER JOIN ODDELKI UPORABA (DepartmentId);

Pojasnilo

  • Za razliko od prejšnjega primera nismo napisali " ON Students.DepartmentId = Departments.DepartmentId ". Pravkar smo napisali " USING (DepartmentId) ".
  • SQLite samodejno ugotovi pogoj združevanja in primerja DepartmentId iz obeh tabel - študentov in oddelkov.
  • To sintakso lahko uporabite, kadar sta stolpca, ki ju primerjate, z istim imenom.

Izhod

  • Tako boste dobili enak natančen rezultat kot prejšnji primer:

SQLITE NATURAL JOIN

NATURAL JOIN je podoben JOIN ... USING, razlika je v tem, da samodejno preizkuša enakost med vrednostmi vsakega stolpca, ki obstaja v obeh tabelah.

Razlika med INNER JOIN in NATURAL JOIN:

  • V INNER JOIN morate določiti pogoj združevanja, ki ga notranje združevanje uporablja za združevanje obeh tabel. Medtem ko pri naravnem združevanju ne napišete pogoja združevanja. Imena tabel preprosto napišete brez pogojev. Nato bo naravno združevanje samodejno preizkusilo enakost med vrednostmi za vsak stolpec v obeh tabelah. Naravni spoj samodejno sklene pogoj združevanja.
  • V NATURAL JOIN se bodo vsi stolpci iz obeh tabel z istim imenom ujemali med seboj. Če imamo na primer dve tabeli z dvema skupnima imenoma stolpcev (dva stolpca obstajata z istim imenom v obeh tabelah), se bo naravno združevanje pridružilo dvema tabelama s primerjavo vrednosti obeh stolpcev in ne samo iz enega stolpec.

Primer

IZBERIŠtudenti.StudentName,Departments.DepartmentNameOD ŠTUDENTOVNatural Pridružite se oddelkom;

Pojasnilo

  • Ni nam treba zapisati pogoja združevanja z imeni stolpcev (kot v primeru INNER JOIN). Imena stolpca nam niti enkrat ni bilo treba napisati (tako kot pri PRIDRUŽITVI UPORABI).
  • Naravna povezava bo skenirala oba stolpca iz obeh tabel. Ugotovil bo, da mora biti pogoj sestavljen iz primerjave oddelka Id iz obeh tabel Študenti in Oddelki.

Izhod

  • Natural JOIN vam bo dal enak izhod kot izhod iz primerov INNER JOIN in JOIN USING. Ker so v našem primeru vse tri poizvedbe enakovredne. Toda v nekaterih primerih se izhod razlikuje od notranjega spajanja kot pri naravnem. Če je na primer več tabel z enakimi imeni, se bo naravno združevanje ujemalo z vsemi stolpci. Vendar se bo notranje združevanje ujemalo samo s stolpci v pogoju združevanja (več podrobnosti v naslednjem razdelku; razlika med notranjim združevanjem in naravnim združevanjem).

SQLite LEVO ZUNANJE PRIDRUŽITEV

Standard SQL opredeljuje tri vrste ZUNANJIH PRIDRUŽITEV: LEVO, DESNO in FULL, vendar SQLite podpira samo LEVO ZUNANJE PRIDRUŽITEV.

V LEFT OUTER JOIN bodo vse vrednosti stolpcev, ki jih izberete iz leve tabele, vključene v rezultat poizvedbe, zato bo ne glede na vrednost, ki se ujema s pogojem združevanja, vključena v rezultat.

Torej, če ima leva tabela 'n' vrstic, bodo rezultati poizvedbe imeli 'n' vrstic. Če pa katera koli vrednost, ki se ne ujema s pogojem združevanja, vsebuje vrednosti stolpcev, ki prihajajo iz desne tabele, bo vsebovala vrednost "null".

Tako boste dobili število vrstic, enakovredno številu vrstic v levem spoju. Tako boste dobili obe ujemajoči se vrstici iz obeh tabel (na primer rezultati INNER JOIN) in neujemajoče se vrstice iz leve tabele.

Primer

V naslednjem primeru bomo poskusili z "LEVO PRIDRUŽITEV" združiti dve tabeli "Študenti" in "Oddelki":

IZBERIŠtudenti.StudentName,Departments.DepartmentNameOD ŠTUDENTOV - to je leva tabelaLEVO PRIDRUŽITE ODDELKOM NA ŠTUDENTI.DepartmentId = Departments.DepartmentId;

Pojasnilo

  • Sintaksa LEFT JOIN je enaka INNER JOIN; med dve tabeli napišete LEFT JOIN, nato pa za klavzulo ON pride pogoj združevanja.
  • Prva tabela za stavkom from je leva tabela. Medtem ko je druga tabela, navedena po levem združevanju, desna tabela.
  • Klavzula OUTER ni obvezna; LEFT OUTER JOIN je enako kot LEFT JOIN.

Izhod

  • Kot lahko vidite, so vključene vse vrstice iz tabele študentov, ki jih je skupaj 10. Tudi če četrti in zadnji študent, Jena in George oddelka Id ne obstajajo v tabeli oddelkov, so vključeni tudi oni.
  • In v teh primerih bo vrednost departmentName za Jena in Georgea "nična", ker tabela oddelkov nima oddelkaName, ki se ujema z njihovo vrednostjo departmentId.

Dajmo prejšnjo poizvedbo z uporabo levega združevanja poglobljeno razlago z Vanovimi diagrami:

LEFT JOIN bo vsem študentom dal imena iz tabele študentov, tudi če ima študent ID oddelka, ki ne obstaja v tabeli oddelkov. Torej, poizvedba vam ne bo dala samo ujemajočih se vrstic kot INNER JOIN, ampak vam bo dala dodatni del, ki ima neujemajoče se vrstice iz leve tabele, ki je tabela študentov.

Upoštevajte, da bo ime vsakega študenta, ki nima ustreznega oddelka, ime "nič" za ime oddelka, ker zanj ni ujemajoče se vrednosti in so te vrednosti vrednosti v neujemajočih se vrsticah.

SQLite CROSS JOIN

CROSS JOIN daje kartezični izdelek za izbrane stolpce dveh združenih tabel tako, da se vse vrednosti iz prve tabele ujemajo z vsemi vrednostmi iz druge tabele.

Torej, za vsako vrednost v prvi tabeli boste dobili 'n' ujemanja iz druge tabele, kjer je n število vrstic druge tabele.

V nasprotju z INNER JOIN in LEFT OUTER JOIN vam pri CROSS JOIN ni treba določiti pogoja združevanja, ker ga SQLite ne potrebuje za CROSS JOIN.

Rezultat SQLite bo logični rezultat, ki bo združen z vsemi vrednostmi iz prve tabele z vsemi vrednostmi iz druge tabele.

Če ste na primer izbrali stolpec iz prve tabele (colA) in drug stolpec iz druge tabele (colB). ColA vsebuje dve vrednosti (1,2) in colB vsebuje tudi dve vrednosti (3,4).

Potem bo rezultat PRESKRBLJENJA v štiri vrstice:

  • Dve vrstici s kombiniranjem prve vrednosti iz colA, ki je 1, z dvema vrednostma colB (3,4), ki bo (1,3), (1,4).
  • Prav tako dve vrstici s kombiniranjem druge vrednosti iz colA, ki je 2, z dvema vrednostma colB (3,4), ki sta (2,3), (2,4).

Primer

V naslednji poizvedbi bomo poskusili CROSS JOIN med tabelami študentov in oddelkov:

IZBERIŠtudenti.StudentName,Departments.DepartmentNameOD ŠTUDENTOVCROSS JOIN Oddelki;

Pojasnilo

  • V izbranem stavku smo pravkar izbrali dva stolpca "ime študenta" iz tabele študentov in "ime oddelka" iz tabele oddelkov.
  • Za navzkrižno združevanje nismo podali nobenega pogoja združevanja, le dve tabeli, kombinirani s CROSS JOIN sredi njih.

Izhod:

Kot lahko vidite, je rezultat 40 vrstic; 10 vrednosti iz tabele študentov se je ujemalo s 4 oddelki iz tabele oddelkov. Kot sledeče:

  • Štiri vrednosti za štiri oddelke iz tabele oddelkov so se ujemale s prvim študentom Michelom.
  • Štiri vrednosti za štiri oddelke iz tabele oddelkov so se ujemale z drugim študentom Janezom.
  • Štiri vrednosti za štiri oddelke iz tabele oddelkov so se ujemale s tretjim študentom Jackom.

    … in tako naprej.

Povzetek

Z uporabo PRIDRUŽITEV SQLite lahko povežete eno ali več tabel ali podpoizvedb, da izberete stolpce iz obeh tabel ali podpoizvedb.