Eine illustrierte Anleitung zum Multiple Join

Bisher haben unsere Artikel in der Reihe "Ein illustrierter Leitfaden" verschiedene Join-Typen erklärt: INNER JOINs, OUTER JOINs (LEFT JOIN, RIGHT JOIN, FULL JOIN), CROSS JOIN, self-join und non-equi join. In diesem letzten Artikel der Reihe zeigen wir Ihnen, wie Sie SQL-Abfragen erstellen, die Daten aus mehreren Tabellen mit einem oder mehreren Join-Typen abgleichen.

Join-Typen in SQL-Abfragen

Bevor wir mit der Besprechung von SQL-Beispielabfragen beginnen, die mehrere Join-Typen verwenden, lassen Sie uns kurz die bisher behandelten Join-Typen rekapitulieren, um sicherzugehen, dass Sie die Unterschiede verstehen. Zu diesem Zweck finden Sie hier eine kurze Zusammenfassung in Form einer Tabelle. Werfen Sie einen Blick darauf:

Type of JOIN	Matching records from tables	Explanation
INNER JOIN(JOIN) SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue	An INNER JOIN returns records that match in both tables.
LEFT JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t LEFT JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue 1 S NULL NULL NULL	A LEFT JOIN returns all records from the left table, even when they do not match in the right table. Missing values become NULLs.
RIGHT JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t RIGHT JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue NULL NULL NULL 2 NULL	A RIGHT JOIN returns all records from the right table, even when they do not match those in the left table. Missing values become NULLs.
FULL JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t FULL JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue 1 S NULL NULL NULL NULL NULL NULL 2 NULL	A FULL JOIN returns all records from both tables: left and right, even when rows do not match. Missing values become NULLs.
CROSS JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t CROSS JOIN color c ;	Result: id size color_id id color 2 M 1 1 yellow 2 M 1 2 NULL 2 M 1 3 blue 3 NULL 3 1 yellow 3 NULL 3 2 NULL 3 NULL 3 3 blue 1 S NULL 1 yellow 1 S NULL 2 NULL 1 S NULL 3 blue	A CROSS JOIN returns the Cartesian product of the records from both tables. This means that each record from the left table is joined with each record from the right table. Missing values become NULLs.

Beachten Sie, dass das Ergebnis eines jeden Join-Typs Daten enthält, die aus den Tabellen tshirt und stammen. color. Der spezifische Join-Typ bestimmt den Inhalt der Tabelle, die zurückgegeben werden soll.

Was ist ein Multiple Join in SQL?

Jede Abfrage kann aus null, einem oder mehreren Joins bestehen. Ein Multiple Join ist die Verwendung von mehr als einem Join in einer einzigen Abfrage. Die verwendeten Joins können alle vom gleichen Typ sein, oder sie können sich in ihrem Typ unterscheiden. Wir beginnen unsere Diskussion mit einer Beispielabfrage, die zwei Joins desselben Typs verwendet. Werfen Sie einen Blick auf die folgende Abfrage.

SELECT v.name, c.name,  p.lastname
FROM vehicle v
INNER JOIN color c ON  v.color_id = c.id
INNER JOIN person p ON v.person_id = p.id ;

Die Abfrage ruft zwei INNER JOINs auf, um drei Tabellen zu verbinden: vehicle, person und color. Es werden nur die Datensätze zurückgegeben, die in jeder Tabelle eine Übereinstimmung aufweisen. Schauen Sie sich zunächst die Datensätze an, die verknüpft wurden.

Es gibt drei Datensätze, die drei Tabellen in der Datenbank entsprechen: vehicle, color und person, die unten dargestellt sind.

person

color

vehicle

Sie können sehen, dass jeder vehicle in der Fahrzeugtabelle eine Farbe hat, mit Ausnahme von motorcycle. Jedem Fahrzeug ist ein Besitzer zugewiesen, mit Ausnahme von bicycle, das keinen Besitzer hat. Eine der Farben (blue) in der Tabelle color Tabelle ist keinem Fahrzeug zugewiesen. Außerdem ist für das Motorrad keine Farbe in der Datenbank verfügbar. Andererseits hat bicycle eine zugewiesene Farbe, aber keinen Besitzer. Schließlich ist der Person mit dem Namen Miller kein Fahrzeug zugewiesen.

In der obigen Abfrage haben wir eine Mehrfachverknüpfung verwendet, um nur die Fahrzeuge abzurufen, denen sowohl eine Farbe als auch ein Besitzer zugewiesen ist. Die Tabelle vehicle Tabelle hat die color_id Spalte, die den Besitzer identifiziert color in der Farbtabelle identifiziert, als auch die person_id Spalte, die den person in der Personentabelle.

Abfrageergebnis:

