Arbeiten mit JOIN
Dieses Kapitel enthält die Einführung in die Variante mit JOIN, mit der mehrere Tabellen verknüpft werden können. Dazu wird jede der in Betracht kommenden Tabellen in einer JOIN-Klausel aufgeführt; der ON-Parameter enthält die Verknüpfungsbedingung. Die WHERE-Klausel enthält „nur“ die Auswahlbedingungen.
Die Syntax von JOIN
BearbeitenUm Tabellen sinnvoll miteinander zu verknüpfen (= verbinden, engl. join), wurde die JOIN-Klausel für den SELECT-Befehl mit folgender Syntax eingeführt.
SELECT <spaltenliste> FROM <haupttabelle> [<join-typ>] JOIN <verknüpfte tabelle> ON <bedingung>
Als <join-typ> stehen zur Verfügung:
- [INNER] JOIN, auch Equi-Join genannt, ist eine Verknüpfung innerhalb zweier Tabellen, d. h. ein Teil des kartesischen Produkts, bei dem ein Wert in beiden Tabellen vorhanden ist. INNER JOIN ist der Inhalt dieses Kapitels.
- OUTER JOIN bezeichnet Verknüpfungen, bei denen auch Datensätze geliefert werden, für die eine Vergleichsbedingung nicht erfüllt ist.
- LEFT JOIN, RIGHT JOIN, FULL JOIN bezeichnen Spezialfälle von OUTER JOIN, je nachdem in welcher Tabelle ein gesuchter Wert fehlt.
OUTER JOIN wird im nächsten Kapitel behandelt.
Einige Sonderfälle und Ergänzungen zu JOIN werden im Kapitel Mehr zu JOIN behandelt.
Als <bedingung> wird normalerweise nur eine Übereinstimmung (also eine Gleichheit) zwischen zwei Tabellen geprüft, auch wenn jede Kombination von Bedingungen erlaubt ist. Genauer: es geht um die Gleichheit von Werten je einer Spalte in zwei Tabellen. (Zwei Beispiele für andere Übereinstimmungen lernen Sie in „Mehr zu JOIN“ kennen.)
Auch mehrere Verknüpfungen sind möglich, entweder direkt hintereinander:
SELECT <spaltenliste> FROM <haupttabelle> [<join-typ>] JOIN <zusatztabelle1> ON <bedingung1> [<join-typ>] JOIN <zusatztabelle2> ON <bedingung2> [<join-typ>] JOIN <zusatztabelle3> ON <bedingung3>
oder durch Klammern gegliedert:
SELECT <spaltenliste>
FROM <haupttabelle>
[<join-typ>] JOIN
( <zusatztabelle1>
[<join-typ>] JOIN
( <zusatztabelle2>
[<join-typ>] JOIN <zusatztabelle3> ON <bedingung3>
) ON <bedingung2>
) ON <bedingung1>
Bitte beachten Sie dabei genau, wo und wie die Klammern und die dazugehörigen ON-Bedingungen gesetzt werden. Beide Varianten können unterschiedliche Ergebnisse liefern – abhängig vom JOIN-Typ und dem Zusammenhang zwischen den Tabellen.
Alle diese Möglichkeiten werden in den nächsten Abschnitten und Kapiteln genauer erläutert.
INNER JOIN von zwei Tabellen
Bearbeiten
Das Beispiel „alle Mitarbeiter mit den zugehörigen Dienstwagen“ aus dem vorigen Kapitel benötigt nur geringe Änderungen.
select mi.Personalnummer as MitNr,
mi.Name, mi.Vorname,
dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
from Mitarbeiter mi
join Dienstwagen dw on dw.Mitarbeiter_ID = mi.ID
ORDER BY MitNr;
Das Ergebnis dieser Abfrage ist identisch mit der Liste im vorigen Kapitel; wir verzichten deshalb auf die erneute Ausgabe.
Die zweite Tabelle wird in die JOIN-Klausel verschoben, die Verknüpfungsbedingung in den ON-Parameter – fertig.
WHERE-Klausel bei JOINs
BearbeitenEine solche Abfrage kann wie üblich durch eine WHERE-Klausel eingeschränkt werden. Eine Suchbedingung auf die verknüpfte Tabelle Dienstwagen kann wahlweise in der WHERE-Klausel oder in der JOIN-Klausel stehen. In den beiden folgenden Beispielen geht es nur um die Dienstwagen von Mercedes. Die Information, welche Typen zu Mercedes gehören, kommt über eine Unterabfrage, die ebenfalls einen JOIN verwendet und die in Klammern gesetzt ist.
Suche die Dienstwagen vom Typ Mercedes.
Zulässig, aber nicht so schön, weil Verknüpfungsbedingung und Auswahlbedingung vermischt werdenselect mi.Personalnummer as MitNr,
mi.Name, mi.Vorname,
dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
from Mitarbeiter mi
join Dienstwagen dw
on mi.ID = dw.Mitarbeiter_ID
and dw.Fahrzeugtyp_ID in ( SELECT ft.ID
from Fahrzeugtyp ft
join Fahrzeughersteller fh
on ft.Hersteller_ID = fh.ID
and fh.Name = 'Mercedes-Benz' );
Besseres Vorgehen, weil die Auswahlbedingungen als solche direkt zu erkennen sindselect mi.Personalnummer as MitNr,
mi.Name, mi.Vorname,
dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
from Mitarbeiter mi
join Dienstwagen dw
on mi.ID = dw.Mitarbeiter_ID
where dw.Fahrzeugtyp_ID in ( SELECT ft.ID
from Fahrzeugtyp ft
join Fahrzeughersteller fh
on ft.Hersteller_ID = fh.ID
where fh.Name = 'Mercedes-Benz');
Natürlich sind Einschränkungen auf beide Tabellen möglich:
Gesucht werden Mitarbeiter mit 'M' und Mercedes als Dienstwagen.
select mi.Personalnummer as MitNr,
mi.Name, mi.Vorname,
dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
from Mitarbeiter mi
join Dienstwagen dw
on mi.ID = dw.Mitarbeiter_ID
where dw.Fahrzeugtyp_ID in ( SELECT ft.ID
from Fahrzeugtyp ft
join Fahrzeughersteller fh
on ft.Hersteller_ID = fh.ID
where fh.Name = 'Mercedes-Benz')
and mi.Name like 'M%';
AusgabeMITNR NAME VORNAME ID KENNZEICHEN TYP
------ --------- ------- -- ----------- ---
10001 Müller Kurt 1 DO-WB 421 14
20001 Meyer Walter 2 DO-WB 422 14
Bei diesem Beispiel wird sofort deutlich, welche Bedingungen die Verknüpfung und welche Bedingungen die Auswahl bezeichnen. Auf diese Übersichtlichkeit sollten Sie immer achten.
Übrigens gibt es keine allgemeine Regel, was als Haupttabelle und was als verknüpfte Tabelle zu verwenden ist. In den bisherigen Beispielen können die beiden Tabellen ohne weiteres vertauscht werden:
select mi.Personalnummer as MitNr,
mi.Name, mi.Vorname,
dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
from Dienstwagen dw
join Mitarbeiter mi
on mi.ID = dw.Mitarbeiter_ID
where dw.Fahrzeugtyp_ID in ( SELECT ft.ID
from Fahrzeugtyp ft
join Fahrzeughersteller fh
on ft.Hersteller_ID = fh.ID
where fh.Name = 'Mercedes-Benz')
and mi.Name like 'M%';
Die Haupttabelle kann nach folgenden Überlegungen gewählt werden:
- Es sollte die Tabelle sein, die die „wichtigste“ bei der Abfrage ist.
- Es sollte diejenige mit den größten Einschränkungen sein; das beschleunigt die Abfrage besonders stark.
INNER JOIN mehrerer Tabellen
BearbeitenDazu nehmen wir wiederum das komplexe Beispiel aus dem vorigen Kapitel, das bei den Gruppierungen genauer besprochen wird. In diesem Fall spielt die Reihenfolge der JOIN-Klauseln eher keine Rolle, weil es sich sowieso um direkte Übereinstimmungen handelt und nur solche Datensätze benutzt werden, die es zu den betreffenden Werten tatsächlich gibt.
Gesucht wird für jeden Fahrzeughersteller (mit Angabe von ID und Name) und jedes Jahr die Summe der Schadenshöhe aus der Tabelle Schadensfall.
SELECT fh.ID AS Hersteller_ID,
fh.Name AS Hersteller_Name,
EXTRACT(YEAR FROM sf.Datum) AS Jahr,
SUM(sf.Schadenshoehe) AS Schadenssumme
FROM Schadensfall sf
JOIN Zuordnung_SF_FZ zu ON sf.ID = zu.Schadensfall_ID
JOIN Fahrzeug fz ON fz.ID = zu.Fahrzeug_ID
JOIN Fahrzeugtyp ft ON ft.ID = fz.Fahrzeugtyp_ID
JOIN Fahrzeughersteller fh ON fh.ID = ft.Hersteller_ID
GROUP BY Hersteller_ID, Hersteller_Name, Jahr
ORDER BY Jahr, Hersteller_ID;
Übrigens ist es zulässig, den „traditionellen“ Weg mit mehreren Tabellen in der FROM-Klausel und den „modernen“ Weg über JOIN zu mischen. Wenn Sie in einem Ausnahmefall wirklich so vorgehen wollen, sollten Sie erst recht genau auf die Übersichtlichkeit und den Zusammenhang der Bedingungen achten. Der Autor dieses Hinweises kann sich keine passende Situation vorstellen, aber vielleicht ist es auch einmal sinnvoll.
Zusammenfassung
BearbeitenIn diesem Kapitel lernten Sie die Verknüpfung von Tabellen über die JOIN-Klausel kennen.
- Mit einem INNER JOIN werden Datensätze abgefragt, bei denen ein Wert in je einer Spalte beider Tabellen vorhanden ist.
- In der ON-Klausel steht diese Verknüpfungsbedingung.
- In der WHERE-Klausel stehen die „normalen“ Auswahlbedingungen.
Genauso können mehrere Tabellen verknüpft werden.
Übungen
Bearbeiten
| Übung 1 | Definition von JOINs | Zur Lösung |
Welche der folgenden Aussagen sind wahr, welche falsch, welche sinnvoll?
- Der INNER JOIN liefert das kartesische Produkt zwischen den Tabellen.
- LEFT JOIN ist ein Spezialfall von OUTER JOIN.
- Für einen JOIN ist ON Fahrzeug.ID >= Versicherungsvertrag.Fahrzeug_ID eine zulässige Verknüpfungsbedingung.
- Eine Einschränkung auf die mit JOIN verknüpfte Tabelle gehört in die ON-Klausel:
... FROM Zuordnung_SF_FZ zu
JOIN Schadensfall sf
ON sf.ID = zu.Schadensfall_ID AND EXTRACT(YEAR from sf.Datum) = 2008;
| Übung 2 | Definition von JOINs | Zur Lösung |
Erläutern Sie, was am folgenden Befehl falsch oder äußerst ungünstig ist. Es handelt sich um diese Abfrage:
Gesucht sind die Schadensfälle des Jahres 2008. Zu jedem Schadensfall sind die beteiligten Fahrzeuge, der Schadensanteil sowie die Versicherungsdaten des Fahrzeugs (einschließlich Name des Halters) anzugeben.
SELECT Datum, SUBSTRING(Ort from 1 for 30) as Ort, Schadenshoehe,
zu.Schadenshoehe,
fz.Kennzeichen,
Vertragsnummer as Vertrag, Abschlussdatum, Art,
vn.Name as VN-Name, vn.Vorname as VN-Vorname
from Schadensfall sf
join Zuordnung_SF_FZ zu on ID = zu.Schadensfall_ID
join Fahrzeug fz on ID = zu.Fahrzeug_ID
join Versicherungsnehmer vn on ID = vv.Versicherungsnehmer_ID
join Versicherungsvertrag vv on vv.Fahrzeug_ID = zu.Fahrzeug_ID
where EXTRACT(YEAR from Datum) = 2008
order by Schadensfall_ID, Fahrzeug_ID;
Die folgenden Aufgaben entsprechen teilweise Aufgaben aus dem Kapitel „Einfache Tabellenverknüpfung“. Sie sollen jetzt an den passenden Stellen JOINs verwenden, anstatt die Tabellen einfach aufzulisten.
| Übung 3 | Sinnvolle Verknüpfung von Tabellen | Zur Lösung |
Erstellen Sie eine Abfrage zur Tabelle Versicherungsvertrag mit den wichtigsten Informationen (einschließlich der IDs auf andere Tabellen). Beim Versicherungsnehmer sollen dessen Name und Vorname angezeigt werden. Es werden nur Verträge ab 1990 gesucht.
| Übung 4 | Sinnvolle Verknüpfung von Tabellen | Zur Lösung |
Erweitern Sie die Abfrage von Aufgabe 3, sodass Name und Vorname des Mitarbeiters sowie das Fahrzeug-Kennzeichen eines jeden Vertrags angezeigt werden.
| Übung 5 | Sinnvolle Verknüpfung von Tabellen | Zur Lösung |
Ändern Sie die Abfrage von Aufgabe 4 so, dass die ausgewählten Zeilen den folgenden Bedingungen entsprechen:
- Es geht ausschließlich um Eigene Kunden.
- Vollkasko-Verträge sollen immer angezeigt werden, ebenso Fahrzeuge aus dem Kreis Recklinghausen 'RE'.
- Teilkasko-Verträge sollen angezeigt werden, wenn sie nach 1990 abgeschlossen wurden.
- Haftpflicht-Verträge sollen angezeigt werden, wenn sie nach 1985 abgeschlossen wurden.
| Lösung zu Übung 1 | Definition von JOINs | Zur Übung |
- Falsch; es liefert einen Teil des kartesischen Produkts, der durch die ON-Bedingung bestimmt wird.
- Richtig.
- Diese Bedingung ist zulässig, aber nicht sinnvoll. JOIN-ON passt in der Regel nur für Gleichheiten.
- Diese Bedingung ist zulässig. Besser ist es aber, eine Einschränkung der Auswahl in die WHERE-Klausel zu setzen.
| Lösung zu Übung 2 | Definition von JOINs | Zur Übung |
Richtig ist beispielsweise die folgende Version. Als Haupttabelle wurde wegen der WHERE-Klausel die Tabelle Schadensfall gewählt; wegen der Reihenfolge der Verknüpfungen wäre auch Zuordnung_SF_FZ als Haupttabelle geeignet.
SELECT sf.Datum, SUBSTRING(sf.Ort from 1 for 30) as Ort, sf.Schadenshoehe,
zu.Schadenshoehe as Teilschaden,
fz.Kennzeichen,
vv.Vertragsnummer as Vertrag, vv.Abschlussdatum, vv.Art,
vn.Name as VN_Name, vn.Vorname as VN_Vorname
from Schadensfall sf
join Zuordnung_SF_FZ zu on sf.ID = zu.Schadensfall_ID
join Fahrzeug fz on fz.ID = zu.Fahrzeug_ID
join Versicherungsvertrag vv on fz.ID = vv.Fahrzeug_ID
join Versicherungsnehmer vn on vn.ID = vv.Versicherungsnehmer_ID
where EXTRACT(YEAR from sf.Datum) = 2008
order by zu.Schadensfall_ID, zu.Fahrzeug_ID;
Die Variante aus der Aufgabenstellung enthält folgende Problemstellen:
- Zeile 1: Der Tabellen-Alias sf fehlt bei Schadenshoehe und bei Ort. Bei Datum fehlt er auch, aber das ist kein Problem, weil es diese Spalte nur bei dieser Tabelle gibt.
- Zeile 2: Diese Spalte sollte einen Spalten-Alias bekommen wegen der abweichenden Bedeutung zu sf.Schadenshoehe.
- Zeile 4: Es ist schöner, auch hier mit einem Tabellen-Alias zu arbeiten.
- Zeile 5: Der Bindestrich in der Bezeichnung des Spalten-Alias wird nicht bei allen DBMS akzeptiert.
- Zeile 7, 8, 9: Zur Spalte ID ist jeweils die Tabelle anzugeben, ggf. mit dem Alias. Die JOIN-ON-Bedingung bezieht sich nicht automatisch auf diese Spalte und diese Tabelle.
- Zeile 9, 10: In Zeile 9 ist die Tabelle Versicherungsvertrag vv noch nicht bekannt. Wegen der Verknüpfungen ist zuerst Zeile 10 zu verwenden, danach Zeile 9. Die Verknüpfung über vv.Fahrzeug_ID = zu.Fahrzeug_ID ist nicht glücklich (wenn auch korrekt); besser ist der Bezug auf die direkt zugeordnete Tabelle Fahrzeug und deren PrimaryKey, nämlich ID.
- Zeile 11: Es ist klarer, auch hier den Tabellen-Alias sf zu verwenden.
- Zeile 12: Der Tabellen-Alias zu fehlt bei beiden Spalten. Bei Fahrzeug_ID ist er erforderlich (doppelte Verwendung bei vv), bei Schadensfall_ID sinnvoll.
| Lösung zu Übung 3 | Sinnvolle Verknüpfung von Tabellen | Zur Übung |
SELECT Vertragsnummer, Abschlussdatum, Art,
Name, Vorname,
Fahrzeug_ID,
Mitarbeiter_ID
from Versicherungsvertrag vv
join Versicherungsnehmer vn on vn.ID = vv.Versicherungsnehmer_ID
where vv.Abschlussdatum >= '01.01.1990';
| Lösung zu Übung 4 | Sinnvolle Verknüpfung von Tabellen | Zur Übung |
SELECT vv.Vertragsnummer as Vertrag, vv.Abschlussdatum, vv.Art,
vn.Name as VN_Name, vn.Vorname as VN_Vorname,
fz.Kennzeichen,
mi.Name as MI_Name, mi.Vorname as MI_Vorname
from Versicherungsvertrag vv
join Versicherungsnehmer vn on vn.ID = vv.Versicherungsnehmer_ID
join Fahrzeug fz on fz.ID = vv.Fahrzeug_ID
join Mitarbeiter mi on mi.ID = vv.Mitarbeiter_ID
where vv.Abschlussdatum >= '01.01.1990';
| Lösung zu Übung 5 | Sinnvolle Verknüpfung von Tabellen | Zur Übung |
SELECT vv.Vertragsnummer as Vertrag, vv.Abschlussdatum, vv.Art,
vn.Name as VN_Name, vn.Vorname as VN_Vorname,
fz.Kennzeichen,
mi.Name as MI_Name, mi.Vorname as MI_Vorname
from Versicherungsvertrag vv
join Versicherungsnehmer vn on vn.ID = vv.Versicherungsnehmer_ID
join Fahrzeug fz on fz.ID = vv.Fahrzeug_ID
join Mitarbeiter mi on mi.ID = vv.Mitarbeiter_ID
where vn.Eigener_kunde = 'J'
and ( ( vv.Art = 'HP' and vv.Abschlussdatum > '31.12.1985' )
or ( vv.Art = 'TK' and vv.Abschlussdatum > '31.12.1990' )
OR ( vv.Art = 'VK' )
or ( fz.Kennzeichen STARTING WITH 'RE-' ) );
Siehe auch
BearbeitenIn diesem Kapitel werden Sachverhalte der folgenden Themen angesprochen: