SQLite Forbind: Naturlig venstre ydre, indre, kryds med tabeller

SQLite understøtter forskellige typer SQL Joins, som INNER JOIN, LEFT OUTER JOIN og CROSS JOIN. Hver type JOIN bruges til en anden situation, som vi vil se i denne tutorial.

Introduktion til SQLite JOIN klausul

Når du arbejder på en database med flere tabeller, skal du ofte hente data fra disse flere tabeller.

Med JOIN-klausulen kan du sammenkæde to eller flere tabeller eller underforespørgsler ved at forbinde dem. Du kan også definere med hvilken kolonne du skal linke tabellerne og efter hvilke betingelser.

Enhver JOIN-klausul skal have følgende syntaks:

Hver joinklausul indeholder:

• En tabel eller en underforespørgsel, som er den venstre tabel; tabellen eller underforespørgslen før join-klausulen (til venstre for den).
• JOIN-operatør – angiv sammenføjningstypen (enten INNER JOIN, LEFT OUTER JOIN eller CROSS JOIN).
• JOIN-begrænsning – efter at du har angivet de tabeller eller underforespørgsler, der skal tilsluttes, skal du angive en join-begrænsning, som vil være en betingelse, hvorpå de matchende rækker, der matcher den betingelse, vil blive valgt afhængigt af join-typen.

Bemærk, at for alt det følgende SQLite JOIN-tabeller eksempler, du skal køre sqlite3.exe og åbne en forbindelse til eksempeldatabasen som strømmende:

Trin 1) I dette trin skal du åbne Denne computer og navigere til følgende mappe “C:\sqlite” og derefter åbne “sqlite3.exe“:

Trin 2) Åbn databasen “TutorialsSampleDB.db” med følgende kommando:

Nu er du klar til at køre enhver form for forespørgsel på databasen.

SQLite INNER JOIN

INNER JOIN returnerer kun de rækker, der matcher join-betingelsen, og eliminerer alle andre rækker, der ikke matcher join-betingelsen.

Eksempel

I det følgende eksempel vil vi sammenkæde de to tabeller "Studerende" og "Afdelinger" med Afdelings-ID for at få afdelingsnavnet for hver studerende, som følger:

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

Forklaring af kode

INNER JOIN fungerer som følgende:

• I Select-udtrykket kan du vælge de kolonner, du vil vælge fra de to refererede tabeller.
• INNER JOIN-klausulen er skrevet efter den første tabel, der refereres til med "Fra"-klausulen.
• Derefter angives sammenføjningsbetingelsen med ON.
• Aliaser kan angives for refererede tabeller.
• Det INDRE ord er valgfrit, du kan bare skrive JOIN.

Produktion

INNER JOIN producerer poster fra både de studerendes og afdelingens tabeller, der matcher betingelsen "Students.DepartmentId = Departments.DepartmentId". De ikke-matchede rækker ignoreres og inkluderes ikke i resultatet.

Derfor blev kun 8 ud af 10 studerende returneret fra denne forespørgsel med IT-, matematik- og fysikafdelinger. Hvorimod eleverne "Jena" og "George" ikke blev inkluderet, fordi de har et null afdelings-ID, som ikke matcher departmentId-kolonnen fra afdelingstabellen. Som følger:

SQLite TILMELD … VED HJÆLP

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

Du kan bruge "JOIN .. USING", når de kolonner, du vil sammenligne i forbindelsestilstanden, har samme navn. I sådanne tilfælde er det ikke nødvendigt at gentage dem ved at bruge på betingelsen og blot angive kolonnenavnene og SQLite vil opdage det.

Forskellen mellem INNER JOIN og JOIN .. BRUG AF:

Med “JOIN … USING” skriver du ikke en join-betingelse, du skriver bare den join-kolonne, som er fælles for de to joinede tabeller. I stedet for at skrive tabel1 “INNER JOIN tabel2 ON tabel1.cola = tabel2.cola” skriver vi det som “tabel1 JOIN tabel2 USING(cola)”.

