Tabellen verbinden: JOIN

Der einfache Join

Bestellungen speichern customer_id, Kunden den Namen. Um beides zusammen zu sehen, sagen Sie der Datenbank, wie die zwei Tabellen zueinander passen.

SELECT o.order_number,
       o.ordered_at,
       o.total,
       c.first_name || ' ' || c.last_name AS customer,
       c.country
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.ordered_at DESC
LIMIT 20;

Drei Dinge verdienen einen Namen. JOIN allein bedeutet INNER JOIN. ON c.id = o.customer_id ist die Join-Bedingung – wie eine Zeile links einer Zeile rechts zugeordnet wird. Und orders o gibt der Tabelle einen kurzen Alias, wodurch Sie o.total und c.country schreiben können, ohne den vollen Tabellennamen zu wiederholen.

Aliase sind spätestens dann nicht mehr optional, wenn zwei Tabellen eine gleichnamige Spalte haben. Beide Tabellen hier haben eine id; unqualifiziert wäre id mehrdeutig, und die Datenbank würde die Abfrage ablehnen.

Mehr als zwei Tabellen

Joins lassen sich aneinanderhängen. Die Produkte einer Bestellung sind zwei Schritte entfernt: orders → order_items → products.

SELECT o.order_number,
       p.name AS product,
       oi.quantity,
       oi.unit_price
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'delivered'
ORDER BY o.ordered_at DESC
LIMIT 25;

Jedes JOIN hängt eine weitere Tabelle an das an, was Sie bisher haben, ein Join über vier Tabellen ist also dasselbe mit einer Zeile mehr. Von oben nach unten gelesen: "Bestellungen, mit ihren Positionen, mit dem Produkt je Position".

Zeilen ohne Gegenstück behalten

Ein Inner Join wirft alles ohne Partner weg. Ein Kunde, der nie bestellt hat, verschwindet einfach aus dem Ergebnis. LEFT JOIN behält jede Zeile der linken Tabelle und füllt die rechte Seite mit NULL, wo es nichts zuzuordnen gab.

SELECT c.id,
       c.first_name,
       c.last_name,
       o.order_number,
       o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id
LIMIT 25;

Damit wird die Abfrage "finde, was fehlt" möglich: alles behalten und dann nur die Zeilen behalten, bei denen die Zuordnung fehlschlug.

-- The 95 customers who have never ordered anything.
SELECT c.id, c.first_name, c.last_name, c.country
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id
LIMIT 25;

WHERE o.id IS NULL liest sich zunächst seltsam – orders.id ist ein Primärschlüssel und kann in der Tabelle nie null sein. In diesem Ergebnis kann es das aber, weil der LEFT JOIN diese NULLs für die nicht zugeordneten Zeilen erfunden hat. Diese Zeilen sind die 95 Kunden, die nie bestellt haben.

ON oder WHERE: nicht dieselbe Frage

Hier ist die Falle. Beide Abfragen erwähnen status = 'cancelled'. Nur eine davon ist in der Praxis ein LEFT JOIN.

-- The condition is part of the join: every customer is kept,
-- and only cancelled orders are attached to them.
SELECT c.id, c.first_name, o.order_number, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'cancelled'
ORDER BY c.id
LIMIT 25;

Die Bedingung steht in ON, ist also Teil der Zuordnung: Jeder Kunde bleibt erhalten, und eine stornierte Bestellung wird angehängt, wo es eine gibt. Kunden ohne stornierte Bestellung erscheinen weiterhin, mit NULLs.

-- The same condition in WHERE runs after the join and throws away
-- every row where it is not true — including the NULLs the LEFT JOIN made.
SELECT c.id, c.first_name, o.order_number, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'cancelled'
ORDER BY c.id
LIMIT 25;

Hier steht die Bedingung in WHERE, das nach dem Join läuft und jede Zeile wegwirft, für die sie nicht wahr ist. Die NULL-Zeilen, die der LEFT JOIN gerade erzeugt hat, fallen durch diesen Test – NULL = 'cancelled' ist nicht wahr – und werden alle verworfen. Sie haben einen LEFT JOIN geschrieben und einen INNER JOIN bekommen.

Die Regel ist kurz: Eine Bedingung auf die rechte Tabelle eines LEFT JOIN gehört in ON. Eine Bedingung auf die linke Tabelle gehört in WHERE. Bei einem INNER JOIN macht es tatsächlich keinen Unterschied, weshalb man sich die Gewohnheit leicht falsch angewöhnt.

Eine Tabelle mit sich selbst verbinden

Nirgends steht, dass die beiden Seiten verschiedene Tabellen sein müssen. Mitarbeiter haben eine manager_id, die auf einen anderen Mitarbeiter zeigt, also verbindet sich die Tabelle unter zwei verschiedenen Aliasen mit sich selbst.

SELECT e.first_name || ' ' || e.last_name AS employee,
       e.position,
       m.first_name || ' ' || m.last_name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY manager, employee
LIMIT 25;

e und m sind dieselbe Tabelle, zweimal betrachtet, und erst die Aliase machen das möglich. Es ist ein LEFT JOIN, weil die zehn Personen an der Spitze keine Führungskraft haben und ein Inner Join sie still weglassen würde.

Was Sie jetzt können

Sie können Fragen über das ganze Schema stellen. Was noch nicht geht, ist die Antwort zusammenzufassen: Bestellungen zählen, Umsatz summieren und Durchschnitte bilden folgen in der nächsten Lektion.