Mehr zu JOIN

Aus Wikibooks
Wechseln zu: Navigation, Suche

Seitentitel: Einführung in SQL: Mehr zu JOIN
(Einführung in SQL: Mehr zu JOIN)


Die folgenden Ergänzungen zu JOIN sind in besonderen Situationen hilfreich.

Welcher JOIN passt wann?[Bearbeiten]

Diese 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.

Eltern und ihre Kinder

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 Relation1010.svg Alle Paare und (falls es Kinder gibt) auch diese SELECT * FROM Paare
  LEFT  JOIN Kinder ON Paare.Key = Kinder.Key
INNER JOIN Relation1110.svg Nur Paare, die Kinder haben SELECT * FROM Paare
  INNER JOIN Kinder ON Paare.Key = Kinder.Key
RIGHT JOIN Relation1100.svg Alle Kinder und (falls es Eltern gibt) auch diese SELECT * FROM Paare
  RIGHT JOIN Kinder ON Paare.Key = Kinder.Key
LEFT JOIN
IS NULL
Relation1011.svg 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
Relation1101.svg Nur Waisenkinder SELECT * FROM Paare
  RIGHT  JOIN Kinder ON Paare.Key = Kinder.Key
  WHERE Paare.Key IS NULL
FULL JOIN Relation1000.svg Alle Paare und alle Kinder SELECT * FROM Paare
  FULL  JOIN Kinder ON Paare.Key = Kinder.Key
FULL JOIN
IS NULL
Relation1001.svg 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
Relation1110.svg 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[Bearbeiten]

Solche 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]

Aufgabe

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;
Crystal Clear app terminal.png Ausgabe
KENNZEICHEN	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]

Aufgabe

Zeige zu jedem Fahrzeug mit mehreren Schadensfällen den zeitlichen Abstand von einem Vorfall zum nächsten an.

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
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;
Crystal Clear app terminal.png 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
Lösungswege 2 und 3

Alternativen bieten die folgenden Lösungen:

Datumsvergleich als Teil der Verknüpfungsbedingungen
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;
Datumsvergleich als Funktion bei den SELECT-Spalten
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;
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 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[Bearbeiten]

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:

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;
Crystal Clear app terminal.png 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.

Weitere Situationen[Bearbeiten]

Zum 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[Bearbeiten]

Mit 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:

Aufgabe

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[Bearbeiten]

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:

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[Bearbeiten]

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.

Übungen[Bearbeiten]

Übung 1 Fragen zum Verständnis Zur Lösung

Welche der folgenden Aussagen sind wahr, welche falsch?

  1. Eine Tabelle kann mit sich selbst verknüpft werden.
  2. SELF JOIN ist nur ein inhaltlicher Begriff, aber kein SQL-Schlüsselwort.
  3. Bei einem SELF JOIN sind nur INNER JOINs erlaubt.
  4. Eine bestimmte Tabelle darf in einem SELF JOIN nur zweimal verwendet werden.
  5. Für einen SELF JOIN können Tabellen-Aliase benutzt werden, aber sie sind nicht überall erforderlich.
  6. Ein CROSS JOIN ist eine Verknüpfung zweier Tabellen ohne Verknüpfungsbedingung.
  7. Bei einem CROSS JOIN darf sich die WHERE-Klausel nicht auf die (rechte) Tabelle des JOINs beziehen.
  8. 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ösungen

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[Bearbeiten]

Bei Wikipedia finden Sie weitere Erläuterungen: