JOIN: zwei Tabellen in einer Abfrage

Kurz gesagt: JOIN … ON verbindet eine Zeile einer Tabelle mit der Zeile einer anderen Tabelle, auf die ihr Fremdschlüssel verweist, zum Beispiel eine Bestellung mit ihrem Kunden, und behält nur die Zeilen, die einen Partner haben.

Problem

Bestellungen mit Namen

Der Kurierdienst will eine Liste: Bestellnummer, Datum und Name des Kunden. Nummer und Datum stehen in orders, der Name steht in customers.

In der letzten Lektion haben wir es mit zwei Abfragen gemacht und die Nummer von Hand dazwischen weitergegeben. Bei 60 Bestellungen sind das 60 Nachschlagevorgänge. Wir brauchen eine Abfrage, die den Kunden jeder Bestellung selbst findet.

Die Idee

JOIN … ON

JOIN stellt neben jede Zeile der einen Tabelle die passende Zeile der anderen. ON sagt, welche Zeile zu welcher passt:

SQLDatenbank: Shop

SELECT orders.id, orders.ordered_at, customers.name
FROM orders
JOIN customers ON customers.id = orders.customer_id;

SELECT orders.id, orders.ordered_at, customers.name FROM orders JOIN customers ON customers.id = orders.customer_id;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Für jede Bestellung sucht die Datenbank den Kunden, dessen customers.id gleich orders.customer_id ist, und klebt beide zu einer Zeile zusammen. Deshalb stehen die Spalten mit dem Tabellennamen davor: orders.id, customers.name.

Es kommen 60 Zeilen heraus, so viele, wie es Bestellungen gibt: Jede Bestellung hat genau einen Kunden. Das heißt JOIN (oder INNER JOIN): Nur die Paare, die gefunden werden, bleiben übrig. Teodora und Yavor stehen nicht in der Liste, weil sie keine Bestellungen haben. Zu ihnen kommen wir in der nächsten Lektion zurück.

An kleinen Tabellen sieht man, wie es geschieht: vier Bestellungen und drei Kunden, und Teodora hat keine Bestellungen.

SELECT orders.id, orders.customer_id, customers.name
FROM orders
JOIN customers ON customers.id = orders.customer_id;

Schritt für Schritt, in Worten

1. 4 Bestellungen und 3 Kunden. Die customer_id der Bestellung ist die id ihres Kunden.
2. Bestellung 1 hat customer_id 2. Die Datenbank sucht den Kunden mit der id 2: Georgi Petrov. Das Paar wird zu einer Zeile.
3. Bestellung 2: customer_id 1, das ist Maria Ivanova.
4. Bestellung 3 gehört wieder zu Kunde 1: Maria Ivanova steht zum zweiten Mal im Ergebnis.
5. Bestellung 4 gehört wieder zu Kunde 2: Georgi Petrov steht zum zweiten Mal im Ergebnis.
6. Teodora Genova (id 3) ist die customer_id keiner einzigen Bestellung. Es gibt kein Paar, deshalb steht sie nicht im Ergebnis.
7. 4 Zeilen, so viele, wie es Bestellungen gibt. JOIN behält nur die Paare, die gefunden werden.

Dieselbe Abfrage auf diesen kleinen Tabellen. Führe sie aus und vergleiche mit dem letzten Schritt:

SQLleere Datenbank

SELECT orders.id, orders.customer_id, customers.name
FROM orders
JOIN customers ON customers.id = orders.customer_id;

SELECT orders.id, orders.customer_id, customers.name FROM orders JOIN customers ON customers.id = orders.customer_id;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Die Idee

Kurze Namen

orders.ordered_at und customers.name werden lang. Einer Tabelle kann man für die Abfrage einen kurzen Namen geben: Du schreibst ihn in FROM und JOIN hinter den Tabellennamen.

SQLDatenbank: Shop

SELECT o.id, o.ordered_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

SELECT o.id, o.ordered_at, c.name FROM orders o JOIN customers c ON c.id = o.customer_id;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Dieselbe Abfrage, dasselbe Ergebnis. o ist orders, c ist customers. Der kurze Name gilt nur in dieser Abfrage. Du kannst auch FROM orders AS o schreiben.

Probier es aus

Die neuen Bestellungen

Zeige die Nummer der neuen Bestellungen (Status new) mit Name und E-Mail des Kunden, damit eine Bestätigung gesendet werden kann.

SQLDatenbank: Shop

SELECT id, customer_id FROM orders WHERE status = 'new';

SELECT id, customer_id FROM orders WHERE status = 'new';

Prüfen▶ Ausführen

Sieh es dir an

„ambiguous column name“

Hier eine Abfrage ohne Tabellennamen. Führe sie aus:

SQLDatenbank: Shop

