SQLite Verbinden: Natürlicher Linksaußenknoten, Innenknoten, Kreuzknoten 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 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) Öffnen Sie in diesem Schritt „Arbeitsplatz“ und navigieren Sie zum folgenden Verzeichnis „C:\sqlite“. Öffnen Sie anschließend „sqlite3.exe“:

Schritt 2) Öffnen Sie die Datenbank „TutorialsSampleDB.db“ mit folgendem 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 der Join-Bedingung nicht entsprechen.

Beispiel

Im folgenden Beispiel verknüpfen wir die beiden Tabellen „Students“ und „Departments“ mit DepartmentId, um den Abteilungsnamen für jeden Studenten zu erhalten:

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 liefert die Datensätze aus den Tabellen „Studenten“ und „Abteilungen“, die der Bedingung „Students.DepartmentId = Departments.DepartmentId“ entsprechen. Nicht übereinstimmende Zeilen werden ignoriert und nicht in das Ergebnis aufgenommen.

Deshalb wurden bei dieser Abfrage nur 8 von 10 Studierenden aus den Fachbereichen Informatik, Mathematik und Physik zurückgegeben. Die Studierenden „Jena“ und „George“ wurden nicht berücksichtigt, da ihre Fachbereichs-ID fehlt und nicht mit der Spalte „departmentId“ der Tabelle „Fachbereiche“ übereinstimmt.

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:

Bei „JOIN … USING“ schreibt man keine Join-Bedingung, sondern nur die Join-Spalte, die beiden Tabellen gemeinsam ist. Anstatt „INNER JOIN table2 ON table1.cola = table2.cola“ zu schreiben, schreibt man „table1 JOIN table2 USING(cola)“.

Beispiel

Im folgenden Beispiel verknüpfen wir die beiden Tabellen „Students“ und „Departments“ mit DepartmentId, um den Abteilungsnamen für jeden Studenten zu erhalten:

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

Erläuterung

• Anders als im vorherigen Beispiel haben wir nicht „ON Students.DepartmentId = Departments.DepartmentId“ geschrieben, sondern lediglich „USING(DepartmentId)“.
• 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 einem INNER JOIN muss eine Verknüpfungsbedingung angegeben werden, anhand derer die beiden Tabellen verknüpft werden. Bei einem NATIONAL JOIN hingegen wird keine Verknüpfungsbedingung angegeben. Es werden lediglich die Namen der beiden Tabellen ohne weitere Bedingungen angegeben. Der NATIONAL JOIN prüft dann automatisch, ob die Werte jeder Spalte in beiden Tabellen gleich sind. Die Verknüpfungsbedingung wird beim NATIONAL JOIN automatisch ermittelt.
• 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 bei INNER JOIN). Wir müssen den Spaltennamen nicht einmal angeben (wie bei JOIN USING).
• 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 liefert exakt dasselbe Ergebnis wie die Beispiele INNER JOIN und JOIN USING, da in unserem Beispiel alle drei Abfragen äquivalent sind. In manchen Fällen kann sich das Ergebnis eines INNER JOIN jedoch von dem eines NATURAL JOIN unterscheiden. Existieren beispielsweise mehrere Tabellen mit demselben Namen, gleicht der NATURAL JOIN alle Spalten miteinander ab. Der INNER JOIN hingegen gleicht nur die Spalten ab, die in der Join-Bedingung angegeben sind.

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 der Abfrage aufgenommen, unabhängig davon, ob der Wert der Join-Bedingung entspricht oder nicht.

Wenn die linke Tabelle also 'n' Zeilen enthält, liefert die Abfrage ebenfalls 'n' Zeilen. Für die Werte der Spalten aus der rechten Tabelle gilt jedoch: Jeder Wert, der nicht der Verknüpfungsbedingung entspricht, enthält den Wert „null“.

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, sind alle Zeilen der Studententabelle enthalten, insgesamt zehn Studenten. Auch wenn die vierte und letzte Studentin, Jena und George, Abteilungs-IDs haben, die in der Tabelle „Abteilungen“ nicht existieren, sind sie ebenfalls enthalten.

In diesen Fällen ist der Wert departmentName sowohl für Jena als auch für George „null“, da die Tabelle departments keinen departmentNamen enthält, der mit ihrem departmentId-Wert übereinstimmt.

Lassen Sie uns die vorherige Abfrage mit dem Left Join anhand von Venn-Diagrammen genauer erläutern:

Der LEFT JOIN liefert alle Studentennamen aus der Tabelle „students“, selbst wenn die zugehörige Abteilungs-ID nicht in der Tabelle „departments“ existiert. Die Abfrage liefert also nicht nur die übereinstimmenden Zeilen wie der INNER JOIN, sondern zusätzlich auch die nicht übereinstimmenden Zeilen aus der linken Tabelle, also der Tabelle „students“.

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 muss bei CROSS JOIN keine Join-Bedingung angegeben werden, weil SQLite Wird für die Kreuzverbindung nicht benötigt.

Das SQLite wird zu einem logischen Ergebnissatz führen, 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 (Spalte A) und eine weitere Spalte aus der zweiten Tabelle (Spalte B) ausgewählt haben, enthält Spalte A zwei Werte (1, 2) und Spalte B ebenfalls 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 nur die beiden Tabellen mit CROSS JOIN in der Mitte kombiniert.

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 Abteilungsübersicht stimmten mit dem zweiten Studenten John überein.
• Vier Werte für die vier Abteilungen aus der Abteilungstabelle stimmten mit dem dritten Studenten Jack überein… und so weiter.

Häufig gestellte Fragen