Arbeiten mit JOIN

Inhaltsverzeichnis: Abfragen für Fortgeschrittene

Zurück zu: Einfache Tabellenverknüpfung

Weiter zu: OUTER JOIN

(Einführung in SQL: 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

Um 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

Aufgabe

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

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

Aufgabe

Suche die Dienstwagen vom Typ Mercedes.

Zulässig, aber nicht so schön, weil Verknüpfungsbedingung und Auswahlbedingung vermischt werden

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 
        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 sind

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');

Natürlich sind Einschränkungen auf beide Tabellen möglich:

Aufgabe

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%';

Ausgabe

MITNR   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%';

Mitarbeiter mit 'M' und Mercedes als Dienstwagen

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

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

Aufgabe

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

In 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

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

Aufgabe

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ösungen

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

In diesem Kapitel werden Sachverhalte der folgenden Themen angesprochen:

Inhaltsverzeichnis: Abfragen für Fortgeschrittene

Zurück zu: Einfache Tabellenverknüpfung

Weiter zu: OUTER JOIN