Level 10: LEFT JOIN und mehrere Joins

Lektion

JOIN lässt die Zeilen ohne Partner verschwinden. Dabei sind gerade sie oft wichtig: die Kunden, die nie bestellt haben, die Bücher, die nie ausgeliehen wurden. LEFT JOIN behält sie.

LEFT JOIN behält alle Zeilen der linken Tabelle, der im FROM geschriebenen. Hat eine Zeile einen Partner, erhält man dasselbe wie mit JOIN. Hat sie keinen, bleibt sie trotzdem erhalten, und alle Spalten der rechten Tabelle sind NULL. FROM directors d LEFT JOIN movies m … behält so Sara Diaz, mit einem Titel NULL.

Um die „Waisen“ zu finden, macht man ein LEFT JOIN und dann WHERE rechts.id IS NULL: Übrig bleiben nur die linken Zeilen ohne Partner. Zum Zählen verwende COUNT(rechts.id), das für eine Zeile ohne Partner 0 ergibt; COUNT(*) würde 1 zählen, denn die Zeile existiert im Ergebnis ja.

Klassische Falle: eine Bedingung auf die rechte Tabelle im WHERE. Das WHERE wird nach dem Join ausgeführt; für eine Zeile ohne Partner ist die rechte Spalte NULL, der Test schlägt fehl, und die Zeile verschwindet, wie bei einem gewöhnlichen JOIN. Um die rechte Tabelle zu filtern, ohne linke Zeilen zu verlieren, setze die Bedingung ins ON: LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'shipped'. Die Herausforderung 10.5 lässt dich auf diese Falle stoßen.

Man kann beliebig viele Joins aneinanderreihen, jeden mit seinem ON. Eine Tabelle kann auch mit sich selbst verbunden werden: Das ist ein Self-Join. In staff verweist manager_id auf die id eines anderen Angestellten. FROM staff e LEFT JOIN staff m ON m.id = e.manager_id liest die Tabelle zweimal unter zwei Aliasen: e für den Angestellten, m für seine Führungskraft. m.name liefert dann den Namen der Führungskraft, und das LEFT JOIN behält Alice, die keine hat.

Syntax

SELECT …
FROM a
LEFT JOIN b ON b.a_id = a.id
JOIN c ON c.id = b.c_id;

Kommentiertes Beispiel

SELECT d.name, m.title
FROM directors d
LEFT JOIN movies m ON m.director_id = d.id;

Alle Regisseure werden aufgelistet. Sara Diaz, die keinen Film hat, erscheint mit leerem Titel (NULL).

Ergebnis des Beispiels

Das Wichtigste

• LEFT JOIN behält die ganze linke Tabelle.
• LEFT JOIN + IS NULL findet die „Waisen“.
• Ein Self-Join verwendet zwei Aliase für dieselbe Tabelle.

Häufige Fallen

• Ein WHERE auf eine Spalte der rechten Tabelle kann die Wirkung des LEFT JOIN aufheben.
• COUNT(*) zählt 1, auch wenn es keine Entsprechung gibt: Verwende COUNT(spalte_rechts).

Weiterführend

RIGHT JOIN behält die ganze rechte Tabelle und FULL JOIN beide Seiten (in SQLite seit Version 3.39 verfügbar). CROSS JOIN verbindet jede Zeile mit allen Zeilen der anderen Tabelle: Das ist das kartesische Produkt, nützlich, um alle Kombinationen zu erzeugen.

Die 5 Übungen des Levels

1. Geführt · Zeige den Namen jedes Künstlers und seine Zahl an Songs, auch für Künstler ohne Song (0). (Tabellen: artists, songs)
2. Training · Zeige den Namen der Mitglieder, die nie ein Buch ausgeliehen haben. (Tabellen: members, loans)
3. Training · Zeige für jede Ausleihe den Namen des Mitglieds, den Buchtitel und das Ausleihdatum. (Tabellen: loans, members, books)
4. Training · Zeige den Namen jedes Mitarbeiters und den Namen seiner Führungskraft (leer für die Person ohne Führungskraft). (Tabelle: staff)
5. Herausforderung · Zeige für jeden Kunden seinen Namen und die Anzahl seiner nicht versandten Bestellungen, also derjenigen, deren Status nicht 'shipped' ist (not_shipped). Kunden ohne solche Bestellungen müssen mit 0 erscheinen. (Tabellen: customers, orders)

In SpeedQL wird jede Abfrage sofort geprüft: Ihr Ergebnis wird mit dem erwarteten verglichen, dann läuft die Abfrage noch einmal auf einer versteckten Kontrolldatenbank. Zu jeder Übung gibt es schriftliche Hinweise, die du nur bei Bedarf aufrufst.