Joins in SQL: Tabellen effizient kombinieren
Einführung
Viele Datenbankdesigns trennen Informationen in verschiedene Tabellen basierend auf den Beziehungen zwischen bestimmten Datenpunkten. Selbst in solchen Fällen wird es wahrscheinlich Zeiten geben, in denen jemand Informationen aus mehr als einer Tabelle gleichzeitig abrufen möchte.
Eine gängige Methode, um Daten aus mehreren Tabellen in einer einzigen SQL-Abfrage zu erhalten, ist die Verwendung einer JOIN-Klausel. Basierend auf den Join-Operationen der relationalen Algebra kombiniert eine JOIN-Klausel separate Tabellen, indem sie Zeilen in jeder Tabelle miteinander vergleicht, die miteinander in Beziehung stehen. Üblicherweise basiert diese Beziehung auf einem Paar von Spalten – eine aus jeder Tabelle – die gemeinsame Werte enthalten, wie z.B. der Fremdschlüssel einer Tabelle und der Primärschlüssel einer anderen Tabelle, auf die der Fremdschlüssel verweist.
Dieser Leitfaden beschreibt, wie man eine Vielzahl von SQL-Abfragen erstellt, die eine JOIN-Klausel beinhalten. Er hebt auch verschiedene Arten von JOIN-Klauseln hervor, wie sie Daten aus mehreren Tabellen kombinieren und wie man Spaltennamen aliasiert, um die Schreibweise von JOIN-Operationen zu vereinfachen.
Voraussetzungen für Joins in SQL
Um diesem Leitfaden zu folgen, benötigen Sie einen Computer, der ein relationales Datenbankmanagementsystem (RDBMS) verwendet, das SQL nutzt. Die Anweisungen und Beispiele in diesem Leitfaden wurden mit der folgenden Umgebung validiert:
- Ein Server mit Ubuntu 20.04, einem Nicht-Root-Benutzer mit administrativen Rechten und einer mit UFW konfigurierten Firewall, wie im Leitfaden zur Ersteinrichtung des Servers für Ubuntu 20.04 beschrieben.
- MySQL, das auf dem Server installiert und gesichert ist, wie im Leitfaden „How To Install MySQL on Ubuntu 20.04“ beschrieben. Dieser Leitfaden wurde mit einem neu erstellten Benutzer verifiziert, wie in Schritt 3 beschrieben.
Hinweis: Beachten Sie bitte, dass viele RDBMSs ihre eigenen einzigartigen Implementierungen von SQL verwenden. Obwohl die in diesem Tutorial gezeigten Befehle in den meisten RDBMSs funktionieren sollten, kann die genaue Syntax oder Ausgabe abweichen, wenn Sie sie auf einem System testen, das nicht MySQL ist.
Sie benötigen außerdem eine Datenbank mit einigen Tabellen, die mit Beispieldaten gefüllt sind, um JOIN-Operationen zu üben. Wir empfehlen Ihnen, den Abschnitt „Verbindung zu MySQL und Einrichtung einer Beispieldatenbank“ zu lesen, um zu erfahren, wie Sie sich mit einem MySQL-Server verbinden und die Testdatenbank erstellen, die in den Beispielen dieses Leitfadens verwendet wird.
Verbindung zu MySQL und Einrichtung einer Beispieldatenbank für Joins in SQL
Wenn Ihr SQL-Datenbanksystem auf einem Remote-Server läuft, stellen Sie eine SSH-Verbindung zu Ihrem Server von Ihrer lokalen Maschine aus her:
ssh sammy@your_server_ip
Öffnen Sie dann die MySQL-Serveraufforderung und ersetzen Sie „sammy“ durch den Namen Ihres MySQL-Benutzerkontos:
mysql -u sammy -p
Erstellen Sie eine Datenbank namens „joinsDB“:
CREATE DATABASE joinsDB;
Wenn die Datenbank erfolgreich erstellt wurde, erhalten Sie eine Ausgabe wie diese:
Query OK, 1 row affected (0.01 sec)
Um die „joinsDB“-Datenbank auszuwählen, führen Sie die folgende USE-Anweisung aus:
USE joinsDB;
Ausgabe:
Database changed
Nachdem Sie „joinsDB“ ausgewählt haben, erstellen Sie einige Tabellen darin. Für die in diesem Leitfaden verwendeten Beispiele stellen Sie sich vor, dass Sie eine Fabrik betreiben und beschlossen haben, Informationen über Ihre Produktlinie, Mitarbeiter im Verkaufsteam und die Verkäufe Ihres Unternehmens in einer SQL-Datenbank zu verfolgen. Sie planen, mit drei Tabellen zu beginnen, wobei die erste Tabelle Informationen zu Ihren Produkten speichert. Diese erste Tabelle benötigt drei Spalten:
- productID: Die Identifikationsnummer jedes Produkts, die mit dem int-Datentyp ausgedrückt wird. Diese Spalte dient als Primärschlüssel der Tabelle, was bedeutet, dass jeder Wert als eindeutiger Bezeichner für die jeweilige Zeile fungiert. Da jeder Wert in einem Primärschlüssel einzigartig sein muss, wird in dieser Spalte auch eine UNIQUE-Bedingung angewendet.
- productName: Der Name jedes Produkts, der mit dem varchar-Datentyp und einer maximalen Länge von 20 Zeichen ausgedrückt wird.
- price: Der Preis jedes Produkts, der mit dem decimal-Datentyp ausgedrückt wird. Diese Anweisung gibt an, dass Werte in dieser Spalte auf maximal vier Stellen begrenzt sind, wobei zwei dieser Stellen nach dem Dezimalpunkt liegen. Daher reicht der zulässige Wertebereich von -99,99 bis 99,99.
Erstellen Sie eine Tabelle namens „products“, die diese drei Spalten enthält:
CREATE TABLE products (
productID int UNIQUE,
productName varchar(20),
price decimal (4,2),
PRIMARY KEY (productID)
);
Die zweite Tabelle speichert Informationen über die Mitarbeiter im Verkaufsteam Ihres Unternehmens. Sie entscheiden, dass diese Tabelle ebenfalls drei Spalten benötigt:
- empID: Ähnlich wie die Spalte „productID“ enthält diese Spalte eine eindeutige Identifikationsnummer für jeden Mitarbeiter im Verkaufsteam, ausgedrückt mit dem int-Datentyp. Auch diese Spalte erhält eine UNIQUE-Bedingung und dient als Primärschlüssel der „team“-Tabelle.
- empName: Der Name jedes Verkäufers, ausgedrückt mit dem varchar-Datentyp und einer maximalen Länge von 20 Zeichen.
- productSpecialty: Jedes Mitglied Ihres Verkaufsteams hat ein Produkt als Spezialisierung. Sie können jedes Produkt verkaufen, aber ihr Fokus liegt auf dem Produkt, auf das sie sich spezialisiert haben. Diese Spalte enthält den „productID“-Wert des Produkts, auf das sich jeder Mitarbeiter spezialisiert.
Um sicherzustellen, dass die Spalte „productSpecialty“ nur gültige Produkt-ID-Nummern enthält, entscheiden Sie sich, eine Fremdschlüssel-Bedingung auf diese Spalte anzuwenden, die auf die Spalte „productID“ der „products“-Tabelle verweist. Eine Fremdschlüssel-Bedingung stellt eine Beziehung zwischen zwei Tabellen her, indem sie verlangt, dass Werte in der betreffenden Spalte bereits in der verweisenden Spalte existieren. Im folgenden CREATE TABLE-Befehl erfordert die FOREIGN KEY-Bedingung, dass jeder Wert in der Spalte „productSpecialty“ in der „team“-Tabelle bereits in der Spalte „productID“ der „products“-Tabelle existiert.
Erstellen Sie eine Tabelle namens „team“ mit diesen drei Spalten:
CREATE TABLE team (
empID int UNIQUE,
empName varchar(20),
productSpecialty int,
PRIMARY KEY (empID),
FOREIGN KEY (productSpecialty) REFERENCES products (productID)
);
Die letzte Tabelle, die Sie erstellen, speichert Aufzeichnungen über die Verkäufe des Unternehmens. Diese Tabelle hat vier Spalten:
- saleID: Ähnlich wie die Spalten „productID“ und „empID“ enthält diese Spalte eine eindeutige Identifikationsnummer für jeden Verkauf, ausgedrückt mit dem int-Datentyp. Auch diese Spalte erhält eine UNIQUE-Bedingung und kann als Primärschlüssel für die „sales“-Tabelle dienen.
- quantity: Die Anzahl der verkauften Einheiten jedes Produkts, ausgedrückt mit dem int-Datentyp.
- productID: Die Identifikationsnummer des verkauften Produkts, ausgedrückt als int.
- salesperson: Die Identifikationsnummer des Mitarbeiters, der den Verkauf getätigt hat.
Wie die Spalte „productSpecialty“ in der „team“-Tabelle entscheiden Sie sich, auf die Spalten „productID“ und „salesperson“ Fremdschlüssel-Bedingungen anzuwenden. So stellen Sie sicher, dass diese Spalten nur Werte enthalten, die bereits in den Spalten „productID“ der „products“-Tabelle und „empID“ der „team“-Tabelle existieren.
Erstellen Sie eine Tabelle namens „sales“ mit diesen vier Spalten:
CREATE TABLE sales (
saleID int UNIQUE,
quantity int,
productID int,
salesperson int,
PRIMARY KEY (saleID),
FOREIGN KEY (productID) REFERENCES products (productID),
FOREIGN KEY (salesperson) REFERENCES team (empID)
);
Füllen Sie nun die „products“-Tabelle mit Beispieldaten, indem Sie den folgenden INSERT INTO-Befehl ausführen:
INSERT INTO products
VALUES
(1, 'widget', 18.99),
(2, 'gizmo', 14.49),
(3, 'thingamajig', 39.99),
(4, 'doodad', 11.50),
(5, 'whatzit', 29.99);
Die „team“-Tabelle wird anschließend mit Beispieldaten gefüllt:
INSERT INTO team
VALUES
(1, 'Florence', 1),
(2, 'Mary', 4),
(3, 'Diana', 3),
(4, 'Betty', 2);
Füllen Sie auch die „sales“-Tabelle mit Beispieldaten:
INSERT INTO sales
VALUES
(1, 7, 1, 1),
(2, 10, 5, 4),
(3, 8, 2, 4),
(4, 1, 3, 3),
(5, 5, 1, 3);
Zuletzt stellen Sie sich vor, dass Ihr Unternehmen einige Verkäufe ohne Beteiligung eines Mitarbeiters im Verkaufsteam getätigt hat. Um diese Verkäufe zu erfassen, führen Sie die folgende Operation aus, um drei Zeilen zur „sales“-Tabelle hinzuzufügen, die keinen Wert für die Spalte „salesperson“ enthalten:
INSERT INTO sales (saleID, quantity, productID)
VALUES
(6, 1, 5),
(7, 3, 1),
(8, 4, 5);
Damit sind Sie bereit, den Rest des Leitfadens zu folgen und zu lernen, wie man Tabellen in SQL zusammenführt.
Verstehen der Syntax von Joins in SQL
JOIN-Klauseln können in einer Vielzahl von SQL-Anweisungen verwendet werden, einschließlich UPDATE- und DELETE-Operationen. Zu Illustrationszwecken verwenden jedoch die Beispiele in diesem Leitfaden SELECT-Abfragen, um zu demonstrieren, wie JOIN-Klauseln funktionieren.
Das folgende Beispiel zeigt die allgemeine Syntax einer SELECT-Anweisung, die eine JOIN-Klausel enthält:
SELECT table1.column1, table2.column2
FROM table1 JOIN table2
ON search_condition;
Diese Syntax beginnt mit einer SELECT-Anweisung, die zwei Spalten aus zwei separaten Tabellen zurückgibt. Beachten Sie, dass JOIN-Klauseln den Inhalt von mehr als einer Tabelle vergleichen. Daher gibt diese Beispiel-Syntax an, aus welcher Tabelle jede Spalte ausgewählt werden soll, indem sie den Tabellennamen vor dem Namen der Spalte mit einem Punkt setzt. Dies wird als vollständig qualifizierte Spaltenreferenz bezeichnet.
Sie können vollständig qualifizierte Spaltenreferenzen wie diese in jeder Operation verwenden, aber es ist technisch nur notwendig, sie in Operationen zu verwenden, bei denen zwei Spalten aus verschiedenen Tabellen denselben Namen haben. Es ist jedoch eine gute Praxis, sie zu verwenden, wenn Sie mit mehreren Tabellen arbeiten, da sie dazu beitragen können, JOIN-Operationen leichter lesbar und verständlich zu machen.
Nach der SELECT-Klausel folgt die FROM-Klausel. In jeder Abfrage definieren Sie in der FROM-Klausel das Daten-Set, das durchsucht werden soll, um die gewünschten Daten zurückzugeben. Der einzige Unterschied hier ist, dass die FROM-Klausel zwei Tabellen enthält, die durch das JOIN-Schlüsselwort getrennt sind. Eine hilfreiche Art, sich das Schreiben von Abfragen vorzustellen, ist zu erinnern, dass Sie die Spalten auswählen, die Sie zurückgeben möchten, von welcher Tabelle Sie die Abfrage ausführen möchten.
Danach folgt eine ON-Klausel, die beschreibt, wie die Abfrage die beiden Tabellen durch eine Suchbedingung verbinden soll. Eine Suchbedingung ist eine Menge von einem oder mehreren Prädikaten oder Ausdrücken, die bewerten können, ob eine bestimmte Bedingung „wahr“, „falsch“ oder „unbekannt“ ist. Es kann hilfreich sein, sich eine JOIN-Operation als eine Kombination jeder Zeile aus beiden Tabellen vorzustellen, und dann alle Zeilen zurückzugeben, für die die Suchbedingung in der ON-Klausel als „wahr“ bewertet wird.
In einer ON-Klausel ist es üblich, eine Suchbedingung einzufügen, die testet, ob zwei verwandte Spalten – wie der Fremdschlüssel einer Tabelle und der Primärschlüssel einer anderen Tabelle, auf die der Fremdschlüssel verweist – Werte haben, die gleich sind. Dies wird manchmal als „equi join“ bezeichnet.
Als Beispiel dafür, wie equi joins Daten aus mehreren Tabellen abgleichen, führen Sie die folgende Abfrage mit den zuvor hinzugefügten Beispieldaten aus. Diese Anweisung wird die „products“- und „team“-Tabellen mit einer Suchbedingung verbinden, die auf Übereinstimmung der Werte in ihren jeweiligen Spalten „productID“ und „productSpecialty“ testet. Sie gibt dann die Namen jedes Mitglieds des Verkaufsteams, den Namen jedes Produkts, auf das sie sich spezialisiert haben, und den Preis dieser Produkte zurück:
SELECT team.empName, products.productName, products.price
FROM products JOIN team
ON products.productID = team.productSpecialty;
Hier ist das Ergebnismenge dieser Abfrage:
+----------+-------------+-------+
| empName | productName | price |
+----------+-------------+-------+
| Florence | widget | 18.99 |
| Mary | doodad | 11.50 |
| Diana | thingamajig | 39.99 |
| Betty | gizmo | 14.49 |
+----------+-------------+-------+
4 rows in set (0.00 sec)
Um zu veranschaulichen, wie SQL diese Tabellen kombiniert, um diese Ergebnismenge zu bilden, betrachten wir diesen Prozess im Detail. Es ist zu beachten, dass das Folgende nicht exakt das ist, was passiert, wenn ein Datenbankmanagementsystem zwei Tabellen zusammenführt, aber es kann hilfreich sein, sich JOIN-Operationen als einen Prozess wie diesen vorzustellen.
Zuerst druckt die Abfrage jede Zeile und Spalte in der ersten Tabelle in der FROM-Klausel, „products“:
JOIN Process Example
+-----------+-------------+-------+
| productID | productName | price |
+-----------+-------------+-------+
| 1 | widget | 18.99 |
| 2 | gizmo | 14.49 |
| 3 | thingamajig | 39.99 |
| 4 | doodad | 11.50 |
| 5 | whatzit | 29.99 |
+-----------+-------------+-------+
Dann schaut sie sich jede dieser Zeilen an und vergleicht sie mit einer Zeile aus der „team“-Tabelle, deren „productSpecialty“ dem Wert in „productID“ dieser Zeile entspricht:
JOIN Process Example
+-----------+-------------+-------+-------+----------+------------------+
| productID | productName | price | empID | empName | productSpecialty |
+-----------+-------------+-------+-------+----------+------------------+
| 1 | widget | 18.99 | 1 | Florence | 1 |
| 2 | gizmo | 14.49 | 4 | Betty | 2 |
| 3 | thingamajig | 39.99 | 3 | Diana | 3 |
| 4 | doodad | 11.50 | 2 | Mary | 4 |
| 5 | whatzit | 29.99 | | | |
+-----------+-------------+-------+-------+----------+------------------+
Dann werden alle Zeilen ohne Übereinstimmung entfernt und die Spalten in der Reihenfolge der SELECT-Klausel umgeordnet, nicht benötigte Spalten gelöscht, die Zeilen neu sortiert und das endgültige Ergebnis zurückgegeben:
JOIN Process Example
+----------+-------------+-------+
| empName | productName | price |
+----------+-------------+-------+
| Florence | widget | 18.99 |
| Mary | doodad | 11.50 |
| Diana | thingamajig | 39.99 |
| Betty | gizmo | 14.49 |
+----------+-------------+-------+
4 rows in set (0.00 sec)
Die Verwendung von equi joins ist die häufigste Methode, um Tabellen zu verbinden, aber es ist auch möglich, andere SQL-Operatoren wie <, >, LIKE, NOT LIKE oder sogar BETWEEN in ON-Klausel-Suchbedingungen zu verwenden. Seien Sie jedoch vorsichtig, da die Verwendung komplexerer Suchbedingungen es schwierig machen kann, vorherzusagen, welche Daten im Ergebnis erscheinen werden.
In den meisten Implementierungen können Sie Tabellen mit jeder Spalte verbinden, die einen als „JOIN eligible“ bezeichneten Datentyp hat. Das bedeutet, dass es im Allgemeinen möglich ist, eine Spalte, die numerische Daten enthält, mit einer anderen Spalte zu verbinden, die ebenfalls numerische Daten enthält, unabhängig von deren jeweiligen Datentypen. Ebenso ist es normalerweise möglich, eine Spalte, die Zeichenwerte enthält, mit jeder anderen Spalte zu verbinden, die Zeichen enthalten. Wie bereits erwähnt, werden die Spalten, die Sie zum Verbinden von Tabellen verwenden, normalerweise solche sein, die bereits eine Beziehung zwischen den Tabellen anzeigen, wie ein Fremdschlüssel und der Primärschlüssel einer anderen Tabelle, auf den der Fremdschlüssel verweist.
Viele SQL-Implementierungen erlauben es Ihnen auch, Spalten mit demselben Namen mit dem USING-Schlüsselwort anstelle von ON zu verbinden. So könnte die Syntax für eine solche Operation aussehen:
SELECT table1.column1, table2.column2
FROM table1 JOIN table2
USING (related_column);
In dieser Beispiel-Syntax entspricht die USING-Klausel der ON table1.related_column = table2.related_column;
Da „sales“ und „products“ jeweils eine Spalte namens „productID“ haben, können Sie diese durch das MATCHEN dieser Spalten mit dem USING-Schlüsselwort verbinden. Der folgende Befehl tut dies und gibt die „saleID“ jedes Verkaufs, die verkaufte Menge, den Namen des jeweiligen Produkts und seinen Preis zurück. Außerdem sortiert er das Ergebnis nach „saleID“ in aufsteigender Reihenfolge:
SELECT sales.saleID, sales.quantity, products.productName, products.price
FROM sales JOIN products
USING (productID)
ORDER BY saleID;
Ausgabe
+--------+----------+-------------+-------+
| saleID | quantity | productName | price |
+--------+----------+-------------+-------+
| 1 | 7 | widget | 18.99 |
| 2 | 10 | whatzit | 29.99 |
| 3 | 8 | gizmo | 14.49 |
| 4 | 1 | thingamajig | 39.99 |
| 5 | 5 | widget | 18.99 |
| 6 | 1 | whatzit | 29.99 |
| 7 | 3 | widget | 18.99 |
| 8 | 4 | whatzit | 29.99 |
+--------+----------+-------------+-------+
8 rows in set (0.00 sec)
Beim Verbinden von Tabellen wird das Datenbanksystem manchmal die Zeilen auf eine Weise umsortieren, die schwer vorherzusagen ist. Eine ORDER BY-Klausel wie diese kann helfen, das Ergebnis lesbarer und zusammenhängender zu machen.
Verbindung von mehr als zwei Tabellen
Es kann Fälle geben, in denen Sie Daten aus mehr als nur zwei Tabellen kombinieren müssen. Sie können beliebig viele Tabellen verbinden, indem Sie JOIN-Klauseln in andere JOIN-Klauseln einbetten. Die folgende Syntax zeigt ein Beispiel dafür, wie dies aussieht, wenn drei Tabellen verbunden werden:
SELECT table1.column1, table2.column2, table3.column3
FROM table1 JOIN table2
ON table1.related_column = table2.related_column
JOIN table3
ON table3.related_column = table1_or_2.related_column;
Diese Syntax der FROM-Klausel beginnt damit, dass sie „table1“ mit „table2“ verbindet. Nach der ON-Klausel dieses Joins in SQL startet ein zweiter JOIN in SQL. Dieser kombiniert das erste verbundene Set mit der „table3“. Beachten Sie, dass die dritte Tabelle entweder mit der ersten oder der zweiten Tabelle verbunden werden kann.
Zur Veranschaulichung stellen Sie sich vor, Sie möchten wissen, wie viel Umsatz die Verkäufe Ihrer Mitarbeiter eingebracht haben. Es interessieren Sie aber nur Verkäufe, bei denen ein Mitarbeiter das Produkt verkauft hat, auf das er spezialisiert ist.
Um diese Informationen zu erhalten, könnten Sie die folgende Abfrage ausführen. Diese Abfrage beginnt damit, dass sie die „products“- und „sales“-Tabellen verbindet, indem sie ihre jeweiligen „productID“-Spalten vergleicht. Dann wird die „team“-Tabelle mit den ersten beiden Tabellen verbunden, indem jede Zeile in dem ursprünglichen JOIN mit ihrer „productSpecialty“-Spalte abgeglichen wird. Die Abfrage filtert die Ergebnisse mit einer WHERE-Klausel. So werden nur Zeilen zurückzugeben, bei denen der abgeglichene Mitarbeiter auch derjenige war, der den Verkauf getätigt hat. Diese Abfrage enthält auch eine ORDER BY-Klausel, die die endgültigen Ergebnisse sortiert:
SELECT sales.saleID,
team.empName,
products.productName,
(sales.quantity * products.price)
FROM products JOIN sales
USING (productID)
JOIN team
ON team.productSpecialty = sales.productID
WHERE team.empID = sales.salesperson
ORDER BY sales.saleID;
Beachten Sie, dass unter den Spalten, die in der SELECT-Klausel dieser Abfrage aufgeführt sind, ein Ausdruck enthalten ist. Dieser multipliziert die Werte in der „quantity“-Spalte der „sales“-Tabelle mit den Preiswerten aus der „products“-Tabelle. Es gibt die Produkte dieser Werte in den übereinstimmenden Zeilen zurück:
Ausgabe
+--------+----------+-------------+-----------------------------------+
| saleID | empName | productName | (sales.quantity * products.price) |
+--------+----------+-------------+-----------------------------------+
| 1 | Florence | widget | 132.93 |
| 3 | Betty | gizmo | 115.92 |
| 4 | Diana | thingamajig | 39.99 |
+--------+----------+-------------+-----------------------------------+
3 rows in set (0.00 sec)
Alle bisherigen Beispiele beinhalteten denselben Typ von JOIN-Klausel: den INNER JOIN. Eine Übersicht über INNER Joins in SQL, OUTER Joins und wie sie sich unterscheiden, finden Sie im nächsten Abschnitt.
Inner vs. Outer JOIN-Operationen
Es gibt zwei Haupttypen von JOIN-Klauseln: INNER Joins und OUTER Joins. Der Unterschied zwischen diesen beiden JOIN-Typen besteht darin, welche Daten sie zurückgeben. INNER JOIN-Operationen geben nur übereinstimmende Zeilen aus jeder verbundenen Tabelle zurück. OUTER Joins in SQL geben sowohl übereinstimmende als auch nicht übereinstimmende Zeilen zurück.
Die Beispielsynthesen und Abfragen aus den vorherigen Abschnitten verwendeten alle INNER JOIN-Klauseln, obwohl keine von ihnen das INNER-Schlüsselwort enthält. Die meisten SQL-Implementierungen behandeln jede JOIN-Klausel als INNER JOIN, es sei denn, es wird explizit anders angegeben.
Abfragen, die einen OUTER JOIN angeben, verbinden mehrere Tabellen und geben sowohl übereinstimmende als auch nicht übereinstimmende Zeilen zurück. Dies kann hilfreich sein, um Zeilen mit fehlenden Werten zu finden oder in Fällen, in denen partielle Übereinstimmungen akzeptabel sind.
OUTER JOIN-Operationen können weiter in drei Typen unterteilt werden: LEFT OUTER Joins, RIGHT OUTER Joins und FULL OUTER Joins. LEFT OUTER Joins, oder einfach LEFT Joins, geben jede übereinstimmende Zeile aus den beiden verbundenen Tabellen sowie jede nicht übereinstimmende Zeile aus der „linken“ Tabelle zurück. Im Kontext von JOIN-Operationen ist die „linke“ Tabelle immer die erste Tabelle, die direkt nach dem FROM-Schlüsselwort und links vom JOIN-Schlüsselwort angegeben wird. Ebenso ist die „rechte“ Tabelle die zweite Tabelle oder diejenige, die unmittelbar nach JOIN folgt, und RIGHT OUTER Joins in SQL geben jede übereinstimmende Zeile der verbundenen Tabellen sowie jede nicht übereinstimmende Zeile der „rechten“ Tabelle zurück. Ein FULL OUTER JOIN gibt jede Zeile aus beiden Tabellen zurück, einschließlich aller Zeilen, die aus einer der beiden Tabellen keine Übereinstimmungen haben.
Um zu veranschaulichen, wie diese verschiedenen JOIN-Typen Daten zurückgeben, führen Sie die folgenden Beispielabfragen aus. Diese Abfragen sind identisch, mit dem Unterschied, dass jede einen anderen JOIN-Typ angibt.
Dieses erste Beispiel verwendet einen INNER JOIN, um die „sales“- und „team“-Tabellen zu kombinieren, indem ihre jeweiligen „salesperson“- und „empID“-Spalten verglichen werden. Wieder ist das INNER-Schlüsselwort impliziert, obwohl es nicht explizit enthalten ist:
SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales JOIN team
ON sales.salesperson = team.empID;
Da diese Abfrage eine INNER JOIN-Klausel verwendet, gibt sie nur übereinstimmende Zeilen aus beiden Tabellen zurück:
Ausgabe
+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName |
+--------+----------+-------------+----------+
| 1 | 7 | 1 | Florence |
| 4 | 1 | 3 | Diana |
| 5 | 5 | 3 | Diana |
| 2 | 10 | 4 | Betty |
| 3 | 8 | 4 | Betty |
+--------+----------+-------------+----------+
5 rows in set (0.00 sec)
Diese Version der Abfrage verwendet stattdessen eine LEFT OUTER JOIN-Klausel:
SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales LEFT OUTER JOIN team
ON sales.salesperson = team.empID;
Wie die vorherige Abfrage gibt diese ebenfalls jede übereinstimmende Zeile aus beiden Tabellen zurück. Sie gibt jedoch auch alle Werte aus der „linken“ Tabelle zurück, die keine Übereinstimmungen in der „rechten“ Tabelle haben. Da keine Übereinstimmungen in der rechten Tabelle vorhanden sind, werden nicht übereinstimmende Werte als NULL zurückgegeben:
Ausgabe
+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName |
+--------+----------+-------------+----------+
| 1 | 7 | 1 | Florence |
| 2 | 10 | 4 | Betty |
| 3 | 8 | 4 | Betty |
| 4 | 1 | 3 | Diana |
| 5 | 5 | 3 | Diana |
| 6 | 1 | NULL | NULL |
| 7 | 3 | NULL | NULL |
| 8 | 4 | NULL | NULL |
+--------+----------+-------------+----------+
8 rows in set (0.00 sec)
Diese nächste Version der Abfrage verwendet stattdessen eine RIGHT JOIN-Klausel:
SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales RIGHT JOIN team
ON sales.salesperson = team.empID;
Beachten Sie, dass die JOIN-Klausel dieser Abfrage RIGHT JOIN lautet und nicht RIGHT OUTER JOIN. Ähnlich wie das INNER-Schlüsselwort nicht erforderlich ist, um eine INNER JOIN-Klausel zu spezifizieren, ist OUTER impliziert, wann immer Sie LEFT JOIN oder RIGHT JOIN schreiben.
Das Ergebnis dieser Abfrage gibt alle Zeilen zurück, aber nur nicht übereinstimmende Zeilen aus der rechten Tabelle:
Ausgabe
+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName |
+--------+----------+-------------+----------+
| 1 | 7 | 1 | Florence |
| NULL | NULL | NULL | Mary |
| 4 | 1 | 3 | Diana |
| 5 | 5 | 3 | Diana |
| 2 | 10 | 4 | Betty |
| 3 | 8 | 4 | Betty |
+--------+----------+-------------+----------+
6 rows in set (0.00 sec)
Hinweis: Beachten Sie, dass MySQL keine FULL OUTER JOIN-Klauseln unterstützt. Um zu veranschaulichen, welche Daten diese Abfrage zurückgeben würde, wenn sie eine FULL OUTER JOIN-Klausel verwendet, sieht das Ergebnis in einer PostgreSQL-Datenbank so aus:
SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales FULL OUTER JOIN team
ON sales.salesperson = team.empID;
Ausgabe
saleid | quantity | salesperson | empname
--------+----------+-------------+----------
1 | 7 | 1 | Florence
2 | 10 | 4 | Betty
3 | 8 | 4 | Betty
4 | 1 | 3 | Diana
5 | 5 | 3 | Diana
6 | 1 | |
7 | 3 | |
8 | 4 | |
| | | Mary
(9 rows)
Wie diese Ausgabe zeigt, gibt der FULL JOIN jede Zeile aus beiden Tabellen zurück, einschließlich der nicht übereinstimmenden.
Alias-Namen für Tabellen und Spalten in JOIN-Klauseln
Wenn Sie Tabellen mit langen oder sehr beschreibenden Namen verbinden, kann es mühsam werden, mehrere vollständig qualifizierte Spaltenreferenzen zu schreiben. Um dies zu vermeiden, finden es viele Benutzer hilfreich, Tabellen- oder Spaltennamen kürzere Aliase zu geben.
Fügen Sie in SQL nach der Tabellendefinition in der FROM-Klausel das Schlüsselwort AS mit einem Alias hinzu:
SELECT t1.column1, t2.column2
FROM table1 AS t1 JOIN table2 AS t2
ON t1.related_column = t2.related_column;
Dieses Beispiel verwendet Aliase in der SELECT-Klausel, obwohl sie erst in der FROM-Klausel definiert werden. Das ist möglich, weil in SQL-Abfragen die Ausführungsreihenfolge mit der FROM-Klausel beginnt. Sie sollten Ihre Aliase festlegen, bevor Sie mit dem Schreiben der Abfrage beginnen.
Als Beispiel führen Sie die Abfrage aus, die die „sales“- und „products“-Tabellen verbindet und ihnen die Aliase S und P zuweist:
SELECT S.saleID, S.quantity,
P.productName,
(P.price * S.quantity) AS revenue
FROM sales AS S JOIN products AS P
USING (productID);
Beachten Sie, dass dieses Beispiel auch ein drittes Alias „revenue“ erstellt. Dieses stellt das Produkt der Werte in der Spalte „quantity“ der „sales“-Tabelle und der übereinstimmenden Werte aus der Spalte „price“ der „products“-Tabelle dar. Dies ist nur im Spaltennamen im Ergebnismenge sichtbar. Aliase wie dieser können helfen, die Bedeutung oder den Zweck der Abfrageergebnisse zu verdeutlichen:
Ausgabe
+--------+----------+-------------+---------+
| saleID | quantity | productName | revenue |
+--------+----------+-------------+---------+
| 1 | 7 | widget | 132.93 |
| 2 | 10 | whatzit | 299.90 |
| 3 | 8 | gizmo | 115.92 |
| 4 | 1 | thingamajig | 39.99 |
| 5 | 5 | widget | 94.95 |
| 6 | 1 | whatzit | 29.99 |
| 7 | 3 | widget | 56.97 |
| 8 | 4 | whatzit | 119.96 |
+--------+----------+-------------+---------+
8 rows in set (0.00 sec)
Beachten Sie, dass beim Definieren eines Aliases das AS-Schlüsselwort technisch optional ist. Das vorherige Beispiel könnte auch so geschrieben werden:
SELECT S.saleID, S.quantity, P.productName, (P.price * S.quantity) revenue
FROM sales S JOIN products P
USING (productID);
Obwohl das AS-Schlüsselwort nicht erforderlich ist, um einen Alias zu definieren, ist es eine gute Praxis, es beizufügen. Dadurch ist der Zweck der Abfrage klar und die Lesbarkeit verbessert sich.
Fazit
Durch das Lesen dieses Leitfadens haben Sie gelernt, wie Sie JOIN-Operationen verwenden, um separate Tabellen in einem einzigen Abfrage-Ergebnis zusammenzuführen. Während die hier gezeigten Befehle in den meisten relationalen Datenbanken funktionieren sollten, beachten Sie bitte, dass jede SQL-Datenbank ihre eigene einzigartige Implementierung der Sprache verwendet. Sie sollten die offizielle Dokumentation Ihres DBMS konsultieren, um eine vollständige Beschreibung jedes Befehls und seiner vollständigen Optionen zu erhalten.
Für mehr SQL-Wissen empfehlen wir, die anderen Tutorials der Serie „How To Use SQL“ zu lesen.
