3.5 INNER JOIN

In relationalen Datenbanken werden sachlich zusammengehörende Daten auf verschiedene Tabellen verteilt. Bei einer Abfrage können die verteilten Daten je nach Anforderung wieder zusammengefügt und als Abfrageergebnis dem Benutzer zur Verfügung gestellt werden.

Abfrage von Daten aus zwei Tabellen, die über ein Schlüsselpaar aus Fremd- und Primärschlüssel verbunden sind.

Beispiel

Gewünscht wird eine Übersicht aller Mietverträge. Diese soll für jeden Mietvertrag dessen ID und Abschlussdatum enthalten sowie die ID, den Vorname und den Nachname des Kunden, der den Mietvertrag abgeschlossen hat.

Fahrradvermietung (Relationales Datenmodell)
Abb. 3-49b: Fahrradvermietung (Relationales Datenmodell)

Die gewünschten Daten sind in den Tabellen Mietvertrag und Kunde gespeichert.

  • Tabelle Mietvertrag

    ID Abschlussdatum Mietbeginn Mietdauer ↑Kunde_ID
    1 2015-08-11 2015-08-12 3 1
    2 2015-08-13 2015-08-15 5 2

  • Tabelle Kunde

    ID Vorname Nachname
    1 Max Maier
    2 Fritz Haug
    3 Eva Maier
    4 Anja Sommer
Abb. 3-50: Die Tabellen Mietvertrag und Kunde.

  1. In der SELECT-Klausel werden zunächst die Spalten ausgewählt, die die gewünschten Daten enthalten.

    Da sowohl aus der Tabelle Mietvertrag als auch aus der Tabelle Kunde eine Spalte namens ID ausgewählt werden soll, reicht die Angabe des Spaltennamens für eine eindeutige Identifikation nicht aus. Bei der Auswahl gleichnamiger Spalten aus verschiedenen Tabellen muss deshalb vor dem Spaltennamen der Name der jeweiligen Tabelle angegeben werden. Beide Angaben werden durch einen Punkt getrennt.

    SQL
    SELECT Mietvertrag.ID, Abschlussdatum, Kunde_ID, Kunde.ID, Vorname, Nachname
    ...
    Abb. 3-51: SQL – Auswahl der gewünschten Spalten

  2. In der FROM-Klausel wird angegeben in welchen Tabellen die gewünschten Daten gespeichert sind und über welches Schlüsselpaar aus Fremd- und Primärschlüssel diese Tabellen miteinander verbunden sind.

    Das Abfrageergebnis wird dabei In zwei Schritten ermittelt:

    1. Die INNER-JOIN-Verknüpfung verbindet jeden Datensatz aus der Tabelle Mietvertrag mit jedem Datensatz der Tabelle Kunde (kartesisches Produkt1). Das heißt, jedem Mietvertrag wird der Reihe nach jeder Kunde zugeordnet. Welcher Kunde den Mietvertrag tatsächlich abgeschlossen hat, spielt dabei noch keine Rolle.

      SQL
      SELECT Mietvertrag.ID, Abschlussdatum, Kunde_ID, Kunde.ID, Vorname, Nachname
      FROM Mietvertrag INNER JOIN Kunde;
      Abb. 3-52: SQL – INNER JOIN
      Datenbanktabellen:

      • Tabelle Mietvertrag

        ID Abschlussdatum Mietbeginn Mietdauer ↑Kunde_ID
        1 2015-08-11 2015-08-12 3 1
        2 2015-08-13 2015-08-15 5 2

      • Tabelle Kunde

        ID Vorname Nachname
        1 Max Maier
        2 Fritz Haug
        3 Eva Maier
        4 Anja Sommer
      Abfrageergebnis:

      • Abfrageergebnis der SQL-Anweisung aus Abb. 3-52

        Mietvertrag.ID Abschlussdatum Kunde_ID Kunde.ID Vorname Nachname
        1 2015-08-11 1 1 Max Maier
        1 2015-08-11 1 2 Fritz Haug
        1 2015-08-11 1 3 Eva Maier
        1 2015-08-11 1 4 Anja Sommer
        2 2015-08-13 2 1 Max Maier
        2 2015-08-13 2 2 Fritz Haug
        2 2015-08-13 2 3 Eva Maier
        2 2015-08-13 2 4 Anja Sommer
      Abb. 3-53: Abfrageergebnis der SQL-Anweisung aus Abb. 3-52.

    2. Im ersten Schritt wurden mittels INNER JOIN die Daten eines jeden Mietvertrags mit den Daten eines jeden Kunden verknüpft. Im zweiten Schritt müssen nun mit Hilfe der ON-Klausel die Zeilen herausgefiltert werden, bei denen in der Spalte Kunde_ID (Fremdschlüssel in der Tabelle Mietvertrag) und der Spalte Kunde.ID (Primärschlüssel in der Tabelle Kunde) der gleiche Wert steht. Nur in diesen Zeilen wurden die Daten eines Mietvertrags mit den Daten des Kunden verknüpft, der diesen auch abgeschlossen hat.

      SQL
      SELECT Mietvertrag.ID, Abschlussdatum, Kunde_ID, Kunde.ID, Vorname, Nachname
      FROM Mietvertrag INNER JOIN Kunde ON Kunde_ID = Kunde.ID;
      Abb. 3-54: SQL – INNER JOIN ... ON ...

      Abfrageergebnis

      Mietvertrag.ID Abschlussdatum Kunde_ID Kunde.ID Vorname Nachname
      1 2015-08-11 1 1 Max Maier
      2 2015-08-13 2 2 Fritz Haug
      Abb. 3-55: Abfrageergebnis der SQL-Anweisung aus Abb. 3-54.

  3. Im Abfrageergebnis ist in jeder Zeile die ID des Kunden, der den Mietvertrag abgeschlossen hat, doppelt vorhanden: einmal in der aus der Tabelle Mietvertrag stammenden Spalte Kunde_ID und einmal in der aus der Tabelle Kunde stammenden Spalte Kunde.ID. Für die Aufgabenstellung unseres Beispiels reicht die Anzeige einer der beiden Spalten aus.

    SQL
    SELECT Mietvertrag.ID, Abschlussdatum, Kunde_ID, Vorname, Nachname
    FROM Mietvertrag INNER JOIN Kunde ON Kunde_ID = Kunde.ID;
    Abb. 3-56: SQL – INNER JOIN ... ON ...

    Abfrageergebnis

    Mietvertrag.ID Abschlussdatum Kunde_ID Vorname Nachname
    1 2015-08-11 1 Max Maier
    2 2015-08-13 2 Fritz Haug
    Abb. 3-57: Abfrageergebnis der SQL-Anweisung aus Abb. 3-56.

