SQLite-tabeller: indre, naturlige, venstre ytre, kryss (eksempler)

Innholdsfortegnelse:

Anonim

SQLite støtter forskjellige typer SQL Joins, som INNER JOIN, LEFT OUTER JOIN og CROSS JOIN. Hver type JOIN brukes til en annen situasjon, som vi vil se i denne veiledningen.

I denne veiledningen vil du lære-

  • Introduksjon til SQLite JOIN-klausul
  • INNRE MEDLEM
  • BLI MED ... BRUKER
  • NATURLIG MEDLEM
  • VENSTRE YTRE MEDLEM
  • CROSS JOIN

Introduksjon til SQLite JOIN-klausul

Når du jobber med en database med flere tabeller, må du ofte hente data fra disse flere tabellene.

Med JOIN-leddet kan du koble to eller flere tabeller eller underspørsmål ved å bli med dem. Du kan også definere hvilken kolonne du trenger for å koble tabellene og etter hvilke betingelser.

Enhver JOIN-klausul må ha følgende syntaks:

Hver medlemsklausul inneholder:

  • En tabell eller en undersøking som er den venstre tabellen; tabellen eller underforespørselen før tilknytningsparagrafen (til venstre for den).
  • JOIN-operatør - spesifiser sammenføyningstypen (enten INNER JOIN, LEFT OUTER JOIN eller CROSS JOIN).
  • JOIN-begrensning - etter at du har angitt tabellene eller underspørringene som skal sammenføyes, må du spesifisere en sammenføyningsbegrensning, som vil være en betingelse der de matchende radene som samsvarer med den betingelsen blir valgt, avhengig av sammenføyningstypen.

Merk at for alle følgende eksempler må du kjøre sqlite3.exe og åpne en forbindelse til eksempeldatabasen som flytende:

Trinn 1) I dette trinnet,

  1. Åpne Min datamaskin og naviger til følgende katalog " C: \ sqlite " og
  2. Åpne deretter " sqlite3.exe ":

Trinn 2) Åpne databasen " TutorialsSampleDB.db " med følgende kommando:

Nå er du klar til å kjøre alle typer spørsmål i databasen.

SQLite INNER JOIN

INNER JOIN returnerer bare radene som samsvarer med koblingsbetingelsen og eliminerer alle andre radene som ikke samsvarer med koblingsbetingelsen.

Eksempel

I det følgende eksemplet vil vi slutte oss til de to tabellene " Studenter " og " Avdelinger " med DepartmentId for å få avdelingsnavnet for hver student, som følger:

Å VELGEStudents.StudentName,Avdelinger. AvdelingsnavnFRA studenterINNER JOIN Avdelinger PÅ Students.DepartmentId = Departments.DepartmentId;

Forklaring av kode:

INNER JOIN fungerer som følger:

  • I Select-setningen kan du velge hvilke kolonner du vil velge fra de to refererte tabellene.
  • INNER JOIN-leddet er skrevet etter at den første tabellen refereres til med "Fra".
  • Deretter blir tilknytningsbetingelsen spesifisert med ON.
  • Aliaser kan spesifiseres for refererte tabeller.
  • INNER-ordet er valgfritt, du kan bare skrive JOIN.

Produksjon:

  • INNER JOIN produserer postene fra begge - studentene og instituttets tabeller som samsvarer med tilstanden som er " S tudents.DepartmentId = Departments.DepartmentId ". De umatchede radene blir ignorert og ikke inkludert i resultatet.
  • Derfor ble bare 8 studenter fra 10 studenter returnert fra dette spørsmålet med IT, matematikk og fysikk. Mens studentene "Jena" og "George" ikke var inkludert, fordi de har null avdelings-ID, som ikke samsvarer med avdeling Id-kolonnen fra avdelingstabellen. Som følger:

SQLite BLI MED ... BRUKER

INNER JOIN kan skrives ved bruk av "USING" -klausulen for å unngå redundans, så i stedet for å skrive "ON Students.DepartmentId = Departments.DepartmentId", kan du bare skrive "USING (DepartmentID)".

Du kan bruke "JOIN ... USING" når kolonnene du vil sammenligne i sammenkoblingsbetingelsen har samme navn. I slike tilfeller er det ikke nødvendig å gjenta dem ved bruk av betingelsen og bare oppgi kolonnenavn og SQLite vil oppdage det.

Forskjellen mellom INNER JOIN og JOIN ... BRUKER:

Med "BLI MED

