SQLite Verbindung: Natürlich links außen, innen, Kreuz mit Tabellenbeispiel

SQLite unterstützt verschiedene Arten von SQL Verknüpfungen wie INNER JOIN, LEFT OUTER JOIN und CROSS JOIN. Jeder JOIN-Typ wird für eine andere Situation verwendet, wie wir in diesem Tutorial sehen werden.

Einführung in die SQLite JOIN-Klausel

Wenn Sie an einer Datenbank mit mehreren Tabellen arbeiten, müssen Sie häufig Daten aus diesen mehreren Tabellen abrufen.

Mit der JOIN-Klausel können Sie zwei oder mehr Tabellen oder Unterabfragen verknüpfen, indem Sie sie verbinden. Außerdem können Sie festlegen, nach welcher Spalte und nach welchen Bedingungen Sie die Tabellen verknüpfen möchten.

Jede JOIN-Klausel muss die folgende Syntax haben:

Jede Join-Klausel enthält:

• Eine Tabelle oder eine Unterabfrage, bei der es sich um die linke Tabelle handelt; die Tabelle oder die Unterabfrage vor der Join-Klausel (links davon).
• JOIN-Operator – Geben Sie den Verknüpfungstyp an (entweder INNER JOIN, LEFT OUTER JOIN oder CROSS JOIN).
• JOIN-Einschränkung – nachdem Sie die zu verknüpfenden Tabellen oder Unterabfragen angegeben haben, müssen Sie eine Join-Einschränkung angeben, die eine Bedingung darstellt, unter der je nach Join-Typ die übereinstimmenden Zeilen ausgewählt werden, die dieser Bedingung entsprechen.

Beachten Sie, dass für alle folgenden SQLite Um Beispiele für JOIN-Tabellen zu erhalten, müssen Sie sqlite3.exe ausführen und wie folgt eine Verbindung zur Beispieldatenbank öffnen:

Schritt 1) In diesem Schritt

1. Öffnen Sie „Arbeitsplatz“ und navigieren Sie zum folgenden Verzeichnis „C:\sqlite" und
2. Dann öffnen Sie „sqlite3.exe"

Schritt 2) Öffnen Sie die Datenbank“TutorialsSampleDB.db“ durch den folgenden Befehl:

Jetzt können Sie jede Art von Abfrage in der Datenbank ausführen.

SQLite INNER JOIN

Der INNER JOIN gibt nur die Zeilen zurück, die der Join-Bedingung entsprechen, und eliminiert alle anderen Zeilen, die nicht der Join-Bedingung entsprechen.

Beispiel

Im folgenden Beispiel verknüpfen wir die beiden Tabellen „Die Kursteilnehmer" und "Fachbereiche” mit DepartmentId, um den Abteilungsnamen für jeden Studenten zu erhalten, wie folgt:

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

Erläuterung des Codes

Der INNER JOIN funktioniert wie folgt:

• In der Select-Klausel können Sie die gewünschten Spalten aus den beiden referenzierten Tabellen auswählen.
• Die INNER JOIN-Klausel wird nach der ersten Tabelle geschrieben, auf die mit der „From“-Klausel verwiesen wird.
• Dann wird die Join-Bedingung mit ON angegeben.
• Für referenzierte Tabellen können Aliase angegeben werden.
• Das INNER-Wort ist optional, Sie können einfach JOIN schreiben.

Ausgang

• Der INNER JOIN erzeugt die Datensätze sowohl von den Studenten- als auch von den Abteilungstabellen, die der Bedingung entsprechen: „Students.DepartmentId = Departments.DepartmentId „. Die nicht übereinstimmenden Zeilen werden ignoriert und nicht in das Ergebnis einbezogen.
• Aus diesem Grund wurden von dieser Abfrage nur 8 von 10 Studenten mit den Fachbereichen IT, Mathematik und Physik zurückgegeben. Die Studenten „Jena“ und „George“ wurden hingegen nicht berücksichtigt, da sie eine Null-Abteilungs-ID haben, die nicht mit der Spalte „departmentId“ aus der Abteilungstabelle übereinstimmt. Wie folgt:

  

SQLite BEITRETEN … NUTZEN

Der INNER JOIN kann mit der „USING“-Klausel geschrieben werden, um Redundanz zu vermeiden. Anstatt also „ON Students.DepartmentId = Departments.DepartmentId“ zu schreiben, können Sie einfach „USING(DepartmentID)“ schreiben.

Sie können „JOIN .. USING“ immer dann verwenden, wenn die Spalten, die Sie in der Join-Bedingung vergleichen, denselben Namen haben. In solchen Fällen ist es nicht erforderlich, sie mit der On-Bedingung zu wiederholen und nur die Spaltennamen und anzugeben SQLite werde das erkennen.

Der Unterschied zwischen INNER JOIN und JOIN .. USING:

Mit „JOIN … USING“ schreiben Sie keine Join-Bedingung, sondern nur die Join-Spalte, die den beiden verbundenen Tabellen gemeinsam ist. Anstatt Tabelle1 zu schreiben, schreiben wir „INNER JOIN table2 ON table1.cola = table2.cola“. es wie „table1 JOIN table2 USING(cola)“.

