SQL-JOIN-Typen Erklärt mit Beispielen

Ein falsch geschriebener JOIN wirft keinen Fehler. Er läuft durch, liefert ein Ergebnis, und die Zeilen sind falsch: fehlende Kunden, aufgeblasene Summen, unsichtbare verwaiste Datensätze. Meistens hat die Abfrage genau das bekommen, was sie verlangt hat, und der Autor hat den falschen Join-Typ gewählt oder an der falschen Stelle gefiltert.

Ein Join kombiniert Zeilen aus zwei Tabellen anhand einer Bedingung, die Sie schreiben. Welche Zeilen übrig bleiben, hängt vom Join-Typ ab, und die sechs Typen unten liefern sechs unterschiedliche Antworten aus denselben zwei Tabellen.

customers enthält fünf Personen. Drei von ihnen wurden von einem anderen Kunden empfohlen, erfasst in referred_by:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        TEXT NOT NULL,
  referred_by INT REFERENCES customers(customer_id)
);

INSERT INTO customers (customer_id, name, referred_by) VALUES
  (1, 'Alice', NULL),
  (2, 'Bob',   NULL),
  (3, 'Carol', 1),
  (4, 'Dave',  1),
  (5, 'Erin',  2);

orders enthält sechs Bestellungen. Fünf verweisen auf einen echten Kunden. Die sechste, 106, verweist auf Kunde 99, der nach der Bestellung gelöscht wurde, sodass die Bestellung niemandem mehr entspricht:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount      NUMERIC(10,2) NOT NULL
);

INSERT INTO orders (order_id, customer_id, amount) VALUES
  (101, 1, 50.00),
  (102, 1, 20.00),
  (103, 2, 75.00),
  (104, 4, 30.00),
  (105, 4, 15.00),
  (106, 99, 10.00);

Am einfachsten folgen Sie den Beispielen mit einem Postgres-Container, den Sie wie in Was Ist Docker Compose und Wie Wird Es Verwendet beschrieben aufsetzen. Die Joins sind Standard-SQL. Die zwei Stellen, an denen sich MySQL anders verhält, sind unten vermerkt.

INNER JOIN: nur die Zeilen, die auf beiden Seiten übereinstimmen

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;

Fünf Zeilen. Carol und Erin haben keine Bestellungen, also fallen sie weg. Bestellung 106 hat keinen Kunden, also fällt sie auch weg. JOIN und INNER JOIN bedeuten dasselbe. INNER ist optional, und die meisten lassen es weg.

Bei fünf Zeilen merken Sie keine Verzögerung. Bei einer echten orders-Tabelle braucht orders.customer_id einen Index, sonst durchsucht jeder Join, der diese Spalte nutzt, die gesamte Tabelle. Wie Datenbankindizes Funktionieren und Wann Sie Nutzen erklärt, wann sich dieser Index lohnt.

LEFT JOIN: jede Zeile von links, mit oder ohne Treffer

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;

Jeder Kunde ist dabei. Carol und Erin bekommen NULL in den Bestellspalten. Das ist der Join, den Sie am häufigsten schreiben: “alle X, zusammen mit dem, was sie jeweils an Y haben”.

RIGHT JOIN: jede Zeile von rechts, mit oder ohne Treffer

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Bestellung 106 taucht wieder auf, mit NULL statt des Namens. Das ist die verwaiste Zeile, die der Inner Join versteckt hat, und ein Right Join (oder der entsprechende Left Join) findet sie absichtlich.

Einen RIGHT JOIN sehen Sie in echtem Code selten. a RIGHT JOIN b liefert dieselben Zeilen wie b LEFT JOIN a, deshalb verwenden die meisten Teams überall LEFT JOIN und tauschen stattdessen die Tabellenreihenfolge. Einen RIGHT JOIN so umzuschreiben, ist eine Frage der Konsistenz. Das Original ist nicht falsch.

FULL OUTER JOIN: alles von beiden Seiten

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name NULLS LAST, o.order_id;

Acht Zeilen: jeder Treffer, plus die Kunden ohne Treffer, plus die Bestellung ohne Treffer. Das macht ihn zum richtigen Join, wenn Sie alles sehen wollen, was auf einer der beiden Seiten nicht zusammenpasst. Sonst brauchen Sie ihn selten.

MySQL hat kein FULL OUTER JOIN und akzeptiert auch kein NULLS LAST. Um dort dieselben Zeilen zu erhalten, nehmen Sie den LEFT JOIN und hängen per UNION ALL einen RIGHT JOIN an, gefiltert mit WHERE c.customer_id IS NULL, damit die bereits getroffenen Zeilen nicht doppelt auftauchen. Ein einfaches UNION der beiden Joins funktioniert auch, kostet aber einen Deduplizierungsschritt und verschmilzt zudem Zeilen, die wirklich identisch sind.

CROSS JOIN: jede Zeile mit jeder Zeile kombiniert

1
2
3

SELECT c.name, o.order_id
FROM customers c
CROSS JOIN orders o;

Fünf Kunden mal sechs Bestellungen: dreißig Zeilen, ganz ohne Join-Bedingung. Die meisten Cross Joins in Produktion sind Unfälle. Jemand hat FROM customers, orders geschrieben und das WHERE vergessen, das sie verbinden sollte. Einen Cross Join setzen Sie absichtlich ein, um Kombinationen zu erzeugen, etwa jede Größe mit jeder Farbe oder jeden Tag eines Zeitraums mit jeder Filiale, bevor Sie die echten Zahlen einfüllen.

Wie funktioniert ein Self-Join?

customers.referred_by verweist auf eine andere Zeile derselben Tabelle. Um anhand der Namen zu zeigen, wer wen empfohlen hat, verbinden Sie die Tabelle mit sich selbst, unter einem zweiten Alias:

1
2
3
4

SELECT c.name AS customer, r.name AS referred_by
FROM customers c
LEFT JOIN customers r ON c.referred_by = r.customer_id
ORDER BY c.name;

Der Join funktioniert genau wie zuvor. customers c und customers r sind zwei Aliase, und der Planer behandelt sie als zwei getrennte Tabellen, die zufällig dieselben Zeilen enthalten. Es ist absichtlich ein LEFT JOIN, damit Alice und Bob, die von niemandem empfohlen wurden, im Ergebnis bleiben. Jede Tabelle, in der eine Zeile auf eine andere Zeile derselben Tabelle verweist, funktioniert so: Mitarbeiter und Vorgesetzte, Kategorien und übergeordnete Kategorien, Kommentare und Antworten.

Warum verwandelt eine WHERE-Klausel einen LEFT JOIN in einen INNER JOIN?

Sie wollen jeden Kunden, mit seinen Bestellungen über 25 $, sofern er welche hat:

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 25;

Carol und Erin sind weg. WHERE läuft nach dem Join, und für sie ist o.amount gleich NULL. NULL > 25 ist weder wahr noch falsch. Es ist unbekannt, und WHERE behält nur Zeilen, die wahr sind. Der LEFT JOIN hat sie behalten, und der Filter hat sie entfernt.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- Jeden Kunden behalten; nur filtern, welche seiner Bestellungen zählen
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.amount > 25;

-- Behält Kunden ganz ohne Bestellungen, aber nicht Kunden, deren Bestellungen alle <= 25 sind
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 25 OR o.amount IS NULL;

Nehmen Sie die erste. Die Bedingung in ON zu setzen, entscheidet, welche Bestellungen überhaupt treffen dürfen, und jeder Kunde bekommt trotzdem mindestens eine Zeile. Die zweite Version lässt nur Zeilen durch, bei denen der Join nichts gefunden hat. Ein Kunde, dessen einzige Bestellung 20 $ war, bekommt eine Zeile mit amount = 20.00, die keine der beiden Bedingungen erfüllt, und dieser Kunde verschwindet erneut. Bei diesen Daten behalten zufällig beide Abfragen alle fünf Kunden, und so rutscht die zweite durch die Tests.