Es stellt sich heraus, dass nur zwei Datensätze den Kriterien entsprechen, die durch die beiden inneren Joins definiert sind.

Die folgende Abbildung zeigt die Reihenfolge, in der die Datensätze aus den jeweiligen Tabellen verbunden wurden.

Beachten Sie, dass alle JOIN Operationen von links nach rechts durchgeführt werden. Im ersten Schritt werden die Tabellen im ersten JOIN abgeglichen (Tabellen vehicle und color). Als Ergebnis wird eine Zwischentabelle erstellt. Im zweiten Schritt wird diese Zwischentabelle (die als linke Tabelle behandelt wird) mit einer anderen Tabelle (Tabelle person) unter Verwendung der zweiten JOIN verbunden.

Erinnern Sie sich daran, dass eine einzelne JOIN eines beliebigen Typs eine einzelne Zwischentabelle (gemeinhin als abgeleitete Tabelle bezeichnet) während einer Multi-Join-Abfrage erzeugt.

Gemischter linker und rechter Join mit Inner Join

Es ist auch möglich, verschiedene Arten von Joins in einer Multi-Join-Abfrage zu kombinieren. Nehmen wir ein Beispiel mit einer INNER JOIN und LEFT JOIN. Angenommen, wir wollen unsere Datenbank nach allen Personen abfragen, die ein farbiges Fahrzeug besitzen oder gar kein Fahrzeug besitzen.

Intuitiv würde man mit der Tabelle person Tabelle und verbinden sie mit der vehicle Tabelle mit Hilfe von LEFT JOIN verbinden. In diesem Fall würde LEFT JOIN jeden Datensatz aus der person Tabelle mit einem Datensatz aus der vehicle Tabelle abgleichen, und für jede Person, für die kein übereinstimmender Datensatz gefunden wurde, würde es fehlende Werte mit NULLs auffüllen. Diese Verknüpfung erzeugt eine Liste aller Personen in der Datenbank mit zugehörigen Fahrzeugdaten, auch wenn sie kein Fahrzeug besitzen. Wir sind jedoch daran interessiert, nur Fahrzeuge mit zugewiesenen Farben zu sehen. Das bedeutet, dass wir eine INNER JOIN für die Tabellen vehicle und color. Hier ist eine Abfrage, die diese spezielle Anforderung erfüllt, aber erfüllt sie auch die Aufgabe?

SELECT v.name vehicle_name, c.name color_name,  p.lastname
FROM person p
LEFT JOIN vehicle v ON  v.person_id = p.id
INNER JOIN color c ON v.color_id = c.id ;

Nein! Diese Abfrage liefert das gleiche Ergebnis wie unsere frühere Abfrage (bei der nur INNER JOINverwendet wurde). Unsere Liste enthält keine Personen ohne Fahrzeug.

Ergebnis:

Aber was ist passiert? INNER JOIN hat die Ergebnisse übersprungen, die nicht in beiden Tabellen übereinstimmten, d. h. in der abgeleiteten Tabelle (die durch die Verknüpfung der Tabellen person und vehicle) und der color Tabelle. Wie kann man dieses Problem lösen?

Die folgende Abfrage stellt eine von mehreren möglichen Lösungen dar. Hier gibt die abgeleitete Tabelle nur Fahrzeuge mit Farben zurück, und wird dann RIGHT JOINmit der person Tabelle verknüpft, um alle Personen zu erhalten.

SELECT p.lastname, v.name, c.name
FROM vehicle v
INNER JOIN color c ON  v.color_id = c.id
RIGHT JOIN person p ON v.person_id = p.id ;

Ergebnis:

Jetzt haben wir eine Liste aller Personen: diejenigen mit farbigen Fahrzeugen und diejenigen ohne Fahrzeuge. Wir begannen mit einer INNER JOIN von Tabellen vehicle und color. Jedem Fahrzeug, das in der abgeleiteten Tabelle enthalten ist, muss eine Farbe zugewiesen werden, weshalb diese Verknüpfungsart geeignet ist. Nachdem wir die farbigen Fahrzeuge ausgewählt hatten, konnten wir nun eine RIGHT JOIN auf die abgeleitete Tabelle mit der person So erhalten wir Personen, die keine Fahrzeughalter sind, neben denen (aus der abgeleiteten Tabelle), die ein farbiges Fahrzeug besitzen.

Eine andere Methode zur Lösung dieses Problems besteht darin, eine LEFT JOIN auf die Personentabelle und eine Unterabfrage zu verwenden, in der wir eine INNER JOIN auf die Tabellen vehicle und color.

Werfen Sie einen Blick auf die folgende Abfrage.

SELECT p.lastname, o.vehicle_name, o.color_name
FROM person p LEFT JOIN
(  SELECT v.name vehicle_name, c.name color_name, v.person_id
    FROM vehicle v
    INNER JOIN color c ON v.color_id=c.id
) o ON  o.person_id = p.id;

