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.
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
-
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.
-
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:
-
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. -
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 derON
-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.Abfrageergebnis
Abb. 3-55: Abfrageergebnis der SQL-Anweisung aus Abb. 3-54.
-
-
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.
Abfrageergebnis
Abb. 3-57: Abfrageergebnis der SQL-Anweisung aus Abb. 3-56.
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:
-
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
SQLSELECT Beginn, Ende, PersNr, Nachname
FROM Reservierung INNER JOIN Mitarbeiter ON Mitarbeiter_ID = Mitarbeiter.ID;Abb. 3-59: SQL-Anweisung -
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
SQLSELECT VersNr, Kennzeichen, Kasko
FROM Versicherungsvertrag INNER JOIN Fahrzeug ON Fahrzeug_ID = Fahrzeug.ID;Abb. 3-60: SQL-Anweisung -
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
SQLSELECT 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 -
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
SQLSELECT 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?
-
Tabelle Fahrrad
-
Tabelle Mietvertrag_vermietet_Fahrrad
-
Tabelle 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.
FROM Mietvertrag_vermietet_Fahrrad
INNER JOIN Fahrrad
ON Fahrrad_ID = Fahrrad.ID
...
Zwischenergebnis 1
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.
FROM Mietvertrag_vermietet_Fahrrad
INNER JOIN Fahrrad
ON Fahrrad_ID = Fahrrad.ID
INNER JOIN Mietvertrag
ON Mietvertrag_ID = Mietvertrag.ID;
Zwischenergebnis 2
4. Schritt: Auswahl der gesuchten Spalten
FROM Mietvertrag_vermietet_Fahrrad
INNER JOIN Fahrrad
ON Fahrrad_ID = Fahrrad.ID
INNER JOIN Mietvertrag
ON Mietvertrag_ID = Mietvertrag.ID;
Abfrageergebnis
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:
-
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
SQLSELECT 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 -
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
SQLSELECT 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 -
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
SQLSELECT 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 3-10 II: Daten aus verschiedenen Tabellen abfragen
Datenbank Fortbildung (SQL-Datei)
Erstellen Sie jeweils eine SQL-Anweisung, die als Abfrageergebnis eine Tabelle mit den gewünschten Daten zurückgibt:
-
Eine Übersicht aller Seminare. Diese soll für jedes Seminar das Thema sowie den Namen des Seminarleiters enthalten.
Lösung
Lösung
SQLSELECT Thema, Name
FROM seminar
INNER JOIN leiter
ON seminar.L_Nr = leiter.L_Nr;Abb. 3-68c: SQL-Anweisung -
Eine Teilnehmerliste des Seminars Kundenwerbung. Diese soll Vor- und Nachnamen der Teilnehmer sowie das Seminarthema enthalten.
Lösung
Lösung
SQLSELECT 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 3-10 III: Daten aus verschiedenen Tabellen abfragen
Datenbank Fortbildung (SQL-Datei)
Erstellen Sie jeweils eine SQL-Anweisung, die als Abfrageergebnis eine Tabelle mit den gewünschten Daten zurückgibt:
-
Eine Übersicht aller Kurse. Diese soll für jeden Kurs die Bezeichnung und den Nachnamen des Surflehrers enthalten.
Lösung
Lösung
SQLSELECT Bezeichnung, Name
FROM kurs
INNER JOIN surflehrer
ON kurs.LehrerNr = surflehrer.LehrerNr;Abb. 3-68f: SQL-Anweisung -
Ein Liste aller Buchungen. Diese soll die Buchungsnummer, sowie die Anzahl der Betten des zugeordneten Zimmers enthalten.
Lösung
Lösung
SQLSELECT BuchungsNr, Anz_Betten
FROM buchung
INNER JOIN zimmer
ON buchung.ZNr = zimmer.ZNr;Abb. 3-68g: SQL-Anweisung -
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
SQLSELECT 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