Hilfe
Diese Seite zu deinem Buch hinzufügen
Sammlung zeigen (0 Wikiseiten)
Seiten vorschlagenEinführung in SQL: Mehr zu JOIN
Aus Wikibooks
Die folgenden Möglichkeiten von JOIN sind in besonderen Situationen hilfreich.
Inhaltsverzeichnis |
[Bearbeiten] SELF JOIN – Verknüpfung mit sich selbst
Eine Besonderheit sind Verknüpfungen einer Tabelle mit sich selbst. Dies wird immer dann nötig, wenn Informationen einer einzigen Spalte aus verschiedenen Datensätzen gesucht und verbunden werden. Dafür wird ein JOIN benutzt, der auf beiden Seiten dieselbe Tabelle <tabelle> benutzt; 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 sah ich einmal ein solches Problem mit einer sehr vertrackten Ausgabe, bei dem diese Ursache erst nach längerer Diskussion klar wurde.
Ich möchte dies an einem Beispiel umsetzen.
Zeige zu jedem Fahrzeug mit mehreren Schadensfällen den zeitlichen Abstand von einem Vorfall zum nächsten an.
[Bearbeiten] Lösungsweg 1
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.
Datumsvergleich als Teil der Auswahlbedingung
SQL-Quelltext:
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;
SQL-Ausgabe:
ID 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
[Bearbeiten] Lösungswege 2 und 3
Alternativen dazu bieten die folgenden Lösungen:
Datumsvergleich als Teil der Verknüpfungsbedingungen
SQL-Quelltext:
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 Schadensfaelle sf3 JOIN Zuordnung_SF_FZ zu3 ON zu3.Schadensfall_ID = sf3.ID WHERE sf1.Datum < suf3.Datum AND zu3.Fahrzeug_ID = zu1.Fahrzeug_ID ) JOIN Fahrzeug fz ON zu1.Fahrzeug_ID = fz.ID ORDER BY fz.ID, Datum1;
Datumsvergleich als Funktion bei den SELECT-Spalten
SQL-Quelltext:
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;
[Bearbeiten] Erläuterungen
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 benutzt dies als 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.
[Bearbeiten] Erweiterung durch einen OUTER JOIN
Bei 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:
SQL-Quelltext:
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;
SQL-Ausgabe:
ID 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.
[Bearbeiten] Weitere Situationen
Zum Schluss möchte ich noch ein paar andere Beispiele erwähnen, 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.
- 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.
[Bearbeiten] WITH – Inline-View
Oft kommt es vor, dass man die Daten aus einer Tabelle erst bearbeiten möchte, bevor man sie mit einer anderen Tabelle verknüpft. Beispiel:
SQL-Quelltext:
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:
SQL-Quelltext:
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.
[Bearbeiten] Zusammenfassung
In 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.