Der Ausdruck „tabelle INNER JOIN tabelle2“ verknüpft jeden Datensatz der Tabelle tabelle mit jedem Datensatz der Tabelle tabelle2 (kartesisches Produkt).

Mit Hilfe der ON-Klausel werden aus der Ergebnismenge des kartesischen Produkts dann die Zeilen herausgefiltert, die die angegebene Bedingung – tabelle.fremdschlüssel = tabelle2.primärschluessel – erfüllen.

SQL
SELECT [DISTINCT] spalte [, ...]
FROM tabelle [INNER JOIN tabelle2 ON tabelle.fremdschlüssel = tabelle2.primärschluessel]
[WHERE bedingung]
[ORDER BY {spalte [ASC|DESC]} [, ...]];
Abb. 3-58: SQL Syntax – SELECT-Anweisung inklusive INNER JOIN ... ON ...-Abschnitt
Merke: Abfrage von Daten aus zwei Tabellen, die über ein Schlüsselpaar aus Fremd- und Primärschlüssel verbunden sind.
Aufgabe

Aufgabe 3-9: Daten aus verschiedenen Tabellen abfragen

Datenbank Firmenwagen

Erstellen Sie jeweils eine SQL-Anweisung, die als Abfrageergebnis eine Tabelle mit den gewünschten Daten zurückgibt:

  1. Eine Übersicht aller Reservierungen. Diese soll für jede Reservierung Beginn und Ende sowie die Personalnummer und den Nachnamen des reservierenden Mitarbeiters enthalten. Lösung
    Lösung
    SQL
    SELECT Beginn, Ende, PersNr, Nachname
    FROM Reservierung INNER JOIN Mitarbeiter ON Mitarbeiter_ID = Mitarbeiter.ID;
    Abb. 3-59: SQL-Anweisung
  2. Eine Übersicht aller Versicherungsverträge. Diese soll für jeden Versicherungsvertrag die Versicherungsnummer und das Kennzeichen des versicherten Fahrzeugs enthalten. Außerdem soll ersichtlich sein, welchen Kaskoschutz der Versicherungsvertrag beihnhaltet. Lösung
    Lösung
    SQL
    SELECT VersNr, Kennzeichen, Kasko
    FROM Versicherungsvertrag INNER JOIN Fahrzeug ON Fahrzeug_ID = Fahrzeug.ID;
    Abb. 3-60: SQL-Anweisung
  3. Eine Übersicht aller Firmenwagen des Herstellers VW. Diese soll für jedes Fahrzeug das Kennzeichen, den Hersteller sowie das Modell enthalten und nach dem Modell absteigend sortiert sein. Lösung
    Lösung
    SQL
    SELECT Kennzeichen, Hersteller, Bezeichnung
    FROM Fahrzeug INNER JOIN Fahrzeugmodell ON Fahrzeugmodell_ID = Fahrzeugmodell.ID
    WHERE Hersteller = "VW"
    ORDER BY Bezeichnung DESC;
    Abb. 3-61: SQL-Anweisung
  4. Eine Übersicht der Instandhaltungen, die Kosten zwischen 300,00 und 500,00 EUR verursacht haben. Die Übersicht soll für jede Instandhaltung das Datum, die Kosten sowie das Kennzeichen und das Anschaffungsdatum des jeweiligen Fahrzeugs enthalten und bezüglich der angefallenen Kosten aufsteigend sortiert sein. Lösung
    Lösung
    SQL
    SELECT Datum, Kosten, Kennzeichen, Anschaffungsdatum
    FROM Instandhaltung INNER JOIN Fahrzeug ON Fahrzeug_ID = Fahrzeug.ID
    WHERE Kosten BETWEEN 300 AND 500
    ORDER BY Kosten ASC;
    Abb. 3-62: SQL-Anweisung

