SQLite Join Tables: Inner, Natural, Left Outer, Cross (voorbeelden)

Inhoudsopgave:

Anonim

SQLite ondersteunt verschillende typen SQL-joins, zoals INNER JOIN, LEFT OUTER JOIN en CROSS JOIN. Elk type JOIN wordt gebruikt voor een andere situatie, zoals we in deze tutorial zullen zien.

In deze tutorial leer je-

  • Inleiding tot SQLite JOIN-clausule
  • BINNENKOM
  • WORD LID… GEBRUIKEN
  • NATUURLIJKE SAMENWERKING
  • LINKER BUITEN WORD LID
  • CROSS JOIN

Inleiding tot SQLite JOIN-clausule

Wanneer u aan een database met meerdere tabellen werkt, moet u vaak gegevens uit deze meerdere tabellen halen.

Met de JOIN-clausule kunt u twee of meer tabellen of subquery's koppelen door ze samen te voegen. U kunt ook bepalen met welke kolom u de tabellen moet koppelen en met welke voorwaarden.

Elke JOIN-clausule moet de volgende syntaxis hebben:

Elke join-clausule bevat:

  • Een tabel of een subquery die de linkertabel is; de tabel of de subquery vóór de join-clausule (aan de linkerkant ervan).
  • JOIN-operator - specificeer het join-type (ofwel INNER JOIN, LEFT OUTER JOIN of CROSS JOIN).
  • JOIN-beperking - nadat je de tabellen of subquery's hebt gespecificeerd om mee te doen, moet je een join-beperking specificeren, wat een voorwaarde is waarop de overeenkomende rijen die aan die voorwaarde voldoen, worden geselecteerd, afhankelijk van het join-type.

Merk op dat u voor alle volgende voorbeelden de sqlite3.exe moet uitvoeren en een verbinding met de voorbeelddatabase moet openen als stromend:

Stap 1) In deze stap,

  1. Open Deze computer en ga naar de volgende map " C: \ sqlite " en
  2. Open vervolgens " sqlite3.exe ":

Stap 2) Open de database " TutorialsSampleDB.db " met het volgende commando:

Nu bent u klaar om elk type query op de database uit te voeren.

SQLite BINNENKOM

De INNER JOIN retourneert alleen de rijen die overeenkomen met de join-voorwaarde en elimineert alle andere rijen die niet overeenkomen met de join-voorwaarde.

Voorbeeld

In het volgende voorbeeld voegen we de twee tabellen " Studenten " en " Afdelingen " samen met Afdelings-ID om de afdelingsnaam voor elke leerling te krijgen, als volgt:

SELECTEERStudents.StudentName,Afdeling Afdeling NaamVAN StudentenINNER JOIN Afdelingen ON Students.DepartmentId = Departments.DepartmentId;

Toelichting code:

De INNER JOIN werkt als volgt:

  • In de Select-clausule kunt u de kolommen selecteren die u wilt selecteren uit de twee tabellen waarnaar wordt verwezen.
  • De INNER JOIN-clausule wordt geschreven na de eerste tabel waarnaar wordt verwezen met de "From" -component.
  • Vervolgens wordt de samenvoegvoorwaarde gespecificeerd met AAN.
  • Aliassen kunnen worden opgegeven voor tabellen waarnaar wordt verwezen.
  • Het INNER-woord is optioneel, u kunt gewoon JOIN schrijven.

Uitgang:

  • De INNER JOIN produceert de records van zowel de studenten als de tabellen van de afdeling die voldoen aan de voorwaarde " S tudents.DepartmentId = Departments.DepartmentId ". De niet-overeenkomende rijen worden genegeerd en niet opgenomen in het resultaat.
  • Dat is de reden waarom slechts 8 studenten van 10 studenten terugkwamen van deze vraag met IT-, wiskunde- en natuurkunde-afdelingen. Terwijl de studenten "Jena" en "George" niet waren opgenomen, omdat ze een nulafdelings-ID hebben, die niet overeenkomt met de departmentId-kolom uit de afdelings-tabel. Als volgt:

SQLite JOIN ... GEBRUIKEN

De INNER JOIN kan worden geschreven met behulp van de "USING" -clausule om redundantie te voorkomen, dus in plaats van "ON Students.DepartmentId = Departments.DepartmentId" kun je gewoon "USING (DepartmentID)" schrijven.

U kunt "JOIN… USING" gebruiken wanneer de kolommen die u wilt vergelijken in de join-voorwaarde dezelfde naam hebben. In dergelijke gevallen is het niet nodig om ze te herhalen met de on-voorwaarde en alleen de kolomnamen te vermelden en SQLite zal dat detecteren.

Het verschil tussen INNER JOIN en JOIN ... GEBRUIKEN:

Met "JOIN

... MET "je schrijft geen join-voorwaarde, je schrijft gewoon de join-kolom die gemeenschappelijk is tussen de twee samengevoegde tabellen, in plaats van table1 te schrijven" INNER JOIN-tabel2 ON table1.cola = table2.cola "we schrijven het als" table1 JOIN table2 USING (cola) ".

Voorbeeld

In het volgende voorbeeld voegen we de twee tabellen " Studenten " en " Afdelingen " samen met Afdelings-ID om de afdelingsnaam voor elke leerling te krijgen, als volgt:

SELECTEERStudents.StudentName,Afdeling Afdeling NaamVAN StudentenINNER JOIN Afdelingen GEBRUIKEN (DepartmentId);

Uitleg

  • In tegenstelling tot het vorige voorbeeld hebben we niet " ON Students.DepartmentId = Departments.DepartmentId " geschreven. We hebben zojuist " USING (DepartmentId) " geschreven.
  • SQLite leidt automatisch de join-voorwaarde af en vergelijkt de DepartmentId van zowel de tabellen: studenten als afdelingen.
  • U kunt deze syntaxis gebruiken wanneer de twee kolommen die u vergelijkt dezelfde naam hebben.

Uitvoer

  • Dit geeft je exact hetzelfde resultaat als in het vorige voorbeeld:

SQLite NATURAL JOIN

Een NATURAL JOIN is vergelijkbaar met een JOIN ... USING, het verschil is dat het automatisch test op gelijkheid tussen de waarden van elke kolom die in beide tabellen bestaat.

Het verschil tussen INNER JOIN en een NATURAL JOIN:

  • I n INNER JOIN, u aan te geven een join voorwaarde die de inner join gebruiken om de twee tabellen te sluiten. Terwijl je in de natuurlijke join geen join-voorwaarde schrijft. U schrijft gewoon de namen van de twee tabellen zonder enige voorwaarde. Dan zal de natuurlijke join automatisch testen op gelijkheid tussen de waarden voor elke kolom die in beide tabellen bestaat. Met natuurlijke join wordt de join-voorwaarde automatisch afgeleid.
  • In de NATURAL JOIN worden alle kolommen van beide tabellen met dezelfde naam met elkaar vergeleken. Als we bijvoorbeeld twee tabellen hebben met twee gemeenschappelijke kolomnamen (de twee kolommen bestaan ​​met dezelfde naam in de twee tabellen), dan zal de natuurlijke join de twee tabellen samenvoegen door de waarden van beide kolommen te vergelijken en niet alleen van één. kolom.

Voorbeeld

SELECTEERStudents.StudentName,Afdeling Afdeling NaamVAN StudentenNatuurlijke JOIN-afdelingen;

Uitleg

  • We hoeven geen join-voorwaarde met kolomnamen te schrijven (zoals we deden in INNER JOIN). We hoefden de kolomnaam niet één keer te schrijven (zoals we deden in JOIN USING).
  • De natuurlijke join scant beide kolommen van de twee tabellen. Het zal detecteren dat de voorwaarde moet bestaan ​​uit het vergelijken van DepartmentId uit de twee tabellen Studenten en Departementen.

Uitvoer

  • De Natural JOIN geeft je exact dezelfde uitvoer als de uitvoer die we hebben gekregen van de INNER JOIN- en de JOIN USING-voorbeelden. Omdat in ons voorbeeld alle drie de queries gelijkwaardig zijn. Maar in sommige gevallen zal de uitvoer anders zijn dan bij een inner join dan bij een natuurlijke join. Als er bijvoorbeeld meer tabellen met dezelfde naam zijn, worden bij de natuurlijke join alle kolommen met elkaar vergeleken. De inner join komt echter alleen overeen met de kolommen in de join-voorwaarde (meer details over de volgende sectie; het verschil tussen de inner join en natuurlijke join).

SQLite LINKER BUITEN JOIN

De SQL-standaard definieert drie typen OUTER JOINs: LEFT, RIGHT en FULL, maar SQLite ondersteunt alleen de LEFT OUTER JOIN.

In LEFT OUTER JOIN worden alle waarden van de kolommen die u in de linkertabel selecteert, opgenomen in het resultaat van de query, dus ongeacht of de waarde overeenkomt met de join-voorwaarde of niet, wordt deze opgenomen in het resultaat.

Dus als de linkertabel 'n' rijen heeft, zullen de resultaten van de query 'n' rijen hebben. Echter, voor de waarden van de kolommen die uit de rechtertabel komen, zal een waarde die niet overeenkomt met de join-voorwaarde een "null" -waarde bevatten.

U krijgt dus een aantal rijen dat gelijk is aan het aantal rijen in de linker join. Zodat u de overeenkomende rijen uit beide tabellen krijgt (zoals de INNER JOIN-resultaten), plus de niet-overeenkomende rijen uit de linkertabel.