Eksempel

I det følgende eksempel vil vi sammenkæde de to tabeller "Studerende" og "Afdelinger" med Afdelings-ID for at få afdelingsnavnet for hver studerende, som følger:

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

Forklaring

• I modsætning til det foregående eksempel skrev vi ikke “ON Students.DepartmentId = Departments.DepartmentId“. Vi skrev blot “USING(DepartmentId)“.
• SQLite udleder automatisk join-betingelsen og sammenligner afdelings-id'et fra begge tabeller – studerende og afdelinger.
• Du kan bruge denne syntaks, når de to kolonner, du sammenligner, har samme navn.

Produktion

Dette vil give dig det samme nøjagtige resultat som det foregående eksempel:

SQLite NATURLIG JOIN

EN NATURLIG JOIN svarer til en JOIN...USING, forskellen er, at den automatisk tester for lighed mellem værdierne i hver kolonne, der findes i begge tabeller.

Forskellen mellem INNER JOIN og en NATURAL JOIN:

• I INNER JOIN skal du angive en join-betingelse, som den indre join bruger til at joine de to tabeller. I den naturlige join skriver du derimod ikke en join-betingelse. Du skriver blot navnene på de to tabeller uden nogen betingelse. Så vil den naturlige join automatisk teste for lighed mellem værdierne for hver kolonne, der findes i begge tabeller. Den naturlige join udleder automatisk join-betingelsen.
• I NATURAL JOIN vil alle kolonnerne fra begge tabeller med samme navn blive matchet mod hinanden. For eksempel, hvis vi har to tabeller med to kolonnenavne til fælles (de to kolonner findes med samme navn i de to tabeller), så vil den naturlige sammenføjning forbinde de to tabeller ved at sammenligne værdierne for begge kolonner og ikke kun fra en kolonne.

Eksempel

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

Forklaring

• Vi behøver ikke at skrive en join-betingelse med kolonnenavne (som vi gjorde i INNER JOIN). Vi behøvede ikke engang at skrive kolonnenavnet én gang (som vi gjorde i JOIN USING).
• Den naturlige join vil scanne begge kolonner fra de to tabeller. Den vil detektere, at betingelsen skal være sammensat af sammenligning af afdelings-id fra både de to tabeller Studerende og afdelinger.

Produktion

NATURAL JOIN vil give dig præcis det samme output som det output, vi fik fra INNER JOIN og JOIN USING eksemplerne, fordi alle tre forespørgsler i vores eksempel er ækvivalente. Men i nogle tilfælde vil outputtet være anderledes fra Inner Join end i en Natural Join. Hvis der for eksempel er flere tabeller med de samme navne, vil den Natural Join matche alle kolonnerne mod hinanden. Den Inner Join vil dog kun matche kolonnerne i join-betingelsen.

SQLite VENSTRE YDRE JOIN

SQL-standarden definerer tre typer OUTER JOINs: LEFT, RIGHT og FULL, men SQLite understøtter kun den naturlige LEFT OUTER JOIN.

I LEFT OUTER JOIN vil alle værdierne i de kolonner, du vælger fra den venstre tabel, blive inkluderet i resultatet af forespørgslen, så uanset om værdien matcher join-betingelsen eller ej, vil den blive inkluderet i resultatet.

Så hvis den venstre tabel har 'n' rækker, vil resultaterne af forespørgslen have 'n' rækker. Men for værdierne i kolonnerne, der kommer fra den højre tabel, vil en værdi, der ikke matcher join-betingelsen, indeholde en "null"-værdi.

Så du får et antal rækker svarende til antallet af rækker i venstre join. Så du får de matchende rækker fra begge tabeller (som INNER JOIN-resultaterne), plus de ikke-matchende rækker fra den venstre tabel.

Eksempel

I det følgende eksempel vil vi prøve "LEFT JOIN" for at forbinde de to tabeller "Studenter" og "Afdelinger":

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