Beispiel

Im folgenden Beispiel verknüpfen wir die beiden Tabellen „Die Kursteilnehmer" und "Fachbereiche” mit DepartmentId, um den Abteilungsnamen für jeden Studenten zu erhalten, wie folgt:

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

Erläuterung

• Im Gegensatz zum vorherigen Beispiel haben wir nicht geschrieben „ON Students.DepartmentId = Departments.DepartmentId„. Wir haben gerade geschrieben: „USING(Abteilungs-ID)".
• SQLite leitet die Verbindungsbedingung automatisch ab und vergleicht die Abteilungs-ID aus beiden Tabellen – „Students“ und „Departments“.
• Sie können diese Syntax immer dann verwenden, wenn die beiden zu vergleichenden Spalten denselben Namen haben.

Ausgang

• Dadurch erhalten Sie genau das gleiche Ergebnis wie im vorherigen Beispiel:

SQLite NATÜRLICHE VERBINDUNG

Ein NATURAL JOIN ähnelt einem JOIN…USING, der Unterschied besteht darin, dass automatisch die Werte aller in beiden Tabellen vorhandenen Spalten auf Gleichheit geprüft werden.

Der Unterschied zwischen INNER JOIN und einem NATURAL JOIN:

• Bei INNER JOIN müssen Sie eine Join-Bedingung angeben, die der Inner Join zum Verbinden der beiden Tabellen verwendet. Beim natürlichen Join hingegen schreiben Sie keine Join-Bedingung. Sie schreiben einfach die Namen der beiden Tabellen ohne jegliche Bedingung. Dann prüft der natürliche Join automatisch die Gleichheit zwischen den Werten für jede Spalte, die in beiden Tabellen vorhanden ist. Der natürliche Join leitet die Join-Bedingung automatisch ab.
• Beim NATURAL JOIN werden alle gleichnamigen Spalten beider Tabellen miteinander abgeglichen. Wenn wir beispielsweise zwei Tabellen mit zwei gemeinsamen Spaltennamen haben (die beiden Spalten sind in den beiden Tabellen mit demselben Namen vorhanden), verbindet der natürliche Join die beiden Tabellen, indem er die Werte beider Spalten und nicht nur die Werte einer Spalte vergleicht Spalte.

Beispiel

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

Erläuterung

• Wir müssen keine Join-Bedingung mit Spaltennamen schreiben (wie wir es bei INNER JOIN getan haben). Wir mussten den Spaltennamen nicht einmal ein einziges Mal schreiben (wie wir es bei JOIN USING getan haben).
• Der natürliche Join scannt beide Spalten der beiden Tabellen. Es wird erkannt, dass die Bedingung aus dem Vergleich der DepartmentId aus den beiden Tabellen „Studenten“ und „Abteilungen“ bestehen sollte.

Ausgang

• Der Natural JOIN gibt Ihnen genau dieselbe Ausgabe wie die Ausgabe, die wir von den Beispielen INNER JOIN und JOIN USING erhalten haben. Denn in unserem Beispiel sind alle drei Abfragen gleichwertig. In manchen Fällen unterscheidet sich die Ausgabe jedoch von einem Inner Join von einem Natural Join. Wenn es beispielsweise mehrere Tabellen mit denselben Namen gibt, gleicht der Natural Join alle Spalten miteinander ab. Der Inner Join gleicht jedoch nur die Spalten in der Join-Bedingung ab (weitere Einzelheiten im nächsten Abschnitt; der Unterschied zwischen dem Inner Join und dem Natural Join).

SQLite LEFT OUTER JOIN

Der SQL-Standard definiert drei Arten von OUTER JOINs: LEFT, RIGHT und FULL, aber SQLite unterstützt nur den natürlichen LEFT OUTER JOIN.

Bei LEFT OUTER JOIN werden alle Werte der Spalten, die Sie aus der linken Tabelle auswählen, in das Ergebnis einbezogen query, sodass der Wert unabhängig davon, ob er mit der Join-Bedingung übereinstimmt oder nicht, in das Ergebnis einbezogen wird.

Wenn also die linke Tabelle „n“ Zeilen enthält, enthalten die Ergebnisse der Abfrage „n“ Zeilen. Wenn jedoch für die Werte der Spalten aus der rechten Tabelle ein Wert, der nicht mit der Join-Bedingung übereinstimmt, einen „Null“-Wert enthält.

Sie erhalten also eine Anzahl von Zeilen, die der Anzahl der Zeilen im linken Join entspricht. Damit erhalten Sie die übereinstimmenden Zeilen aus beiden Tabellen (wie die INNER JOIN-Ergebnisse) sowie die nicht übereinstimmenden Zeilen aus der linken Tabelle.

Beispiel

Im folgenden Beispiel versuchen wir mit „LEFT JOIN“, die beiden Tabellen „Students“ und „Departments“ zu verbinden:

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

Erläuterung

