Mehr zu JOIN
Die folgenden Ergänzungen zu JOIN sind in besonderen Situationen hilfreich.
Welcher JOIN passt wann?
BearbeitenDiese Frage stellt sich vor allem Anfängern sehr oft. Neben den (theoretischen) Überlegungen der vorigen Kapitel helfen oft besondere Beispiele; vielleicht ist dieses nützlich.
Wir haben zwei Tabellen: Paare (also Eltern) und Kinder. Es gibt kinderlose Paare, Paare mit Kindern und Waisenkinder. Wir wollen die Eltern und Kinder in Abfragen verknüpfen; bei den Symbolen steht der linke Kreis für die Tabelle Paare und der rechte Kreis für die Tabelle Kinder.
| LEFT JOIN | 
|
Alle Paare und (falls es Kinder gibt) auch diese | SELECT * FROM Paare LEFT JOIN Kinder ON Paare.Key = Kinder.Key |
| INNER JOIN | 
|
Nur Paare, die Kinder haben | SELECT * FROM Paare INNER JOIN Kinder ON Paare.Key = Kinder.Key |
| RIGHT JOIN | 
|
Alle Kinder und (falls es Eltern gibt) auch diese | SELECT * FROM Paare RIGHT JOIN Kinder ON Paare.Key = Kinder.Key |
| LEFT JOIN IS NULL |
|
Nur Paare, die keine Kinder haben | SELECT * FROM Paare LEFT JOIN Kinder ON Paare.Key = Kinder.Key WHERE Kinder.Key IS NULL |
| RIGHT JOIN IS NULL |
|
Nur Waisenkinder | SELECT * FROM Paare RIGHT JOIN Kinder ON Paare.Key = Kinder.Key WHERE Paare.Key IS NULL |
| FULL JOIN | 
|
Alle Paare und alle Kinder | SELECT * FROM Paare FULL JOIN Kinder ON Paare.Key = Kinder.Key |
| FULL JOIN IS NULL |
|
Alle kinderlosen Paare und alle Waisenkinder | SELECT * FROM Paare FULL JOIN Kinder ON Paare.Key = Kinder.Key WHERE Kinder.Key IS NULL OR Paare.Key IS NULL |
| Die zweite Variante mit dem INNER JOIN kann man auch so ausdrücken: | |||
| LEFT JOIN IS NOT NULL |
|
Alle Paare und (falls es Kinder gibt) auch diese, wobei es ein Kind geben muss | SELECT * FROM Paare LEFT JOIN Kinder ON Paare.Key = Kinder.Key WHERE Kinder.Key IS NOT NULL |
Tatsächlich sind oft mehrere Wege möglich, wie bereits im letzten Kapitel gesagt wurde: Das Ergebnis für "A LEFT JOIN B" gleicht dem von "B RIGHT JOIN A".
SELF JOIN – Verknüpfung mit sich selbst
BearbeitenSolche Verknüpfungen sind immer dann nötig, wenn Werte einer einzigen Spalte aus verschiedenen Datensätzen verbunden werden. Der JOIN dafür benutzt auf beiden Seiten dieselbe Tabelle <tabelle>; diese beiden „Instanzen“ müssen durch einen Alias unterschieden werden.
SELECT <spaltenliste>
FROM <tabelle> t1
JOIN <tabelle> t2 ON <verknüpfung>
WHERE <auswahlbedingungen>
Hinweis: Es funktioniert nicht, wenn eine der beiden Instanzen mit Alias und die andere ohne Alias benutzt wird. Dann kommt das DBMS erst recht durcheinander. In einem Forum stand einmal ein solches Problem mit einer sehr vertrackten Ausgabe, bei dem diese Ursache erst nach längerer Diskussion klar wurde.
Dies soll zunächst an zwei Beispielen umgesetzt werden.
Beispiel 1
Bearbeiten
Zeige zu jedem Fahrzeug andere Fahrzeuge aus dem gleichen Kreis.
Wir beschränken uns darauf, dass in einer Zeile jeweils zwei von allen möglichen Kombinationen angezeigt werden, auch wenn viele Angaben wiederholt werden.
select a.Kennzeichen, b.Kennzeichen
from Fahrzeug a
JOIN Fahrzeug b
on SUBSTRING(a.Kennzeichen from 1 for 3)
= SUBSTRING(b.Kennzeichen from 1 for 3)
where a.Kennzeichen < b.Kennzeichen
order by a.Kennzeichen;
AusgabeKENNZEICHEN KENNZEICHEN1
----------- ------------
BO-GH 102 BO-KL 678
BOR-NO 234 BOR-PQ 567
BOR-NO 234 BOR-RS 890
BOR-PQ 567 BOR-RS 890
GE-AB 123 GE-AC 246
GE-AB 123 GE-EG 892
GE-AC 246 GE-EG 892
RE-CD 456 RE-LM 901
RE-CD 456 RE-LM 902
RE-CD 456 RE-LM 903
Gesucht werden Kombinationen eines Fahrzeugs mit jeweils einem anderen Fahrzeug, wobei die Bedingung „gleicher Kreis“ erfüllt sein soll. Wir brauchen also innerhalb eines SELECT-Befehls zwei Zugriffe auf die Tabelle Fahrzeug mit einer passenden Vergleichsbedingung. (Diese haben wir etwas ungenau formuliert, damit sie nicht unübersichtlich wird.)
Dies ist gleichzeitig ein Beispiel dafür, dass beliebige Bedingungen möglich sind. Überlegen Sie bitte auch, warum unter WHERE die „kleiner als“-Bedingung benutzt wird.
Beispiel 2
Bearbeiten Zeige zu jedem Fahrzeug mit mehreren Schadensfällen den zeitlichen Abstand von einem Vorfall zum nächsten an.
Wir benötigen für jedes der Fahrzeuge aus der Tabelle Schadensfall zwei Einträge mit dem Datum sowie den Abstand, der einfach als Differenz benutzt wird und somit die Anzahl der Tage anzeigt. Die Fahrzeuge sind freilich erst über die Tabelle Zuordnung_SF_FZ zu finden und müssen zusätzlich verbunden werden. Außerdem sind die Fahrzeuge und die Reihenfolge der Datumsangaben zu kontrollieren.
SELECT fz.ID, fz.Kennzeichen,
sf1.Datum AS Datum1, sf2.Datum AS Datum2, sf2.Datum - sf1.Datum AS Abstand
FROM Zuordnung_SF_FZ zu1
join Zuordnung_SF_FZ zu2 on zu1.Fahrzeug_ID = zu2.Fahrzeug_ID
join Schadensfall sf1 ON zu1.Schadensfall_ID = sf1.ID
JOIN Schadensfall sf2 on zu2.Schadensfall_ID = sf2.Id
JOIN Fahrzeug fz ON zu1.Fahrzeug_ID = fz.ID
where sf1.Datum < sf2.Datum
and sf2.Datum = ( SELECT MIN(sf3.Datum)
FROM Schadensfall sf3
join Zuordnung_SF_FZ zu3 on zu3.Schadensfall_ID = sf3.id
WHERE sf1.Datum < sf3.Datum
and zu3.Fahrzeug_ID = zu1.Fahrzeug_ID )
order by fz.ID, Datum1;
AusgabeID KENNZEICHEN DATUM1 DATUM2 ABSTAND
-- -------------- ---------- ---------- -------
4 GE-AB 123 03.02.2007 05.10.2008 610
6 HER-EF 789 19.12.2007 21.06.2009 550
7 BO-GH 102 11.07.2007 13.03.2009 611
7 BO-GH 102 13.03.2009 01.08.2009 141
Alternativen bieten die folgenden Lösungen:
SELECT fz.ID, fz.Kennzeichen,
sf1.Datum AS Datum1, sf2.Datum AS Datum2, sf2.Datum - sf1.Datum AS Abstand
FROM Zuordnung_SF_FZ zu1
join Zuordnung_SF_FZ zu2 on zu1.Fahrzeug_ID = zu2.Fahrzeug_ID
join Schadensfall sf1 ON zu1.Schadensfall_ID = sf1.ID
JOIN Schadensfall sf2 on zu2.Schadensfall_ID = sf2.Id
and sf1.Datum < sf2.Datum
and sf2.Datum = ( SELECT MIN(sf3.Datum)
FROM Schadensfall sf3
join Zuordnung_SF_FZ zu3
on zu3.Schadensfall_ID = sf3.ID
WHERE sf1.Datum < sf3.Datum
and zu3.Fahrzeug_ID = zu1.Fahrzeug_ID )
JOIN Fahrzeug fz ON zu1.Fahrzeug_ID = fz.ID
order by fz.ID, Datum1;
SELECT fz.ID, fz.Kennzeichen,
sf1.Datum AS Datum1, MIN(sf2.Datum) AS Datum2, MIN(sf2.Datum - sf1.Datum) AS Abstand
FROM Zuordnung_SF_FZ zu1
join Zuordnung_SF_FZ zu2 on zu1.Fahrzeug_ID = zu2.Fahrzeug_ID
join Schadensfall sf1 ON zu1.Schadensfall_ID = sf1.ID
JOIN Schadensfall sf2 on zu2.Schadensfall_ID = sf2.Id
and sf1.Datum < sf2.Datum
JOIN Fahrzeug fz ON zu1.Fahrzeug_ID = fz.ID
group by fz.ID, fz.Kennzeichen, sf1.Datum
order by fz.ID, Datum1;
In dieser Aufgabe stecken mehrere Probleme:
- Die Angaben aus der Spalte Datum der Tabelle Schadensfall müssen zweimal geholt werden.
- Zu jedem Schadensfall wird der Eintrag der Tabelle Zuordnung_SF_FZ benötigt, weil die Schadensfälle für jedes Fahrzeug gesucht werden.
- Das Datum, das zu sf1 gehört, muss immer „kleiner“ sein, also früher liegen als das Datum, das zu sf2 gehört.
- Außerdem benötigen wir „irgendwo“ die Einschränkung, dass zum Vergleich nur der jeweils folgende Schadensfall genommen werden darf, also das MINimum der späteren Einträge:
- Die erste Lösung verwendet dafür eine Unterabfrage für eine Auswahlbedingung.
- Die zweite Lösung arbeitet mit einer Unterabfrage bei der Verknüpfungsbedingung.
- Die dritte Lösung benutzt das MINimum direkt als Aggregatfunktion und verlangt „zum Ausgleich“ eine GROUP BY-Klausel.
Die Lösung benötigt deshalb mehrfach verknüpfte Tabellen:
- Als Grundlage wird die Tabelle der Zuordnungen zwischen Schadensfällen und Fahrzeugen zu1 verwendet.
- Hauptverknüpfung ist der Self-Join zu2 auf dieselbe Tabelle, weil nur solche Einträge verknüpft werden sollen, die sich auf dasselbe Fahrzeug beziehen.
- Zu jedem Schadensfall aus zu1 werden die detaillierten Angaben aus sf1 geholt.
- Zu jedem Schadensfall aus zu2 werden die detaillierten Angaben aus sf2 geholt.
- Ergänzend benötigen wir das Kennzeichen des betreffenden Fahrzeugs, also einen JOIN auf Fahrzeug.
- Vor allem müssen die Vergleichsbedingungen für die Datumsangaben eingebaut werden.
Welche Lösung die Datenbank am wenigsten belastet, kann nicht generell gesagt werden, weil es von zu vielen Umständen abhängt.
Erweiterung durch einen OUTER JOIN
BearbeitenBei diesen Lösungen stehen nicht alle Schadensfälle im Ergebnis, weil es nur um den zeitlichen Abstand ging. Wenn beispielsweise auch die Schadenshöhe gewünscht wird, müssen wir dafür sorgen, dass von sf1 oder sf2 alle Einträge angezeigt werden; wir brauchen also einen OUTER JOIN wie zum Beispiel (auf der Grundlage der letzten Version) so:
SELECT fz.ID, fz.Kennzeichen,
sf1.Datum AS Datum1, MIN(sf2.Datum) AS Datum2, MIN(sf2.Datum - sf1.Datum) AS Abstand,
sf1.Schadenshoehe
FROM Zuordnung_SF_FZ zu1
left join Zuordnung_SF_FZ zu2 on zu1.Fahrzeug_ID = zu2.Fahrzeug_ID
left join Schadensfall sf1 ON zu1.Schadensfall_ID = sf1.ID
left JOIN Schadensfall sf2 on zu2.Schadensfall_ID = sf2.Id
and sf1.Datum < sf2.Datum
left JOIN Fahrzeug fz ON zu1.Fahrzeug_ID = fz.ID
group by fz.ID, fz.Kennzeichen, sf1.Datum, sf1.Schadenshoehe
order by fz.ID, Datum1;
AusgabeID KENNZEICHEN DATUM1 DATUM2 ABSTAND SCHADENSHOEHE
-- --------------- ---------- ---------- ------- -------------
3 RE-LM 903 27.05.2008 1.438,75
4 GE-AB 123 03.02.2007 05.10.2008 610 1.234,50
4 GE-AB 123 05.10.2008 1.983,00
5 RE-CD 456 11.07.2007 2.066,00
6 HER-EF 789 19.12.2007 21.06.2009 550 3.715,60
6 HER-EF 789 21.06.2009 865,00
7 BO-GH 102 11.07.2007 13.03.2009 611 2.066,00
7 BO-GH 102 13.03.2009 01.08.2009 141 4.092,15
7 BO-GH 102 01.08.2009 2.471,50
Wir nehmen es hin, dass dann alle Schadensfälle aufgeführt werden, auch für die Fahrzeuge, die nur einmal „aufgefallen“ sind. Dies ist eine Folge davon, dass Grundlage aller Verknüpfungen die Tabelle der Zuordnungen sein musste.
Bei allen solchen Situationen müssen Sie genau überlegen, wie die verschiedenen Instanzen miteinander verknüpft werden und wie die übrigen Bedingungen einzubinden sind. Oft führen erst mehrere Versuche zum Ziel. Hilfreich sind auch die Ausführungspläne, die ein DBMS anbieten kann.
Weitere Situationen
BearbeitenZum Schluss sollen noch ein paar andere Beispiele erwähnt werden, bei denen ein Self-Join hilft.
- Wenn bei den Dienstwagen die privat gefahrenen Strecken abgerechnet werden sollen, können der km-Stand beim Fahrtantritt und beim Fahrtende in derselben Spalte, aber in getrennten Datensätzen gespeichert werden.
- Doppelte Adressen innerhalb einer Adressendatei können aufgespürt werden (siehe Übung 3).
- Wenn in der Tabelle Mitarbeiter zu einem Mitarbeiter der Leiter der Abteilung gesucht wird, benötigen wir wegen des doppelten Zugriffs auf dieselbe Tabelle ebenfalls einen Self-Join.
CROSS JOIN – das kartesische Produkt
BearbeitenMit dieser speziellen Formulierung kann man deutlich machen, dass man wirklich ein kartesisches Produkt herstellen will und nicht etwa nur die JOIN-Bedingung vergessen hat:
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi
CROSS JOIN Dienstwagen dw;
Als Ergebnis wird tatsächlich jede Kombination eines Mitarbeiters mit einem Dienstwagen ausgegeben, also n mal m Sätze – wie beim allerersten Versuch im Kapitel Einfache Tabellenverknüpfung. Man kann die Ergebnismenge auch einschränken durch eine WHERE-Klausel:
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi
CROSS JOIN Dienstwagen dw
WHERE mi.Name like 'S%' and CHAR_LENGTH(dw.Kennzeichen) = 10;
Hinweis: Die DBMS verhalten sich bei einem CROSS JOIN unterschiedlich; teilweise ist ein CROSS JOIN mit WHERE-Klausel nichts anderes als ein INNER JOIN.
Der Nutzen des CROSS JOIN wird bei unserer sparsamen Beispieldatenbank nicht klar. Unter Oracle wäre folgendes Verfahren möglich und hilfreich:
Kontrollieren Sie mit der Tabelle Fahrzeugbuchung, welche Fahrzeuge am 2.12.2009 im Fuhrpark zur Verfügung stehen.
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi
CROSS JOIN Dienstwagen dw
INNER JOIN Fahrzeugbuchung fb
ON dw.Kennzeichen = fb.Kennzeichen
WHERE fb.Datum = to_date('02.12.2009','dd.mm.yyyy')
AND fb.Status = 'noch nicht gebucht';
WITH – Inline-View
BearbeitenOft kommt es vor, dass man die Daten aus einer Tabelle erst bearbeiten möchte, bevor man sie mit einer anderen Tabelle verknüpft. Beispiel:
SELECT Kuerzel, Bezeichnung, Anzahl_Mitarbeiter
FROM Abteilung
INNER JOIN ( select Abteilung_ID, count(*) AS Anzahl_Mitarbeiter
FROM Mitarbeiter
GROUP BY Abteilung_ID
) MA_Anzahl
ON Abteilung.ID = MA_Anzahl.Abteilung_ID
;
Dabei wird zunächst nach der Tabelle Mitarbeiter die Anzahl der Mitarbeiter für jede Abteilung bestimmt. Das Ergebnis wird wie eine Tabelle MA_Anzahl behandelt und über Abteilung_ID mit der Tabelle Abteilung verknüpft.
Diese Syntax ist ziemlich verschachtelt. Man kann sie auch so schreiben:
WITH MA_Anzahl as
( select Abteilung_ID, count(*) AS Anzahl_Mitarbeiter
FROM Mitarbeiter
GROUP BY Abteilung_ID
)
SELECT Kuerzel, Bezeichnung, Anzahl_Mitarbeiter
FROM Abteilung
INNER JOIN MA_Anzahl
ON Abteilung.ID = MA_Anzahl.Abteilung_ID
;
MA_Anzahl wird benutzt wie eine VIEW, die allerdings nicht permanent angelegt wird, sondern die nur für die Ausführung dieses einen SQL-Befehls gültig ist. Der Unterschied liegt „nur“ darin, dass die Unterabfrage herausgelöst wird und durch WITH als separate temporäre Tabelle eingebunden wird.
Ob man die obere oder die untere Variante besser findet, ist sicher Geschmackssache und hat auch damit zu tun, welche Formulierung man gewöhnt ist. Wenn man sich die SQL-Beispiele im Kapitel Fremdschlüssel-Beziehungen daraufhin ansieht, dann würde eine Formulierung ohne WITH viel länger und komplizierter werden.
Zusammenfassung
BearbeitenIn diesem Kapitel lernten Sie einige weitere Möglichkeiten im Zusammenhang mit JOINs kennen.
- Für bestimmte Anforderungen sind Verknüpfungen einer Tabelle mit sich selbst sinnvoll oder notwendig.
- In diesen Fällen sind die Auswahl- und Verknüpfungsbedingungen besonders sorgfältig zu bestimmen.
- Durch WITH können Verknüpfungen über JOINs übersichtlicher werden.
Übungen
Bearbeiten
| Übung 1 | Fragen zum Verständnis | Zur Lösung |
Welche der folgenden Aussagen sind wahr, welche falsch?
- Eine Tabelle kann mit sich selbst verknüpft werden.
- SELF JOIN ist nur ein inhaltlicher Begriff, aber kein SQL-Schlüsselwort.
- Bei einem SELF JOIN sind nur INNER JOINs erlaubt.
- Eine bestimmte Tabelle darf in einem SELF JOIN nur zweimal verwendet werden.
- Für einen SELF JOIN können Tabellen-Aliase benutzt werden, aber sie sind nicht überall erforderlich.
- Ein CROSS JOIN ist eine Verknüpfung zweier Tabellen ohne Verknüpfungsbedingung.
- Bei einem CROSS JOIN darf sich die WHERE-Klausel nicht auf die (rechte) Tabelle des JOINs beziehen.
- Die Schreibweise mit WITH ist kein Sonderfall eines JOINs, sondern eine übersichtlichere Schreibweise, wenn mehrere Tabellen verknüpft werden.
| Übung 2 | Verknüpfung einer Tabelle mit sich selbst | Zur Lösung |
Suchen Sie zu jedem Mitarbeiter den Namen und Vornamen des Leiters der Abteilung. Die Abteilungsleiter in unserer einfachen Firmenhierarchie haben keinen Vorgesetzten; sie sollen in der Liste deshalb nicht aufgeführt werden.
| Übung 3 | Doppelte Adressen suchen | Zur Lösung |
Suchen Sie Einträge in der Tabelle Versicherungsnehmer, bei denen Name, Vorname, PLZ, Strasse übereinstimmen. Jeweils zwei dieser Adressen sollen mit ihrer ID und den übereinstimmenden Angaben aufgeführt werden.
Hinweis: Benutzen Sie einen JOIN, der sich nicht auf übereinstimmende IDs bezieht.
| Lösung zu Übung 1 | Fragen zum Verständnis | Zur Übung |
Die Aussagen 1, 2, 6, 8 sind wahr, die Aussagen 3, 4, 5, 7 sind falsch.
| Lösung zu Übung 2 | Verknüpfung einer Tabelle mit sich selbst | Zur Übung |
select mi1.Abteilung_ID as Abt, mi1.Name, mi1.Vorname, mi2.Name as LtrName, mi2.Vorname as LtrVorn
from Mitarbeiter mi1
join Abteilung ab on mi1.Abteilung_ID = ab.ID
join Mitarbeiter mi2 on mi2.Abteilung_ID = ab.ID
where mi2.Ist_Leiter = 'J'
and mi1.Ist_Leiter = 'N'
| Lösung zu Übung 3 | Doppelte Adressen suchen | Zur Übung |
select a.Name, a.Vorname, a.PLZ, a.Strasse, a.ID, b.ID
from Versicherungsnehmer a
JOIN Versicherungsnehmer b
on a.Name = b.Name and a.Vorname = b.Vorname
and a.PLZ = b.PLZ and a.Strasse = b.Strasse
where a.ID < b.ID;
Siehe auch
BearbeitenBei Wikipedia finden Sie weitere Erläuterungen:
- Auswertungsplan, auch „Ausführungsplan“ genannt