Wie man Joins in SQL verwendet

Einführung

Viele Datenbankdesigns trennen Informationen in verschiedene Tabellen, basierend auf den Beziehungen zwischen bestimmten Datenpunkten. Selbst in solchen Fällen kommt es häufig vor, dass man Informationen aus mehreren Tabellen gleichzeitig abrufen möchte.

Eine gängige Methode, um in einer SQL-Operation (Structured Query Language) auf Daten aus mehreren Tabellen zuzugreifen, ist die Verknüpfung von Tabellen mit einer JOIN-Klausel. Angelehnt an die Join-Operation in der relationalen Algebra kombiniert eine JOIN-Klausel separate Tabellen, indem sie die Zeilen in jeder Tabelle vergleicht, die miteinander in Beziehung stehen. Typischerweise basiert diese Beziehung auf einem Spaltenpaar – je eine Spalte aus jeder Tabelle –, das gemeinsame Werte aufweist, beispielsweise den Fremdschlüssel einer Tabelle und den Primärschlüssel einer anderen Tabelle, auf den der Fremdschlüssel verweist.

Dieser Leitfaden erklärt, wie man verschiedene SQL-Abfragen mit einer JOIN-Klausel erstellt. Er zeigt außerdem die verschiedenen Arten von JOIN-Klauseln, wie man Daten aus mehreren Tabellen kombiniert und wie man Spalten mit Aliasen versieht, um das Schreiben von JOIN-Operationen zu vereinfachen.

Voraussetzungen

Um dieser Anleitung zu folgen, benötigen Sie einen Computer mit einem relationalen Datenbankmanagementsystem (RDBMS), das SQL verwendet. Die Anweisungen und Beispiele in dieser Anleitung wurden in der folgenden Umgebung getestet:

• Ein Server mit Ubuntu 20.04, einem Benutzer ohne Root-Rechte, aber mit Administratorrechten, und einer mit UFW konfigurierten Firewall.
• MySQL ist auf dem Server installiert und gesichert.
• Sie benötigen außerdem eine Datenbank mit einigen Tabellen, die mit Beispieldaten gefüllt sind, um die Verwendung von JOIN-Operationen zu üben. Im Abschnitt „Verbindung zu MySQL herstellen und eine Beispieldatenbank einrichten“ weiter unten finden Sie detaillierte Informationen zum Herstellen einer Verbindung zum MySQL-Server und zum Erstellen der in den Beispielen dieses Leitfadens verwendeten Testdatenbank.

Verbindung zu MySQL herstellen und eine Beispieldatenbank einrichten

Wenn Ihr SQL-Datenbanksystem auf einem Remote-Server läuft, stellen Sie eine SSH-Verbindung von Ihrem lokalen Rechner zu Ihrem Server her:

ssh sammy@your_server_ip

Öffnen Sie anschließend die MySQL-Server-Eingabeaufforderung und ersetzen Sie „Sami“ durch Ihren MySQL-Benutzernamen:

mysql -u sammy -p

Erstelle eine Datenbank namens joinsDB:

CREATE DATABASE joinsDB;

Wenn die Datenbank erfolgreich erstellt wurde, erhalten Sie folgende Ausgabe:

Output
Query OK, 1 row affected (0.01 sec)

Um die joinsDB-Datenbank auszuwählen, führen Sie die folgende USE-Anweisung aus:

USE joinsDB;

Output
Database changed

Nachdem Sie joinsDB ausgewählt haben, erstellen Sie darin einige Tabellen. Stellen Sie sich für die Beispiele in diesem Leitfaden vor, Sie betreiben eine Fabrik und möchten Informationen über Ihre Produktionslinie, Ihre Vertriebsmitarbeiter und den Umsatz Ihres Unternehmens in einer SQL-Datenbank erfassen. Sie beginnen mit drei Tabellen, wobei die erste Informationen über Ihre Produkte speichert. Sie legen fest, dass diese erste Tabelle drei Spalten benötigt:

• Produkt-ID: Die Identifikationsnummer jedes Produkts, dargestellt als Integer-Wert. Diese Spalte dient als Primärschlüssel der Tabelle, d. h. jeder Wert identifiziert die entsprechende Zeile eindeutig. Da jeder Wert eines Primärschlüssels eindeutig sein muss, ist diese Spalte mit der UNIQUE-Einschränkung versehen.
• productName: Der Name jedes Produkts wird mit Hilfe des Datentyps varchar und maximal 20 Zeichen angegeben.
• Preis: Der Preis jedes Produkts, angegeben als Dezimalzahl. Dies bedeutet, dass jeder Wert in dieser Spalte maximal vier Stellen haben darf, wobei zwei dieser Stellen rechts vom Dezimaltrennzeichen stehen. Der zulässige Wertebereich in dieser Spalte liegt daher zwischen -99,99 und 99,99.

Erstellen Sie eine Tabelle namens „Produkte“, 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 Ihres Vertriebsteams. Sie entscheiden, dass auch diese Tabelle drei Spalten benötigt:

• empID: Ähnlich der Spalte productID enthält diese Spalte eine eindeutige Identifikationsnummer für jeden Mitarbeiter im Vertriebsteam, dargestellt als Integer-Datentyp. Daher verfügt diese Spalte über eine UNIQUE-Einschränkung und dient als Primärschlüssel für die Teamtabelle.
• empName: Der Name jedes Verkäufers, angegeben als varchar-Datentyp mit maximal 20 Zeichen.
• Produktspezialisierung: Jedem Mitglied Ihres Vertriebsteams ist ein Produkt als Spezialgebiet zugeordnet. Sie können zwar alle Produkte Ihres Unternehmens verkaufen, ihr Schwerpunkt liegt jedoch auf dem jeweiligen Spezialgebiet. Um dies in der Tabelle darzustellen, erstellen Sie diese Spalte, die die Produkt-ID (productID) jedes Produkts enthält, auf das sich der jeweilige Mitarbeiter spezialisiert hat.

Um sicherzustellen, dass die Spalte „productSpecialty“ nur gültige Produkt-IDs enthält, wird eine Fremdschlüsselbedingung auf die Spalte angewendet, die auf die Spalte „productID“ der Tabelle „Products“ verweist. Eine Fremdschlüsselbedingung beschreibt die Beziehung zwischen zwei Tabellen, indem sie vorschreibt, dass Werte in der Spalte, auf die sie angewendet wird, auch in der referenzierten Spalte vorhanden sein müssen. In der folgenden CREATE TABLE-Anweisung legt die FOREIGN KEY-Bedingung fest, dass jeder Wert, der der Spalte „productSpecialty“ in der Tabelle „team“ hinzugefügt wird, auch in der Spalte „productID“ der Tabelle „Products“ vorhanden sein muss.

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, enthält die Verkaufsdaten 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, dargestellt als Integer-Datentyp. Diese Spalte verfügt außerdem über eine UNIQUE-Einschränkung, sodass sie als Primärschlüssel der Verkaufstabelle fungieren kann.
• Menge: Die Anzahl der verkauften Einheiten jedes Produkts wird mit dem Datentyp int ausgedrückt.
• Produkt-ID: Die Identifikationsnummer des verkauften Produkts, ausgedrückt als int.
• Verkäufer: Identifikationsnummer des Mitarbeiters, der den Verkauf getätigt hat.

Wie bei der Spalte „productSpecialty“ aus der Tabelle „team“ entscheiden Sie sich, FOREIGN KEY-Constraints sowohl auf die Spalte „productID“ als auch auf die Spalte „salesperson“ anzuwenden. Dadurch wird sichergestellt, dass diese Spalten nur Werte enthalten, die in der Spalte „productID“ der Tabelle „products“ bzw. in der Spalte „empID“ der Tabelle „team“ vorhanden sind.

Erstellen Sie eine Tabelle namens „Umsatz“ 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)
);

Laden Sie anschließend die Tabelle „Products“ mit Beispieldaten, indem Sie die folgende INSERT INTO-Operation 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);

Laden Sie anschließend die Teamtabelle mit Beispieldaten:

INSERT INTO team
VALUES
(1, 'Florence', 1),
(2, 'Mary', 4),
(3, 'Diana', 3),
(4, 'Betty', 2);

Laden Sie die Verkaufstabelle ebenfalls 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);

Stellen Sie sich abschließend vor, Ihr Unternehmen tätigt einige Verkäufe, ohne dass jemand aus Ihrem Vertriebsteam daran beteiligt ist. Um diese Verkäufe zu erfassen, führen Sie die folgende Operation aus, um der Tabelle „Verkäufe“ drei Zeilen hinzuzufügen, die keinen Wert in der Spalte „Verkäufer“ enthalten:

INSERT INTO sales (saleID, quantity, productID)
VALUES
(6, 1, 5),
(7, 3, 1),
(8, 4, 5);

Damit sind Sie bereit, dem Rest der Anleitung zu folgen und zu lernen, wie man Tabellen in SQL miteinander verknüpft.

Die Syntax der JOIN-Operation verstehen