Voorbeeld

In het volgende voorbeeld zullen we de "LINKS JOIN" proberen om aan de twee tafels "Studenten" en "Afdelingen" deel te nemen:

SELECTEERStudents.StudentName,Afdeling Afdeling NaamVAN Studenten - dit is de linkertafelLINKS JOIN Afdelingen ON Students.DepartmentId = Afdelingen.DepartmentId;

Uitleg

  • LEFT JOIN-syntaxis is hetzelfde als INNER JOIN; je schrijft de LEFT JOIN tussen de twee tabellen, en dan komt de join-voorwaarde na de ON-clausule.
  • De eerste tabel na de from-clausule is de linkertabel. Terwijl de tweede tafel gespecificeerd na de linker join de rechter tafel is.
  • De OUTER-clausule is optioneel; LEFT OUTER JOIN is hetzelfde als LEFT JOIN.

Uitvoer

  • Zoals je kunt zien zijn alle rijen van de studententafel inbegrepen, dit zijn in totaal 10 studenten. Zelfs als de vierde en laatste student, Jena en George departementIds niet in de departemententabel voorkomen, zijn ze ook opgenomen.
  • En in deze gevallen is de waarde departmentName voor zowel Jena als George "null" omdat de tabel met departementen geen departmentName heeft die overeenkomt met hun departmentId-waarde.

Laten we de vorige vraag met behulp van de linker join een diepere uitleg geven met behulp van Van-diagrammen:

De LINKER JOIN geeft alle studenten namen uit de studententabel, zelfs als de student een afdelings-ID heeft dat niet bestaat in de afdelings-tabel. De query geeft je dus niet alleen de overeenkomende rijen als de INNER JOIN, maar geeft je het extra deel met de niet-overeenkomende rijen van de linkertabel, de studententabel.

Merk op dat elke studentnaam die geen overeenkomende afdeling heeft, een "nul" -waarde heeft voor de afdelingsnaam, omdat er geen overeenkomende waarde voor is, en die waarden zijn de waarden in de niet-overeenkomende rijen.

SQLite CROSS JOIN

Een CROSS JOIN geeft het Cartesiaans product voor de geselecteerde kolommen van de twee samengevoegde tabellen, door alle waarden uit de eerste tabel te matchen met alle waarden uit de tweede tabel.

Dus voor elke waarde in de eerste tabel krijgt u 'n'-overeenkomsten uit de tweede tabel, waarbij n het aantal tweede tabelrijen is.

In tegenstelling tot INNER JOIN en LEFT OUTER JOIN, hoeft u met CROSS JOIN geen voorwaarde voor een join op te geven, omdat SQLite deze niet nodig heeft voor de CROSS JOIN.

De SQLite resulteert in logische resultaten die zijn ingesteld door alle waarden uit de eerste tabel te combineren met alle waarden uit de tweede tabel.

Als u bijvoorbeeld een kolom uit de eerste tabel (colA) en een andere kolom uit de tweede tabel (colB) hebt geselecteerd. De colA bevat twee waarden (1,2) en de colB bevat ook twee waarden (3,4).

Het resultaat van de CROSS JOIN is dan vier rijen:

  • Twee rijen door de eerste waarde van colA, die 1 is, te combineren met de twee waarden van colB (3,4) die (1,3), (1,4) zullen zijn.
  • Evenzo twee rijen door de tweede waarde van colA, die 2 is, te combineren met de twee waarden van de colB (3,4) die (2,3), (2,4) zijn.

Voorbeeld

In de volgende zoekopdracht zullen we CROSS JOIN proberen tussen de tabellen Studenten en Afdelingen:

SELECTEERStudents.StudentName,Afdeling Afdeling NaamVAN StudentenCROSS JOIN afdelingen;

Uitleg

  • In de select-clausule hebben we zojuist twee kolommen "studentnaam" uit de studententabel en de "departmentName" uit de departemententabel geselecteerd.
  • Voor de cross-join hebben we geen join-voorwaarde gespecificeerd, alleen de twee tabellen gecombineerd met CROSS JOIN in het midden ervan.

Uitgang:

Zoals u kunt zien, is het resultaat 40 rijen; 10 waarden uit de studententabel kwamen overeen met de 4 afdelingen uit de afdelingentabel. Als volgt:

  • Vier waarden voor de vier afdelingen uit de afdelingslijst kwamen overeen met de eerste student Michel.
  • Vier waarden voor de vier afdelingen uit de afdelingentabel kwamen overeen met de tweede student John.
  • Vier waarden voor de vier afdelingen uit de afdelingstabel kwamen overeen met de derde student Jack.

    … enzovoort.

Overzicht

Met SQLite JOINs kunt u een of meer tabellen of subquery's aan elkaar koppelen om kolommen uit beide tabellen of subquery's te selecteren.