SQLite Samenvoegen: Natuurlijk Links Buiten, Binnen, Kruis met Tabellen Voorbeeld

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) In deze stap,

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

Stap 2) Open de databank “ZelfstudiesSampleDB.db" door 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 voldoen aan de samenvoegingsvoorwaarde en elimineert alle andere rijen die niet voldoen aan de samenvoegingsvoorwaarde.

Voorbeeld

In het volgende voorbeeld voegen we de twee tabellen samen:Leerlingen"En"AFDELINGEN” met DepartmentId om de afdelingsnaam voor elke student op te halen, als volgt:

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 produceert de records van zowel de studenten als de afdelingstabellen die overeenkomen met de voorwaarde die luidt:Students.DepartmentId = Afdelingen.DepartmentId “. De niet-overeenkomende rijen worden genegeerd en niet opgenomen in het resultaat.
• Daarom werden slechts 8 studenten van de 10 studenten geretourneerd uit deze query met IT-, wiskunde- en natuurkundeafdelingen. Terwijl de studenten "Jena" en "George" niet werden opgenomen, omdat ze een null department Id hebben, die niet overeenkomt met de departmentId-kolom uit de departments-tabel. Zoals volgt:

  

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 de INNER JOIN en JOIN .. MET BEHULP VAN:

Met “JOIN … USING” schrijf je geen join-voorwaarde, je schrijft alleen de join-kolom die gemeenschappelijk is tussen de twee samengevoegde tabellen, in plaats van tabel1 te schrijven “INNER JOIN table2 ON table1.cola = table2.cola” schrijven we het lijkt op "tabel1 JOIN table2 USING(cola)".

Voorbeeld

In het volgende voorbeeld voegen we de twee tabellen samen:Leerlingen"En"AFDELINGEN” met DepartmentId om de afdelingsnaam voor elke student op te halen, als volgt:

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

Uitleg

• In tegenstelling tot het vorige voorbeeld hebben we niet geschreven “ON Studenten.AfdelingId = Afdelingen.AfdelingId“. Wij schreven zojuist “USING(AfdelingsID)'.
• 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:

• In INNER JOIN moet u een joinvoorwaarde opgeven die de inner join gebruikt om de twee tabellen samen te voegen. Terwijl u bij de natuurlijke join geen join-voorwaarde schrijft. U schrijft gewoon de namen van de twee tabellen zonder enige voorwaarde. Vervolgens test de natuurlijke join automatisch op gelijkheid tussen de waarden voor elke kolom in beide tabellen. Bij natuurlijke join wordt de joinvoorwaarde automatisch afgeleid.
• 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 join-voorwaarde met kolomnamen te schrijven (zoals we deden in INNER JOIN). We hoefden de kolomnaam niet eens één keer op te schrijven (zoals we deden in 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 u exact dezelfde output als de output die we kregen van de INNER JOIN en de JOIN USING voorbeelden. Omdat in ons voorbeeld alle drie de query's equivalent zijn. Maar in sommige gevallen zal de output anders zijn van inner join dan van een natural join. Bijvoorbeeld, als er meer tabellen zijn met dezelfde namen, dan zal de natural join alle kolommen met elkaar matchen. De inner join zal echter alleen matchen met de kolommen in de join conditie (meer details in de volgende sectie; het verschil tussen de inner join en natural join).

SQLite LINKER BUITENSTE JOIN

De SQL-standaard definieert drie soorten OUTER JOIN's: LEFT, RIGHT en FULL but SQLite ondersteunt alleen de natuurlijke 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 vraag, dus ongeacht of de waarde wel of niet overeenkomt met de join-voorwaarde, wordt deze opgenomen in het resultaat.

Dus als de linkertabel 'n' rijen heeft, zullen de resultaten van de zoekopdracht 'n' rijen hebben. Voor de waarden van de kolommen uit de rechtertabel geldt echter dat als een waarde die niet overeenkomt met de join-voorwaarde, deze een “null”-waarde bevat.

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 kunt zien zijn alle rijen uit de studententabel opgenomen, wat in totaal 10 studenten betreft. Zelfs als de vierde en de laatste student, Jena en George, afdelingsIds niet voorkomen in de Afdelingentabel, worden ze ook opgenomen.
• En in deze gevallen zal de waarde DepartmentName voor zowel Jena als George 'null' zijn, omdat de departemententabel geen departementName 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 LEFT JOIN geeft alle studentennamen uit de studententabel, zelfs als de student een afdelings-ID heeft die niet bestaat in de afdelingentabel. De query geeft u dus niet alleen de overeenkomende rijen als de INNER JOIN, maar geeft u het extra deel met de niet-overeenkomende rijen uit de linkertabel, namelijk de studententabel.

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 join-voorwaarde op te geven, omdat SQLite heeft het niet nodig voor de CROSS JOIN.

De SQLite zal resulteren in logische resultaten die worden 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), 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 enkele 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 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 afdelingentabel kwamen overeen met de tweede leerling John.
• Vier waarden voor de Vier afdelingen uit de afdelingentabel kwamen overeen met de derde leerling Jack... enzovoort.

Samenvatting

gebruik SQLite JOIN-query kunt u een of meer tabellen of subquery's aan elkaar koppelen om kolommen uit beide tabellen of subquery's te selecteren.