Die Datensätze mehrerer Tabellen mit JOIN verknüpfen

Sind die benötigten Daten in zwei Tabellen gespeichert, die nicht direkt miteinander über entsprechende Schlüsselfelder verbunden sind, oder sind die Daten auf mehr als zwei Tabellen verteilt, müssen mehrere JOIN-Operationen hintereinander ausgeführt werden.

Beispiel

Benötigt wird eine Übersicht, die für jedes bisher vermietete Fahrrad dessen Kennung sowie das Abschlussdatum des Mietvertrags zeigt.

1. Schritt: In welchen Tabellen sind die benötigten Daten gespeichert?

Fahrradvermietung (Ausschnitt aus dem relationalen Datenmodell)
Abb. 3-62b: Fahrradvermietung (Ausschnitt aus dem relationalen Datenmodell)

  • Tabelle Fahrrad

    ID Kennung ↑Fahrradtyp_ID
    1 Cross#7 1
    2 Mark#2 2

  • Tabelle Mietvertrag_vermietet_Fahrrad

    ↑Mietvertrag_ID ↑Fahrrad_ID
    1 1
    1 2
    2 2

  • Tabelle Mietvertrag

    ID Abschlussdatum Mietbeginn Mietdauer ↑Kunde_ID
    1 2015-08-11 2015-08-12 3 1
    2 2015-08-13 2015-08-15 5 2
Abb. 3-62c: Die Tabellen Fahrrad, Mietvertrag_vermietet_Fahrrad und Mietvertrag.

2. Schritt: Welche Tabellen enthalten die gesuchten Daten bzw. ermöglichen eine Verbindung dieser Tabellen?

Die Fahrradkennung ist in der Spalte Kennung der Tabelle Fahrrad, das Abschlussdatum in der gleichnamigen Spalte der Tabelle Mietvertrag gespeichert. Die beiden Tabellen, die die gesuchten Daten enthalten, sind nicht direkt über ein gemeinsames Schlüsselpaar aus Fremd- und Primärschlüssel miteinander verbunden. Jedoch sorgt die Tabelle Mietvertrag_vermietet_Fahrrad für eine indirekte Verbindung zwischen den Tabellen Fahrrad und Mietvertrag.

3. Schritt: Die Datensätze der verbundenen Tabellen (vgl. 2. Schritt) werden miteinander verknüpft.

Stehen die Tabellen fest, deren Datensätze miteinander verknüpft werden sollen, wählen wir eine Tabelle aus, die einen Fremdschlüssel enthält, der auf den Primärschlüssel einer anderen beteiligten Tabelle verweist. Anschließend verknüpfen wir ihre Datensätze mit jeweils dem Datensatz der anderen Tabelle, dessen Primärschlüsselwert dem Wert ihres Fremdschlüssels entspricht.

In unserem Beispiel verknüpfen wir zunächst die Datensätze der Tabelle Mietvertrag_vermietet_Fahrrad mit jeweils dem Datensatz der Tabelle Fahrrad, dessen Primärschlüsselwert dem Wert des Fremdschlüssels Fahrrad_ID entspricht.

Fahrradvermietung (Ausschnitt aus dem relationalen Datenmodell)
Abb. 3-62d: Fahrradvermietung (Ausschnitt aus dem relationalen Datenmodell)
SQL
SELECT ...
FROM Mietvertrag_vermietet_Fahrrad
  INNER JOIN Fahrrad
    ON Fahrrad_ID = Fahrrad.ID
  ...
Abb. 3-63: SQL – Jeder Datensatz der Tabelle Mietvertrag_vermietet_Fahrrad wird mit jeweils dem Datensatz der Tabelle Fahrrad verknüpft, dessen Primärschlüsselwert dem Wert des Fremdschlüssels Fahrrad_ID entspricht.

Zwischenergebnis 1

Mietvertrag_ID Fahrrad_ID Fahrrad.ID Kennung Fahrradtyp_ID
1 1 1 Cross#7 1
1 2 2 Mark#2 2
2 2 2 Mark#2 2
Abb. 3-63b: Zwischenergebnis nachdem jeder Datensatz der Tabelle Mietvertrag_vermietet_Fahrrad mit jeweils dem Datensatz der Tabelle Fahrrad verknüpft wurde, dessen Primärschlüsselwert dem Wert des Fremdschlüssels Fahrrad_ID entspricht.

Das daraus resultierende Zwischenergebnis verknüpfen wir dann mit einer weiteren Tabelle, mit der eine direkte Verbindung über Schlüsselfelder besteht. Im Beispiel ist dies die Tabelle Mietvertrag.

Fahrradvermietung (Ausschnitt aus dem relationalen Datenmodell)
Abb. 3-63c: Fahrradvermietung (Ausschnitt aus dem relationalen Datenmodell)
SQL
SELECT ...
FROM Mietvertrag_vermietet_Fahrrad
  INNER JOIN Fahrrad
    ON Fahrrad_ID = Fahrrad.ID
  INNER JOIN Mietvertrag
    ON Mietvertrag_ID = Mietvertrag.ID;
Abb. 3-64: SQL – Vernüpfung der Tabellen Mietvertrag_vermietet_Fahrrad, Fahrrad und Mietvertrag.

Zwischenergebnis 2

Mietvertrag_ID Fahrrad_ID Fahrrad.ID Kennung Fahrradtyp_ID Mietvertrag.ID Abschlussdatum Mietbeginn Mietdauer Kunde_ID
1 1 1 Cross#7 1 1 2015-08-11 2015-08-12 3 1
1 2 2 Mark#2 2 1 2015-08-11 2015-08-12 3 1
2 2 2 Mark#2 2 2 2015-08-13 2015-08-15 5 2
Abb. 3-64b: Zwischenergebnis nach der Verknüfung der Datensätze der Tabellen Mietvertrag_vermietet_Fahrrad, Fahrrad und Mietvertrag.

4. Schritt: Auswahl der gesuchten Spalten

SQL
SELECT Kennung, Abschlussdatum
FROM Mietvertrag_vermietet_Fahrrad
  INNER JOIN Fahrrad
    ON Fahrrad_ID = Fahrrad.ID
  INNER JOIN Mietvertrag
    ON Mietvertrag_ID = Mietvertrag.ID;
Abb. 3-64c: SQL – Für jeden Mietvertrag soll das Abschlussdatum und die Kennung des vermieteten Fahrrads ausgegeben werden.

Abfrageergebnis

Kennung Abschlussdatum
Cross#7 2015-08-11
Mark#2 2015-08-11
Mark#2 2015-08-13
Abb. 3-64d: Abfrageergebnis
Aufgabe

Aufgabe 3-10: Daten aus verschiedenen Tabellen abfragen

Datenbank Firmenwagen