Warum liefert SUM nach dem Join zweier Tabellen die falsche Summe?

Fügen Sie eine dritte Tabelle hinzu, payments, bei der eine Bestellung in mehreren Raten bezahlt werden kann:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  order_id   INT NOT NULL,
  amount     NUMERIC(10,2) NOT NULL
);

INSERT INTO payments (payment_id, order_id, amount) VALUES
  (1, 101, 25.00),
  (2, 101, 25.00),
  (3, 103, 75.00);

Bestellung 101 beträgt 50 $, bezahlt in zwei Raten von je 25 $. Verbinden Sie jetzt alle drei Tabellen und summieren Sie, was jeder Kunde bestellt hat:

1
2
3
4
5

SELECT c.name, SUM(o.amount) AS total_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY c.name;

Alices tatsächliche Summe ist 70 $ (50.00 + 20.00). Zwei Dinge gingen schief. Bestellung 101 trifft auf zwei Zahlungen, also liefert der Join diese Bestellzeile zweimal, und SUM(o.amount) addiert ihre 50 $ zweimal. Bestellung 102 hat keine Zahlung, also entfernt der Inner Join mit payments sie. Für Alice ergibt das 50 + 50 + 0 = 100. Dave hat überhaupt keine Zahlung, also erscheint er nicht im Ergebnis.

Die Regel: Wenn Sie eine Spalte aggregieren, verbinden Sie keine Tabellen, die die Aggregation nicht braucht. Jeder Eins-zu-viele-Join kann Zeilen vervielfachen oder entfernen. Wenn Sie nur die Bestellsummen wollen, lassen Sie payments weg:

1
2
3
4

SELECT c.name, SUM(o.amount) AS total_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name;

Brauchen Sie die Zahlungssummen im selben Report, wenden Sie zuerst ein GROUP BY order_id auf payments an, in einer Subquery oder CTE. Das ergibt eine Zeile pro Bestellung, die Sie gefahrlos mit den Bestellungen verbinden können.

NOT IN gegen NOT EXISTS: Wie finden Sie Zeilen ohne Treffer?

“Kunden ohne Bestellungen” sieht nach einem Fall für NOT IN aus:

1
2

SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Das funktioniert hier nur, weil orders.customer_id NOT NULL ist. Entfernen Sie diese Einschränkung und fügen Sie eine einzige Bestellung mit customer_id gleich NULL ein, und die Abfrage liefert null Zeilen, ohne Fehler. NOT IN wird zu einer Kette von <>-Vergleichen, verbunden mit AND. x <> NULL ist unbekannt, also macht ein einziges NULL die gesamte Bedingung für jeden Kunden unbekannt.

Zwei Muster haben dieses Problem nicht:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- LEFT JOIN + IS NULL
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- NOT EXISTS
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Beide liefern Carol und Erin, und ein NULL in orders.customer_id beeinträchtigt keines der beiden. Keines baut eine Liste zum Vergleichen auf. Beide prüfen, Kunde für Kunde, ob eine passende Bestellung existiert. Verwenden Sie standardmäßig NOT EXISTS. Verwenden Sie LEFT JOIN ... IS NULL, wenn die Abfrage die beiden Tabellen bereits verbindet, um andere Spalten zu holen.

Liefert ein Join mehr Zeilen als erwartet, suchen Sie zwischen dem Join und dem GROUP BY nach einer Eins-zu-viele-Tabelle. Liefert er weniger, suchen Sie nach einer WHERE-Bedingung auf der Tabelle, die NULL sein darf. In beiden Fällen korrigieren Sie die Abfrage rund um den Join, bevor Sie den Join-Typ ändern.

Verwandte Artikel

• Wie Datenbankindizes Funktionieren und Wann Sie Nutzen
• Was Ist Docker Compose und Wie Wird Es Verwendet
• Wie Redis-Caching funktioniert und wie Sie es nutzen