Forklaring

• SQLite LEFT JOIN syntaks er den samme som INNER JOIN; du skriver LEFT JOIN mellem de to tabeller, og så kommer joinbetingelsen efter ON-sætningen.
• Den første tabel efter fra-klausulen er den venstre tabel. Hvorimod den anden tabel angivet efter den naturlige LEFT JOIN er den rigtige tabel.
• OUTER-sætningen er valgfri; LEFT natural OUTER JOIN er det samme som LEFT JOIN.

Produktion

Som du kan se, er alle rækkerne fra elevtabellen inkluderet, hvilket er i alt 10 elever. Selv hvis den fjerde og sidste elev, Jena og George, har afdelings-ID'er, der ikke findes i afdelingstabellen, er de også inkluderet.

Og i disse tilfælde vil værdien af ​​departmentName for både Jena og George være "null", fordi tabellen over departments ikke har et departmentName, der matcher deres departmentId-værdi.

Lad os give den foregående forespørgsel, der brugte venstre join, en dybere forklaring ved hjælp af Venn-diagrammer:

LEFT JOIN giver alle eleverne navne fra elevtabellen, selvom eleven har et afdelings-id, der ikke findes i afdelingstabellen. Så forespørgslen vil ikke kun give dig de matchende rækker som INNER JOIN, men vil også give dig den ekstra del, der indeholder de ikke-matchende rækker fra den venstre tabel, som er elevtabellen.

Bemærk, at ethvert elevnavn, der ikke har nogen matchende afdeling, vil have en "nul"-værdi for afdelingsnavn, fordi der ikke er nogen matchende værdi for det, og disse værdier er værdierne i de ikke-matchende rækker.

SQLite KRÆS DETALJER

En CROSS JOIN giver det kartesiske produkt for de valgte kolonner i de to sammenføjede tabeller ved at matche alle værdierne fra den første tabel med alle værdierne fra den anden tabel.

Så for hver værdi i den første tabel vil du få 'n'-matches fra den anden tabel, hvor n er antallet af anden tabelrækker.

I modsætning til INNER JOIN og LEFT OUTER JOIN behøver du med CROSS JOIN ikke at angive en join-betingelse, fordi SQLite behøver det ikke til CROSS JOIN.

SQLite vil resultere i et logisk resultatsæt ved at kombinere alle værdierne fra den første tabel med alle værdierne fra den anden tabel.

Hvis du for eksempel valgte en kolonne fra den første tabel (colA) og en anden kolonne fra den anden tabel (colB). ColA indeholder to værdier (1,2), og colB indeholder også to værdier (3,4).

Så vil resultatet af CROSS JOIN være fire rækker:

• To rækker ved at kombinere den første værdi fra colA, som er 1, med de to værdier af colB (3,4), som vil være (1,3), (1,4).
• Ligeledes to rækker ved at kombinere den anden værdi fra colA, som er 2, med de to værdier af colB (3,4), som er (2,3), (2,4).

Eksempel

I den følgende forespørgsel vil vi prøve CROSS JOIN mellem tabellerne for studerende og afdelinger:

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

Forklaring

• I SQLite vælg fra flere tabeller, vi valgte blot to kolonner "studentnavn" fra elevtabellen og "afdelingsnavn" fra afdelingstabellen.
• For krydsjoiningen angav vi ingen join-betingelse, kun de to tabeller kombineret med CROSS JOIN i midten af ​​dem.

Produktion

Som du kan se, er resultatet 40 rækker; 10 værdier fra elevtabellen matchede mod de 4 afdelinger fra afdelingstabellen. Som følgende:

• Fire værdier for de fire afdelinger fra afdelingstabellen matchede med den første elev Michel.
• Fire værdier for de fire afdelinger fra afdelingstabellen matchede den anden elev John.
• Fire værdier for de fire afdelinger fra afdelingstabellen matchede den tredje elev Jack ... og så videre.

Ofte Stillede Spørgsmål