Erstellen Sie jeweils eine SQL-Anweisung, die als Abfrageergebnis eine Tabelle mit den gewünschten Daten zurückgibt:

  1. Eine Übersicht aller Versicherungsverträge. Diese soll für jeden Versicherungsvertrag die Versicherungsnummer, das Kennzeichen des versicherten Fahrzeugs und den Namen der Versicherungsgesellschaft enthalten. Lösung
    Lösung
    SQL
    SELECT VersNr, Kennzeichen, Firma
    FROM Versicherungsvertrag
      INNER JOIN Fahrzeug
        ON Fahrzeug_ID = Fahrzeug.ID
      INNER JOIN Versicherungsgesellschaft
        ON Gesellschaft_ID = Versicherungsgesellschaft.ID;
    Abb. 3-66: SQL-Anweisung
  2. Eine Übersicht aller Reservierungen. Diese soll für jede Reservierung Beginn und Ende sowie das Kennzeichen und die Modellbezeichnung des reservierten Fahrzeugs enthalten. Lösung
    Lösung
    SQL
    SELECT Beginn, Ende, Kennzeichen, Bezeichnung
    FROM Reservierung
      INNER JOIN Fahrzeug
        ON Fahrzeug_ID = Fahrzeug.ID
      INNER JOIN Fahrzeugmodell
        ON Fahrzeugmodell_ID = Fahrzeugmodell.ID;
    Abb. 3-67: SQL-Anweisung
  3. Eine Übersicht aller Reservierungen. Diese soll für jede Reservierung Beginn und Ende sowie den Nachnamen des reservierenden Mitarbeiters und das Kennzeichen und die Modellbezeichnung des reservierten Fahrzeugs enthalten. Lösung
    Lösung
    SQL
    SELECT Beginn, Ende, Nachname, Kennzeichen, Bezeichnung
    FROM Reservierung
      INNER JOIN Mitarbeiter
        ON Mitarbeiter_ID = Mitarbeiter.ID
      INNER JOIN Fahrzeug
        ON Fahrzeug_ID = Fahrzeug.ID
      INNER JOIN Fahrzeugmodell
        ON Fahrzeugmodell_ID = Fahrzeugmodell.ID;
    Abb. 3-68: SQL-Anweisung
Aufgabe

Aufgabe 3-10 II: Daten aus verschiedenen Tabellen abfragen

Datenbank Fortbildung (SQL-Datei)
Fortbildung (Relationales Datenmodell)
Abb. 3-68b: Fortbildung (Relationales Datenmodell)

Erstellen Sie jeweils eine SQL-Anweisung, die als Abfrageergebnis eine Tabelle mit den gewünschten Daten zurückgibt:

  1. Eine Übersicht aller Seminare. Diese soll für jedes Seminar das Thema sowie den Namen des Seminarleiters enthalten. Lösung
    Lösung
    SQL
    SELECT Thema, Name
    FROM seminar
      INNER JOIN leiter
        ON seminar.L_Nr = leiter.L_Nr;
    Abb. 3-68c: SQL-Anweisung
  2. Eine Teilnehmerliste des Seminars Kundenwerbung. Diese soll Vor- und Nachnamen der Teilnehmer sowie das Seminarthema enthalten. Lösung
    Lösung
    SQL
    SELECT Vorname, Name, Thema
    FROM teilnehmer
      INNER JOIN belegung
        ON teilnehmer.T_Nr = belegung.T_Nr
      INNER JOIN seminar
        ON belegung.S_Nr = seminar.S_Nr;
    Abb. 3-68d: SQL-Anweisung
Aufgabe

Aufgabe 3-10 III: Daten aus verschiedenen Tabellen abfragen

Datenbank Fortbildung (SQL-Datei)
Jump and Ride (Relationales Datenmodell)
Abb. 3-68e: Fortbildung (Relationales Datenmodell)

Erstellen Sie jeweils eine SQL-Anweisung, die als Abfrageergebnis eine Tabelle mit den gewünschten Daten zurückgibt:

  1. Eine Übersicht aller Kurse. Diese soll für jeden Kurs die Bezeichnung und den Nachnamen des Surflehrers enthalten. Lösung
    Lösung
    SQL
    SELECT Bezeichnung, Name
    FROM kurs
      INNER JOIN surflehrer
        ON kurs.LehrerNr = surflehrer.LehrerNr;
    Abb. 3-68f: SQL-Anweisung
  2. Ein Liste aller Buchungen. Diese soll die Buchungsnummer, sowie die Anzahl der Betten des zugeordneten Zimmers enthalten. Lösung
    Lösung
    SQL
    SELECT BuchungsNr, Anz_Betten
    FROM buchung
      INNER JOIN zimmer
        ON buchung.ZNr = zimmer.ZNr;
    Abb. 3-68g: SQL-Anweisung
  3. Eine Übersicht, die zeigt, welches Surfbrett Kunden für den jeweils gebuchten Kurs zugeordnet ist. Angezeigt werden sollen der Nach- und Vorname des Kunden, die Kursnummer sowie die Bezeichnung des Surfbretts. Lösung
    Lösung
    SQL
    SELECT Name, Vorname, KursNr, Bezeichnung
    FROM kunde
      INNER JOIN buchung
        ON kunde.KNr = buchung.KNr
      INNER JOIN surfbrett
        ON buchung.BrettNr = surfbrett.BrettNr;
    Abb. 3-68h: SQL-Anweisung