JOIN-Klauseln können in verschiedenen SQL-Anweisungen verwendet werden, darunter UPDATE- und DELETE-Operationen. Zur Veranschaulichung verwenden die Beispiele in diesem Leitfaden jedoch SELECT-Abfragen, um die Funktionsweise von JOIN-Klauseln zu demonstrieren.

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. Da JOIN-Klauseln die Inhalte mehrerer Tabellen vergleichen, gibt diese Beispielsyntax an, aus welcher Tabelle jede Spalte ausgewählt werden soll. Dazu wird dem Spaltennamen der Tabellenname und ein Punkt vorangestellt. Dies wird als vollqualifizierter Spaltenverweis bezeichnet.

Sie können vollqualifizierte Spaltenreferenzen wie diese in jeder Operation verwenden. Technisch gesehen ist dies jedoch nur dann notwendig, wenn zwei Spalten aus verschiedenen Tabellen denselben Namen haben. Es empfiehlt sich jedoch, sie bei der Arbeit mit mehreren Tabellen zu verwenden, da sie die Lesbarkeit und das Verständnis von JOIN-Operationen verbessern.

Nach SELECT folgt die FROM-Klausel. In jeder Abfrage definiert die FROM-Klausel den Datensatz, der durchsucht werden soll, um die gewünschten Daten zu erhalten. Der einzige Unterschied besteht darin, dass die FROM-Klausel aus zwei Tabellen besteht, die durch das Schlüsselwort JOIN getrennt sind. Eine hilfreiche Methode zum Schreiben von Abfragen ist, sich zu merken, welche Spalten aus welcher Tabelle zurückgegeben werden sollen.

Anschließend folgt die ON-Klausel, die anhand einer Suchbedingung festlegt, wie die Abfrage die beiden Tabellen verknüpfen soll. Eine Suchbedingung besteht aus einer oder mehreren Anweisungen oder Ausdrücken, die für eine bestimmte Bedingung den Wert «wahr», «falsch» oder «unbekannt» ergeben können. Man kann sich die JOIN-Operation so vorstellen, dass sie alle Zeilen beider Tabellen kombiniert und anschließend alle Zeilen zurückgibt, für die die Suchbedingung in der ON-Klausel den Wert “wahr” ergibt.

In einer ON-Klausel ist es üblicherweise sinnvoll, eine Suchbedingung einzufügen, die prüft, ob zwei zusammengehörige Spalten – beispielsweise der Fremdschlüssel einer Tabelle und der Primärschlüssel einer anderen Tabelle, auf die der Fremdschlüssel verweist – gleiche Werte aufweisen. Dies wird auch als Equi-Join bezeichnet.

Um zu veranschaulichen, wie equi übereinstimmende Daten aus mehreren Tabellen verknüpfen kann, führen Sie die folgende Abfrage mit den zuvor hinzugefügten Beispieldaten aus. Diese verknüpft die Tabellen „Products“ und „Team“ mithilfe einer Suchbedingung, die auf übereinstimmende Werte in den jeweiligen Spalten „productID“ und „productSpecialty“ prüft. Anschließend werden der Name jedes Vertriebsteammitglieds, der Name jedes Produkts, auf das es spezialisiert ist, und der Preis dieser Produkte zurückgegeben.

SELECT team.empName, products.productName, products.price
FROM products JOIN team
ON products.productID = team.productSpecialty

Output
+----------+-------------+-------+
| 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)

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 |
+-----------+-------------+-------+

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 | | | |
+-----------+-------------+-------+-------+----------+------------------+

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)

SELECT table1.column1, table2.column2
FROM table1 JOIN table2
USING (related_column);

SELECT sales.saleID, sales.quantity, products.productName, products.price
FROM sales JOIN products
USING (productID)
ORDER BY saleID;

Output
+--------+----------+-------------+-------+
| 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)

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;

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;

Output
+--------+----------+-------------+-----------------------------------+
| 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)

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName 
FROM sales JOIN team
ON sales.salesperson = team.empID;

Output
+--------+----------+-------------+----------+
| 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)

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales LEFT OUTER JOIN team
ON sales.salesperson = team.empID;

Output
+--------+----------+-------------+----------+
| 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)

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales RIGHT JOIN team
ON sales.salesperson = team.empID;

Output
+--------+----------+-------------+----------+
| 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)

SELECT t1.column1, t2.column2
FROM table1 AS t1 JOIN table2 AS t2
ON t1.related_column = t2.related_column;

SELECT S.saleID, S.quantity,
P.productName,
(P.price * S.quantity) AS revenue 
FROM sales AS S JOIN products AS P
USING (productID);

Output
+--------+----------+-------------+---------+
| 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)

SELECT S.saleID, S.quantity, P.productName, (P.price * S.quantity) revenue 
FROM sales S JOIN products P
USING (productID);