Gemischte JOINs mit Full JOIN

Ein weiterer Typ von Mehrfach-Join verwendet Full-Joins. Schauen wir uns zunächst einen Mehrfach-Join nur mit Full-Joins an.

SELECT p.lastname, v.name, c.name
FROM vehicle v
FULL JOIN color c ON  v.color_id = c.id
FULL JOIN person p ON v.person_id = p.id ;

Die obige Abfrage gleicht die Datensätze aus drei Tabellen ab: person, vehicle und color so, dass auch Datensätze, die in den beiden anderen Tabellen nicht übereinstimmen, in der Ergebnistabelle erscheinen. Leere Spalten werden mit den Werten von NULL aufgefüllt. Deshalb liefert die Abfrage alle Personen unabhängig davon, ob sie ein Fahrzeug haben, alle Fahrzeuge unabhängig davon, ob ihnen eine Farbe zugewiesen ist, und alle Farben unabhängig davon, ob sie einem Fahrzeug zugewiesen sind.

Ergebnis:

Wir haben Full-Joins verwendet, um alle Datensätze zu verbinden, auch die, die nicht übereinstimmen. Denken Sie daran, dass Full-Joins alle Datensätze zurückgeben, während Inner-Joins nur diejenigen zurückgeben, die übereinstimmen.

Die folgende Abbildung erklärt die Reihenfolge, in der die Tabellen verbunden wurden.

FULL JOIN kann auch in einer Abfrage mit einem anderen Join-Typ erscheinen, wodurch ein Multiple-Join mit gemischten Typen entsteht. Die folgende Abfrage verwendet eine FULL JOIN mit einer INNER JOIN.

SELECT p.lastname, v.name, c.name
FROM vehicle v
INNER  JOIN color c ON  v.color_id = c.id
FULL  JOIN person p ON v.person_id = p.id ;

Mit dieser Abfrage können wir eine Liste aller Personen abrufen, unabhängig davon, ob sie Fahrzeughalter sind oder nicht, und alle Fahrzeuge, denen eine Farbe zugewiesen ist.

Hier sehen Sie, wie die beiden Verknüpfungen funktionieren:

Zunächst werden die Tabellen vehicle und color mit einer INNER JOIN kombiniert. Anschließend wird die abgeleitete Tabelle mit der Tabelle person Tabelle mit Hilfe von FULL JOIN kombiniert. Das Ergebnis sieht so aus:

Zusammenfassung

Eine einzelne SQL-Abfrage kann zwei oder mehr Tabellen verbinden. Wenn drei oder mehr Tabellen beteiligt sind, können Abfragen einen einzigen Join-Typ mehr als einmal verwenden, oder sie können mehrere Join-Typen verwenden. Bei der Verwendung mehrerer Join-Typen müssen wir die Join-Reihenfolge sorgfältig abwägen, um das gewünschte Ergebnis zu erzielen. Die in diesem Artikel vorgestellten Beispiele zeigen deutlich, wie eine geringfügige Änderung des Join-Typs (oder, im Falle mehrerer Joins, der Reihenfolge, in der sie in der Abfrage erscheinen) das Abfrageergebnis völlig verändern und den Erfolg der Abfrage ausmachen oder zunichte machen kann.

Auf welche Join-Kombinationen sollten wir besonders achten? INNER JOINs mit OUTER JOINs und OUTER JOINs mit OUTER JOINs. Jede dieser Kombinationen kann bei unsachgemäßer Verwendung zu fehlerhaften Abfrageergebnissen führen.

Erfahren Sie mehr über SQL

Grundlegende Kenntnisse über SQL-Joins sind ein absolutes Muss, aber die meisten SQL-Anfänger fühlen sich von JOIN Anweisungen eingeschüchtert. Die Wahrheit ist, dass es absolut nichts zu befürchten gibt!

In diesem Artikel haben wir besprochen, wie man Mehrfach-Joins in einer einzigen Abfrage verwendet: entweder LIKE oder gemischte JOIN. Weitere Informationen über Joins finden Sie im umfassenden SQL-Material der Vertabelo Academy. In den Kursen werden Sie Ihr Fachwissen erheblich erweitern und Ihre neuen Kenntnisse anhand der bereitgestellten interaktiven Übungen testen und verfeinern. Beginnen Sie mit dem Kurs SQL für Anfänger , wenn Sie keine Vorkenntnisse in SQL haben. Schlagen Sie den Kurs SQL JOINs auf, um durch zahlreiche interaktive Übungen zu JOIN Anweisungen praktische Erfahrungen bei der Abfrage mehrerer Tabellen zu sammeln. Probieren Sie es jetzt kostenlos aus!