SQLite Verbinden: Natuurlijk links buiten, binnen, kruis met tafels

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

Proef de 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. Ook kunt u definiëren via welke kolom u de tabellen moet koppelen en onder 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 (INNER JOIN, LEFT OUTER JOIN of CROSS JOIN).
• JOIN-constraint – nadat u de tabellen of subquery's hebt opgegeven waaraan u wilt deelnemen, moet u een join-beperking opgeven. Dit is een voorwaarde waarop de overeenkomende rijen die aan die voorwaarde voldoen, worden geselecteerd, afhankelijk van het join-type.

Houd er rekening mee dat voor alle volgende SQLite JOIN-tabellenvoorbeelden: u moet sqlite3.exe uitvoeren en een verbinding openen met de voorbeeld-database als volgt:

Stap 1) Open in deze stap Deze computer en navigeer naar de volgende map: “C:\sqlite”. 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 INNER JOIN

De INNER JOIN retourneert alleen de rijen die aan de joinvoorwaarde voldoen en verwijdert alle andere rijen die niet aan de joinvoorwaarde voldoen.

Voorbeeld

In het volgende voorbeeld voegen we de twee tabellen "Studenten" en "Afdelingen" samen met behulp van DepartmentId om de afdelingsnaam voor elke student te verkrijgen, zoals hieronder weergegeven:

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

Uitleg van code

De INNER JOIN werkt als volgt:

• In de Select-clausule kunt u de gewenste kolommen selecteren uit de twee tabellen waarnaar wordt verwezen.
• De INNER JOIN-clausule wordt geschreven na de eerste tabel waarnaar wordt verwezen met de 'From'-clausule.
• Vervolgens wordt de join-voorwaarde opgegeven met ON.
• Voor tabellen waarnaar wordt verwezen, kunnen aliassen worden opgegeven.
• Het INNER-woord is optioneel, je kunt gewoon JOIN schrijven.

uitgang

De INNER JOIN selecteert de records uit zowel de studententabel als de afdelingstabel die voldoen aan de voorwaarde "Students.DepartmentId = Departments.DepartmentId". Rijen die niet aan de voorwaarde voldoen, worden genegeerd en niet in het resultaat opgenomen.

Daarom werden er slechts 8 van de 10 studenten met een IT-, wiskunde- of natuurkundeopleiding uit deze query geretourneerd. De studenten "Jena" en "George" werden echter niet meegenomen, omdat hun afdelings-ID een null-waarde heeft, die niet overeenkomt met de kolom 'departmentId' in de tabel 'departments'. Zoals hieronder weergegeven:

SQLite DOE MEE … GEBRUIK

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

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

Het verschil tussen INNER JOIN en JOIN .. USING:

Met "JOIN … USING" schrijf je geen joinvoorwaarde, maar alleen de kolom die de twee tabellen gemeen hebben. In plaats van "table1 INNER JOIN table2 ON table1.cola = table2.cola" schrijf je "table1 JOIN table2 USING(cola)".

Voorbeeld

In het volgende voorbeeld voegen we de twee tabellen "Studenten" en "Afdelingen" samen met behulp van DepartmentId om de afdelingsnaam voor elke student te verkrijgen, zoals hieronder weergegeven:

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

Uitleg

• Anders dan in het vorige voorbeeld schreven we niet "ON Students.DepartmentId = Departments.DepartmentId". We schreven gewoon "USING(DepartmentId)".
• SQLite leidt de join-voorwaarde automatisch af en vergelijkt de DepartmentId van beide tabellen – Studenten en Afdelingen.
• U kunt deze syntaxis gebruiken wanneer de twee kolommen die u vergelijkt dezelfde naam hebben.

uitgang

Dit geeft u exact hetzelfde resultaat als het vorige voorbeeld:

SQLite NATUURLIJKE MOEITE

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

Het verschil tussen INNER JOIN en een NATURAL JOIN:

• Bij een INNER JOIN moet je een joinvoorwaarde specificeren die de inner join gebruikt om de twee tabellen te koppelen. Bij een natural join hoef je geen joinvoorwaarde te schrijven. Je schrijft alleen de namen van de twee tabellen, zonder voorwaarde. De natural join controleert dan automatisch of de waarden van elke kolom in beide tabellen gelijk zijn. De natural join leidt de joinvoorwaarde dus automatisch af.
• In de NATURAL JOIN worden alle kolommen uit beide tabellen met dezelfde naam met elkaar vergeleken. Als we bijvoorbeeld twee tabellen hebben met twee kolomnamen gemeen (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

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

Uitleg

• We hoeven geen joinvoorwaarde met kolomnamen te schrijven (zoals we bij INNER JOIN deden). We hoeven de kolomnaam zelfs helemaal niet te vermelden (zoals bij JOIN USING).
• De natuurlijke join scant beide kolommen uit de twee tabellen. Het zal detecteren dat de voorwaarde moet bestaan ​​uit het vergelijken van DepartmentId uit zowel de twee tabellen Students als Departments.

uitgang

De NATURAL JOIN geeft exact dezelfde uitvoer als de INNER JOIN en JOIN USING voorbeelden, omdat in ons voorbeeld alle drie de query's equivalent zijn. In sommige gevallen kan de uitvoer van een INNER JOIN echter verschillen van die van een NATURAL JOIN. Als er bijvoorbeeld meerdere tabellen met dezelfde namen zijn, zal de NATURAL JOIN alle kolommen met elkaar vergelijken. De INNER JOIN daarentegen vergelijkt alleen de kolommen in de joinvoorwaarde.

SQLite LINKER BUITENSTE JOIN

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

Bij een LEFT OUTER JOIN worden alle waarden van de kolommen die je selecteert uit de linkertabel opgenomen in het resultaat van de query. Dus ongeacht of de waarde aan de joinvoorwaarde voldoet of niet, deze wordt in het resultaat opgenomen.

Als de linkertabel 'n' rijen bevat, zullen de resultaten van de query ook 'n' rijen bevatten. Echter, voor de waarden in de kolommen van de rechtertabel geldt dat elke waarde die niet aan de joinvoorwaarde voldoet, een 'null'-waarde zal 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 “LEFT JOIN” proberen om de twee tabellen “Studenten” en “Afdelingen” samen te voegen:

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

Uitleg

• SQLite 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 tabel die is opgegeven na de natuurlijke LEFT JOIN de rechtertabel is.
• De OUTER-clausule is optioneel; LINKS natuurlijk OUTER JOIN is hetzelfde als LINKS JOIN.

uitgang

Zoals je ziet, zijn alle rijen uit de tabel 'studenten' opgenomen, in totaal 10 studenten. Zelfs als de vierde en laatste student, Jena en George, afdelings-ID's hebben die niet in de tabel 'Afdelingen' voorkomen, zijn ze toch opgenomen.

In deze gevallen zal de waarde van departmentName voor zowel Jena als George "null" zijn, omdat de tabel met afdelingen geen departmentName heeft die overeenkomt met hun departmentId-waarde.

Laten we de voorgaande query met behulp van de left join nader toelichten met behulp van Venn-diagrammen:

De LEFT JOIN geeft alle namen van studenten uit de tabel 'studenten', zelfs als de student een afdelings-ID heeft die niet in de tabel 'afdelingen' voorkomt. De query geeft dus niet alleen de overeenkomende rijen zoals de INNER JOIN, maar ook de niet-overeenkomende rijen uit de linkertabel (de tabel 'studenten').

Houd er rekening mee dat elke studentnaam die geen overeenkomende afdeling heeft, een "null"-waarde voor de afdelingsnaam zal hebben, omdat er geen overeenkomende waarde voor is, en deze waarden zijn de waarden in de niet-overeenkomende rijen.

SQLite KRUIS MEE

Een CROSS JOIN geeft het cartesiaanse 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.

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

In tegenstelling tot INNER JOIN en LEFT OUTER JOIN hoeft u bij CROSS JOIN geen joinvoorwaarde op te geven, omdat SQLite is niet nodig voor de CROSS JOIN.

De SQLite Dit resulteert in een logische resultatenset door alle waarden uit de eerste tabel te combineren met alle waarden uit de tweede tabel.

Stel, u selecteert een kolom uit de eerste tabel (kolom A) en een andere kolom uit de tweede tabel (kolom B). Kolom A bevat twee waarden (1, 2) en kolom B 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), namelijk (1,3), (1,4).
• Op dezelfde manier worden twee rijen gevormd door de tweede waarde van colA, namelijk 2, te combineren met de twee waarden van colB (3,4), namelijk (2,3), (2,4).

Voorbeeld

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

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

Uitleg

• In de SQLite selecteren uit meerdere tabellen, we hebben zojuist twee kolommen "studentnaam" uit de studententabel en de "afdelingsnaam" uit de afdelingentabel geselecteerd.
• Voor de cross join hebben we geen joinvoorwaarde gespecificeerd; de twee tabellen zijn simpelweg gecombineerd met een cross join in het midden.

uitgang

Zoals u kunt zien, is het resultaat 40 rijen; 10 waarden uit de tabel studenten vergeleken met de 4 afdelingen uit de tabel afdelingen. Als volgt:

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

Veelgestelde vragen