... BRUKER du "du skriver ikke en sammenføyningsbetingelse, du skriver bare sammenføyningskolonnen som er felles mellom de to sammenføyde tabellene, i stedet for å skrive tabellen1" INNRE JOIN tabell2 PÅ table1.cola = table2.cola "vi skriver det som" table1 JOIN table2 USING (cola) ".

Eksempel

I det følgende eksemplet vil vi slutte oss til de to tabellene " Studenter " og " Avdelinger " med DepartmentId for å få avdelingsnavnet for hver student, som følger:

Å VELGEStudents.StudentName,Avdelinger. AvdelingsnavnFRA studenterINNER JOIN Avdelinger BRUKER (DepartmentId);

Forklaring

  • I motsetning til forrige eksempel skrev vi ikke " ON Students.DepartmentId = Departments.DepartmentId ". Vi skrev nettopp " USING (DepartmentId) ".
  • SQLite angir sammenkoblingsbetingelsen automatisk og sammenligner DepartmentId fra begge tabellene - studenter og avdelinger.
  • Du kan bruke denne syntaksen når de to kolonnene du sammenligner har samme navn.

Produksjon

  • Dette vil gi deg det samme eksakte resultatet som forrige eksempel:

SQLite NATURAL JOIN

EN NATURLIG JOIN ligner på en JOIN ... BRUKER, forskjellen er at den automatisk tester for likhet mellom verdiene til hver kolonne som finnes i begge tabeller.

Forskjellen mellom INNER JOIN og en NATURLIG JOIN:

  • I INNER JOIN, må du spesifisere en sammenføyningstilstand som den indre sammenføyningen bruker for å bli med i de to tabellene. Mens du i den naturlige sammenføyningen skriver du ikke en tilstand. Du skriver bare de to tabellenes navn uten noen betingelse. Da vil den naturlige sammenføyningen automatisk teste for likhet mellom verdiene for hver kolonne som finnes i begge tabellene. Naturlig sammenføyning avfører sammenføyningstilstanden automatisk.
  • I NATURAL JOIN vil alle kolonnene fra begge tabellene med samme navn bli matchet mot hverandre. For eksempel, hvis vi har to tabeller med to kolonnenavn til felles (de to kolonnene eksisterer med samme navn i de to tabellene), vil den naturlige sammenføyningen bli med i de to tabellene ved å sammenligne verdiene til begge kolonnene og ikke bare fra en kolonne.

Eksempel

Å VELGEStudents.StudentName,Avdelinger. AvdelingsnavnFRA studenterNatural JOIN Avdelinger;

Forklaring

  • Vi trenger ikke å skrive en foreningsbetingelse med kolonnenavn (slik vi gjorde i INNER JOIN). Vi trengte ikke en gang å skrive kolonnenavnet en gang (som vi gjorde i BLI MED BRUK).
  • Den naturlige sammenføyningen skanner begge kolonnene fra de to tabellene. Den vil oppdage at tilstanden skal være sammensatt av å sammenligne DepartmentId fra begge de to tabellene Studenter og institutter.

Produksjon

  • Natural JOIN vil gi deg samme eksakte produksjon som produksjonen vi fikk fra INNER JOIN og JOIN USING-eksemplene. Fordi i vårt eksempel er alle tre spørsmålene likeverdige. Men i noen tilfeller vil produksjonen være forskjellig fra indre sammenføyning og deretter i en naturlig sammenføyning. For eksempel, hvis det er flere tabeller med samme navn, vil den naturlige sammenføyningen matche alle kolonnene mot hverandre. Imidlertid vil den indre sammenføyningen kun matche kolonnene i tilstanden for sammenføyning (mer informasjon om neste avsnitt; forskjellen mellom indre sammenføyning og naturlig sammenføyning).

SQLite VENSTRE YTRE JOIN

SQL-standarden definerer tre typer YTRE JOIN: VENSTRE, HØYRE og FULL, men SQLite støtter bare VENSTRE YTRE JOIN.

I VENSTRE YTRE JOIN vil alle verdiene til kolonnene du velger fra den venstre tabellen bli inkludert i resultatet av spørringen, så uansett verdien samsvarer med tilstanden for sammenføyning eller ikke, vil den bli inkludert i resultatet.

Så hvis den venstre tabellen har 'n' rader, vil resultatene av spørringen ha 'n' rader. Imidlertid, for verdiene til kolonnene som kommer fra høyre tabell, vil en verdi som ikke samsvarer med sammenkoblingsbetingelsen inneholde en "null" -verdi.

Så du får et antall rader som tilsvarer antall rader i venstre sammenføyning. Slik at du får de matchende radene fra begge tabellene (som INNER JOIN-resultatene), pluss de un-matching radene fra venstre tabell.

Eksempel

