Beispiel für SQLite-Join: Natürlicher linker äußerer, innerer, gekreuzter Join mit Tabellen

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 Folgendes enthalten:wing Syntax:

Syntax der SQLite JOIN-Klausel
Syntax der SQLite JOIN-Klausel

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 Join-Typ 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 das für alle folgendenwing Beispiele für SQLite JOIN-Tabellen: Sie müssen sqlite3.exe ausführen und eine Verbindung zur Beispieldatenbank als flo herstellenwing:

Schritt 1) In diesem Schritt

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

SQLite JOIN-Klausel

Schritt 2) Öffnen Sie die Datenbank“TutorialsSampleDB.db” von den folgendenwing Befehl:

SQLite JOIN-Klausel

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.

SQLite INNER JOIN
SQLite INNER JOIN

Beispiel

Im Folgendenwing Beispiel: Wir werden die beiden Tabellen verbinden „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 folgtwing:

  • 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.

Output

SQLite INNER JOIN-Beispiel

  • 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 bei dieser Abfrage mit den Fachbereichen IT, Mathematik und Physik nur 8 Studierende von 10 Studierenden zurückgegeben. Die Studenten „Jena“ und „George“ hingegen wurden nicht berücksichtigt, da sie eine Null-Abteilungs-ID haben, die nicht mit der Abteilungs-ID-Spalte aus der Abteilungstabelle übereinstimmt. Wie folgtwing:

    SQLite INNER JOIN-Beispiel

SQLite JOIN … USING

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 Ein-Bedingung zu wiederholen. Geben Sie einfach die Spaltennamen an, und SQLite erkennt dies.

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 Folgendenwing Beispiel: Wir werden die beiden Tabellen verbinden „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 Join-Bedingung automatisch ab und compares die DepartmentId aus beiden Tabellen – Studenten und Abteilungen.
  • Sie können diese Syntax immer dann verwenden, wenn die beiden zu vergleichenden Spalten denselben Namen haben.

Output

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

SQLite JOIN-Beispiel

SQLite NATÜRLICHER JOIN

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.

Output

SQLite NATURAL JOIN-Beispiel

  • Der Natural JOIN liefert Ihnen genau die gleiche Ausgabe wie die Ausgabe, die wir aus den INNER JOIN- und JOIN USING-Beispielen erhalten haben. Denn in unserem Beispiel sind alle drei Abfragen gleichwertig. In einigen Fällen unterscheidet sich die Ausgabe jedoch von der eines inneren Joins von der eines natürlichen Joins. Wenn beispielsweise mehrere Tabellen mit demselben Namen vorhanden sind, gleicht der natürliche Join alle Spalten miteinander ab. Der innere Join stimmt jedoch nur mit den Spalten in der Join-Bedingung überein (mehr details 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 Folgendenwing Beispielsweise werden wir den „LEFT JOIN“ ausprobieren, um die beiden Tabellen „Studenten“ und „Abteilungen“ zu verbinden:

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

Erläuterung

  • Die SQLite-LEFT-JOIN-Syntax ist dieselbe wie die INNER-JOIN-Syntax. 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.

Output

SQLite LEFT OUTER JOIN-Beispiel

  • 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:

SQLite LEFT OUTER JOIN

SQLite LEFT OUTER JOIN

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, da SQLite diese für den CROSS JOIN nicht benötigt.

Das 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.
  • Likewise, zwei Zeilen durch Kombinieren des zweiten Werts von colA, der 2 ist, mit den beiden Werten von colB (3,4), die (2,3), (2,4) sind.

Beispiel

Im Folgendenwing Abfrage werden wir CROSS JOIN zwischen den Tabellen „Studenten“ und „Abteilungen“ ausprobieren:

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

Erläuterung

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

Output

SQLite CROSS JOIN-Beispiel

Wie Sie sehen, sind das Ergebnis 40 Zeilen; 10 Werte aus der Studententabelle wurden mit den 4 Abteilungen aus der Abteilungstabelle abgeglichen. Wie folgtwing:

  • 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

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