SELECT id, name, ordered_at
FROM orders
JOIN customers ON customer_id = id;

SELECT id, name, ordered_at FROM orders JOIN customers ON customer_id = id;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Sowohl orders als auch customers haben eine Spalte id. Nach einem JOIN liegen die Spalten beider zusammen, und die Datenbank weiß nicht, welche id du meinst. „Ambiguous“ heißt mehrdeutig: Man kann es auf zwei Arten lesen. Die Spalte name gibt es nur in customers, darüber beschwert sie sich nicht.

Die Lösung: sagen, aus welcher Tabelle die Spalte stammt:

SQLDatenbank: Shop

SELECT o.id, c.name, o.ordered_at
FROM orders o
JOIN customers c ON c.id = o.customer_id;

SELECT o.id, c.name, o.ordered_at FROM orders o JOIN customers c ON c.id = o.customer_id;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Eine gute Gewohnheit ist, in einer Abfrage mit JOIN vor jede Spalte den kurzen Namen zu setzen, auch wenn es nicht verlangt wird. Wer sie liest, sieht sofort, woher jede Spalte kommt.

Die Idee

JOIN, WHERE und ORDER BY

Nach einem JOIN funktioniert alles, was du kennst, wie bisher, und Bedingungen dürfen Spalten beider Tabellen benutzen. Die Bestellungen der Kunden aus Varna, nach Datum:

SQLDatenbank: Shop

SELECT o.id, o.ordered_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.city = 'Varna'
ORDER BY o.ordered_at;

SELECT o.id, o.ordered_at, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.city = 'Varna' ORDER BY o.ordered_at;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Die Stadt steht in customers, das Datum in orders. Die Reihenfolge der Teile ist: SELECT … FROM … JOIN … ON … WHERE … ORDER BY.

Probier es aus

Was in Bestellung 60 steckt

Zeige für jede Position von Bestellung 60 den Produktnamen, die Menge und den Preis.

SQLDatenbank: Shop

SELECT product_id, quantity, unit_price FROM order_items WHERE order_id = 60;

SELECT product_id, quantity, unit_price FROM order_items WHERE order_id = 60;

Prüfen▶ Ausführen

Übung

Zwei Aufgaben

1. Der Shop untersucht, warum Bestellungen storniert werden. Zeige die Nummer der stornierten Bestellungen (cancelled) mit Namen und Stadt des Kunden.

Die stornierten Bestellungen mit dem Kunden und seiner Stadt:

SQLDatenbank: Shop

Prüfen▶ Ausführen

2. Das Qualitätsteam will die Bewertungen mit der Note 3 oder niedriger zusammen mit dem Produktnamen, damit es weiß, was zu prüfen ist.

Die niedrigen Noten: das Produkt, die Note und der Text der Bewertung:

SQLDatenbank: Shop

Prüfen▶ Ausführen

Genauer, für Neugierige

Du wirst auch der älteren Form begegnen: Die Tabellen in FROM durch Komma getrennt und die Bedingung für die Paare in WHERE.

SQLDatenbank: Shop

SELECT o.id, c.name
FROM orders o, customers c
WHERE c.id = o.customer_id;

SELECT o.id, c.name FROM orders o, customers c WHERE c.id = o.customer_id;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Das Ergebnis ist dasselbe. Die Form JOIN … ON hält die Verknüpfungsbedingung getrennt von den Filterbedingungen. Deshalb schreibt der Kurs JOIN.

Test

Teste dich

1. Was sagt ON c.id = o.customer_id?

Welche Spalten angezeigt werdenWie die Zeilen sortiert werdenWelche Zeile von customers zu welcher Zeile von orders gehört

2. Warum ergibt SELECT id FROM orders JOIN customers … den Fehler „ambiguous column name: id“?

Weil id ein reserviertes Wort istWeil id nach einem JOIN nicht angezeigt werden kannWeil beide Tabellen eine Spalte id haben und die Datenbank nicht weiß, welche du willst

3. Was ist o in FROM orders o JOIN customers c?

Eine neue Kopie der TabelleEine SpalteEin kurzer Name für orders in dieser Abfrage

4. Wo steht WHERE in einer Abfrage mit JOIN?

Am Ende, nach ORDER BYNach JOIN … ON und vor ORDER BYVor JOIN

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: WHERE: Zeilen filtern · NULL: kein Wert · ORDER BY, LIMIT und DISTINCT · Primärschlüssel und Fremdschlüssel
• Geht weiter in: LEFT JOIN: auch Zeilen ohne Partner · Viele Tabellen und eine Tabelle mit sich selbst verbunden · UNION, INTERSECT, EXCEPT und CROSS JOIN · Unterabfragen
• Aufgaben zum Thema: Tabellen verbinden