I det følgende eksemplet vil vi prøve "VENSTRE JOIN" for å bli med i de to tabellene "Studenter" og "Avdelinger":

Å VELGEStudents.StudentName,Avdelinger. AvdelingsnavnFRA studenter - dette er venstre bordVENSTRE JOIN Avdelinger PÅ Students.DepartmentId = Departments.DepartmentId;

Forklaring

  • VENSTRE JOIN syntaksen er den samme som INNER JOIN; du skriver VENSTRE JOIN mellom de to tabellene, og deretter kommer tilstanden til å komme etter ON-leddet.
  • Den første tabellen etter fra-setningen er den venstre tabellen. Mens den andre tabellen spesifisert etter venstre sammenføyning er den høyre tabellen.
  • OUTER-klausulen er valgfri; VENSTRE YTRE JOIN er det samme som LEFT JOIN.

Produksjon

  • Som du ser er alle radene fra studenttabellen inkludert, som er totalt 10 studenter. Selv om den fjerde og den siste studenten, Jena og George avdeling Ids ikke eksisterer i avdelingstabellen, er de også inkludert.
  • Og i disse tilfellene vil avdelingsnavnverdien for både Jena og George være "null" fordi avdelingstabellen ikke har et avdelingsnavn som samsvarer med deres avdelingsId-verdi.

La oss gi den forrige spørringen ved hjelp av venstre bli en dypere forklaring ved hjelp av Van-diagrammer:

VENSTRE JOIN vil gi alle studentene navn fra studenttabellen, selv om studenten har en avdelings-ID som ikke finnes i avdelingstabellen. Så spørringen vil ikke gi deg bare de samsvarende radene som INNER JOIN, men vil gi deg den ekstra delen som har radene som ikke passer sammen fra venstre tabell som er studenttabellen.

Merk at ethvert studentnavn som ikke har noen matchende avdeling, vil ha en "null" -verdi for avdelingsnavnet, fordi det ikke er noen samsvarende verdi for det, og disse verdiene er verdiene i de ikke-samsvarende radene.

SQLite CROSS JOIN

EN CROSS JOIN gir det kartesiske produktet for de valgte kolonnene i de to sammenføyde tabellene, ved å matche alle verdiene fra den første tabellen med alle verdiene fra den andre tabellen.

Så for hver verdi i den første tabellen får du 'n' kamper fra den andre tabellen der n er antallet andre tabellrader.

I motsetning til INNER JOIN og LEFT OUTER JOIN, med CROSS JOIN, trenger du ikke spesifisere en sammenkoblingsbetingelse, fordi SQLite ikke trenger det for CROSS JOIN.

SQLite vil resultere i logiske resultater satt ved å kombinere alle verdiene fra den første tabellen med alle verdiene fra den andre tabellen.

For eksempel hvis du valgte en kolonne fra den første tabellen (colA) og en annen kolonne fra den andre tabellen (colB). ColA inneholder to verdier (1,2) og colB inneholder også to verdier (3,4).

Da blir resultatet av CROSS JOIN fire rader:

  • To rader ved å kombinere den første verdien fra colA som er 1 med de to verdiene til colB (3,4) som vil være (1,3), (1,4).
  • På samme måte to rader ved å kombinere den andre verdien fra colA som er 2 med de to verdiene til colB (3,4) som er (2,3), (2,4).

Eksempel

I det følgende spørsmålet vil vi prøve CROSS JOIN mellom Student- og avdelingstabellene:

Å VELGEStudents.StudentName,Avdelinger. AvdelingsnavnFRA studenterCROSS JOIN Avdelinger;

Forklaring

  • I valgklausulen valgte vi bare to kolonner "studentnavn" fra studenttabellen og "avdelingsnavn" fra avdelingstabellen.
  • For kryssforbindelsen spesifiserte vi ingen sammenføyningsbetingelser, bare de to tabellene kombinert med CROSS JOIN i midten av dem.

Produksjon:

Som du ser er resultatet 40 rader; 10 verdier fra studenttabellen samsvarte med de 4 avdelingene fra avdelingstabellen. Som følger:

  • Fire verdier for de fire avdelingene fra avdelingstabellen samsvarte med den første studenten Michel.
  • Fire verdier for de fire avdelingene fra avdelingstabellen samsvarte med den andre studenten John.
  • Fire verdier for de fire avdelingene fra avdelingstabellen samsvarte med den tredje studenten Jack.

    … og så videre.

Sammendrag

Ved hjelp av SQLite JOINs kan du koble en eller flere tabeller eller underspøringer sammen for å velge kolonner fra begge tabellene eller underspørringene.