• SQLite Die Syntax von LEFT JOIN ist dieselbe wie die von INNER JOIN; Sie schreiben den LEFT JOIN zwischen den beiden Tabellen, und dann kommt die Join-Bedingung nach der ON-Klausel.
• Die erste Tabelle nach der from-Klausel ist die linke Tabelle. Wohingegen die zweite Tabelle, die nach dem natürlichen LEFT JOIN angegeben wird, die rechte Tabelle ist.
• Die OUTER-Klausel ist optional; LEFT natural OUTER JOIN ist dasselbe wie LEFT JOIN.

Ausgang

• Wie Sie sehen können, sind alle Zeilen aus der Schülertabelle enthalten, also insgesamt 10 Schüler. Auch wenn die Abteilungs-IDs des vierten und letzten Studenten, Jena und George nicht in der Abteilungstabelle vorhanden sind, werden sie ebenfalls einbezogen.
• Und in diesen Fällen ist der Wert „departmentName“ sowohl für Jena als auch für George „null“, da die Tabelle „departments“ keinen „departmentName“ enthält, der mit dem Wert „departmentId“ übereinstimmt.

Lassen Sie uns die vorherige Abfrage mit der linken Verknüpfung mithilfe von Van-Diagrammen genauer erklären:

Der LEFT JOIN gibt alle Studentennamen aus der Studententabelle an, auch wenn der Student eine Abteilungs-ID hat, die in der Abteilungstabelle nicht vorhanden ist. Die Abfrage liefert Ihnen also nicht nur die übereinstimmenden Zeilen als INNER JOIN, sondern auch den zusätzlichen Teil mit den nicht übereinstimmenden Zeilen aus der linken Tabelle, der Schülertabelle.

Beachten Sie, dass jeder Studentenname, der keine passende Abteilung hat, einen „Null“-Wert für den Abteilungsnamen hat, weil es keinen passenden Wert dafür gibt und diese Werte die Werte in den nicht passenden Zeilen sind.

SQLite CROSS JOIN

Ein CROSS JOIN ergibt das kartesische Produkt für die ausgewählten Spalten der beiden verbundenen Tabellen, indem alle Werte aus der ersten Tabelle mit allen Werten aus der zweiten Tabelle abgeglichen werden.

Für jeden Wert in der ersten Tabelle erhalten Sie also „n“ Übereinstimmungen aus der zweiten Tabelle, wobei n die Anzahl der zweiten Tabellenzeilen ist.

Im Gegensatz zu INNER JOIN und LEFT OUTER JOIN müssen Sie bei CROSS JOIN keine Join-Bedingung angeben, weil SQLite benötigt es nicht für den CROSS JOIN.

Die SQLite führt zu einer logischen Ergebnismenge, indem alle Werte aus der ersten Tabelle mit allen Werten aus der zweiten Tabelle kombiniert werden.

Wenn Sie beispielsweise eine Spalte aus der ersten Tabelle (colA) und eine andere Spalte aus der zweiten Tabelle (colB) ausgewählt haben. Die colA enthält zwei Werte (1,2) und die colB enthält auch zwei Werte (3,4).

Dann wird das Ergebnis des CROSS JOIN vier Zeilen sein:

• Zwei Zeilen durch Kombinieren des ersten Werts von colA, der 1 ist, mit den beiden Werten von colB (3,4), die dann (1,3), (1,4) sind.
• Ebenso zwei Zeilen durch Kombination des zweiten Wertes aus Spalte A, der 2 ist, mit den beiden Werten aus Spalte B (3,4), die (2,3), (2,4) sind.

Beispiel

In der folgenden Abfrage versuchen wir einen CROSS JOIN zwischen den Tabellen „Students“ und „Departments“:

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

Erläuterung

• Im SQLite Wählen Sie aus mehreren Tabellen aus. Wir haben gerade zwei Spalten ausgewählt: „studentname“ aus der Tabelle „Studenten“ und „departmentName“ aus der Tabelle „departments“.
• Für den Cross-Join haben wir keine Join-Bedingung angegeben, sondern lediglich die beiden Tabellen kombiniert mit CROSS JOIN in der Mitte.

Ausgang

Wie Sie sehen, besteht das Ergebnis aus 40 Zeilen; 10 Werte aus der Tabelle „Studenten“ werden den 4 Abteilungen aus der Tabelle „Abteilungen“ zugeordnet. Wie folgt:

• Vier Werte für die vier Abteilungen aus der Abteilungstabelle stimmten mit dem ersten Studenten Michel überein.
• Vier Werte für die vier Abteilungen aus der Abteilungstabelle stimmten mit dem zweiten Schüler John überein.
• Vier Werte für die vier Abteilungen aus der Abteilungstabelle, die mit dem dritten Studenten Jack übereinstimmen … und so weiter.

Zusammenfassung

Die richtigen SQLite JOIN-Abfrage: Sie können eine oder mehrere Tabellen oder Unterabfragen miteinander verknüpfen, um Spalten aus beiden Tabellen oder Unterabfragen auszuwählen.