Datenbank/Fortgeschrittene Jointechniken

Das vorliegende Kapitel sieht sich als Ergänzung und gleichzeitig Fortführung zur Einführung in Joins. Die dort vermittelten Kenntnisse werden hier vorausgesetzt. Zielgruppe beider Kapitel sind Quereinsteiger sowie Anfänger im Umgang mit relationalen Datenbanken.

Zwei Schwerpunkte werden behandelt: zum einen die Verknüpfung von mehr als zwei Tabellen, die sogenannten Mehrfachjoins, und zum anderen nichtalltägliche Spezialfälle wie der Selfjoin und der Thetajoin. Die vorgestellten Beispiele zeigen, dass in vielen Fällen etwas Logik (und grundlegende SQL-Kenntnisse) ausreichend sind, um viele Aufgaben zu lösen. Andererseits werden auch Fallstricke aufgezeigt, in denen man sich nach Möglichkeit nicht verheddern sollte.

Inhaltsverzeichnis

1 Die Beispieldatenbank
- 1.1 Besonderheiten
2 Der Selfjoin
- 2.1 1. Schritt - SELFJOIN
- 2.2 2. Schritt - SELFJOIN mit einschränkender WHERE-Klausel
3 Mehrere Tabellen mit JOIN verknüpfen - gleiche Joinspalten
4 Mehrere Tabellen mit JOIN verknüpfen - unterschiedliche Joinspalten
- 4.1 Unterschiedliche Join-Spalten nutzen
- 4.2 Ein Beispiel mit LEFT JOIN
5 Thetajoin
6 Dump der Beispieldatenbank

Die Beispieldatenbank

Die Beispiele in diesem Artikel beziehen sich alle auf die Tabellen der Beispieldatenbank, die hier vorgestellt wird. Die Beispieldatenbank ist weder vollständig, noch in sich geschlossen; am besten stellen Sie sich die Beispieldatenbank als Ausschnitt einer größeren Datenbank vor. Die Daten in den Tabellen sind sämtlich frei erfunden (mit Ausnahme der Kreditkartenanbieter), Ähnlichkeiten mit realen Daten können nur durch Zufall entstanden sein. Die Beispieldaten orientieren sich am Kapitel Einführung in Joins.

Tabelle Kunden
KndNr	Nachname	Vorname	Strasse	PLZ	Ort
123456	Mustermann	Max	Musterweg 1	12345	Musterstadt
123457	Musterfrau	Katrin	Musterstraße 7	12345	Musterstadt
123458	Müller	Lieschen	Beispielweg 3	23987	Irgendwo
123459	Schmidt	Hans	Hauptstraße 2	98765	Anderswo
123460	Becker	Heinz	Mustergasse 4	12543	Musterdorf

Tabelle Kreditkarten
KartenNr	Firma	KndNr	Ablaufdatum
12345	VISA	123457	05/2011
12346	Mastercard	123459	01/2012
12347	American Express	123459	01/2011
12348	Diners Club	123458	03/2012
12349	VISA	123458	07/2011

Tabelle Bestellungen_Oktober
KndNr	BestellungsNr	Datum
123456	987654	2009-10-15
123456	987655	2009-10-16
123457	987656	2009-10-16

Tabelle Positionen
PositionsNr	BestellungsNr	Artikel	Anzahl	Preis
10241	987654	CD-Player	2	49.95
10242	987654	DVD-Player	3	59.95
10243	987654	CD xyz	10	15.95
10244	987654	DVD abc	5	9.95
10245	987655	CD-Player	1	51.20
10246	987655	CD xyz extra	20	16.25
10247	987656	DVD-Player	1	64.95

Tabelle Vorteilsclub
KndNr	ClubNr	Kategorie
123458	1214	3
123456	1415	1
123460	1616	1

Die vorliegenden Tabellen seien der Ausschnitt aus der Verwaltung eines Unternehmens:

Die Tabelle 'Kunden' ist die zentrale Tabelle, in der Details zu den Kunden gespeichert sind.
In der Tabelle 'Kreditkarte' werden alle dem Unternehmen bekannten Kreditkarteninformationen abgelegt, man denke z. B. an Amazon.com, sodass bei einem zukünftigen Einkauf die Informationen als Vorbefüllung des Bestellformulares genutzt werden können.
Die Tabelle 'Bestellungen_Oktober' umfasst nur einen Monat und ordnet einer bestimmte Bestellung den Kunden und das Datum zu.
Die Tabelle 'Vorteilsclub' enthält Informationen über die Mitglieder des Vorteilsclubs, der bei Bestellungen zu Sonderkonditionen berechtigt. Diese sind von der Kategorie, in der sich der Kunde befindet, abhängig.
Sämtliche Tabellen können über die Spalte KndNr, die Kundennummer, zusammengeführt werden.
Die Tabelle 'Positionen' enthält Detailinformationen zu den Bestellungen, sie kann mit der Tabelle 'Bestellungen_Oktober' über die Spalte BestellungsNr verknüpft werden.

Besonderheiten

Nicht jeder Kunde muss über eine Kreditkarte verfügen, aber Kunden können Kreditkarten mehrerer Institute angeben. Entsprechend muss nicht von jedem Kunden im Oktober eine Bestellung vorliegen, nicht jeder Kunde ist Mitglied im Vorteilsclub.

Der Selfjoin

Sie können eine Tabelle auch mit sich selbst verknüpfen, diesen Typ des Joins nennt man Selfjoin.

Sie möchten z. B. für eine spezielle Marketingaktion wissen, welche Kunden sowohl über eine Mastercard als auch eine Kreditkarte von American Express verfügen. Ein Selfjoin kann Ihnen helfen, die gewünschten Daten zu erhalten.

1. Schritt - SELFJOIN

Im ersten Schritt verknüpfen Sie die Tabelle mit sich selbst, als Verknüpfungsfeld wählen Sie KdnNr, die Kundennummer:

für einen Selfjoin

SELECT
    KK1.KndNr,
    KK1.Firma,
    KK2.KndNr,
    KK2.Firma
FROM
    Kreditkarten KK1
INNER JOIN
    Kreditkarten KK2
ON
    KK1.KndNr = KK2.KndNr

Abfrageergebnis Selfjoin
KK1.KndNr	KK1.Firma	KK2.KndNr	KK2.Firma
123457	VISA	123457	VISA
123459	Mastercard	123459	Mastercard
123459	American Express	123459	Mastercard
123459	Mastercard	123459	American Express
123459	American Express	123459	American Express
123458	Diners Club	123458	Diners Club
123458	VISA	123458	Diners Club
123458	Diners Club	123458	VISA
123458	VISA	123458	VISA

(9 row(s) affected)

Sie greifen hier zweimal auf die gleiche Tabelle zu, die über die Spalte KndNr verknüpft sind. Um die beiden "Tabellen" voneinander zu unterscheiden verwenden Sie dazu Aliasnamen in der FROM- und JOIN-Klausel. Diese ermöglichen es Ihnen, in der Feldliste und in den Bedingungen die Herkunft der Daten anzugeben. Bei Kunden mit mehr als einer Kreditkarte werden alle möglichen Kombinationen angegeben - dies ist vergleichbar mit dem CROSS JOIN, siehe Einführung in Joins.

Im nächsten Schritt werden wir zusätzlich die Ergebnismenge filtern, um die gewünschten Datensätze zu erhalten:

2. Schritt - SELFJOIN mit einschränkender WHERE-Klausel

Nun möchten Sie nicht alle möglichen Kombinationen von Kundennummern und zugehörigen Kreditkarten haben, sondern wissen, wer sowohl eine 'Mastercard' und eine 'American Express' hat. Dazu nutzen Sie die WHERE-Klausel.

mit einschränkender WHERE-Klausel

SELECT
    KK1.KndNr,
    KK1.Firma,
    KK2.KndNr,
    KK2.Firma
FROM
    Kreditkarten KK1
INNER JOIN
    Kreditkarten KK2
ON
    KK1.KndNr = KK2.KndNr
WHERE
    KK1.Firma = 'Mastercard' AND KK2.Firma = 'American Express'

Abfrageergebnis Selfjoin mit WHERE-Klausel
KK1.KndNr	KK1.Firma	KK2.KndNr	KK2.Firma
123459	Mastercard	123459	American Express

(1 row(s) affected)

Die WHERE-Klausel filtert aus der Vereinigungstabelle genau die Datensätze aus, die Sie haben möchten. Es bleibt genau ein Datensatz übrig, der Kunde mit der KndNr 123459. Wenn Sie Details zu diesem Kunden haben möchten, so benötigen Sie einen weiteren JOIN mit der Kundentabelle. JOINs über mehrere Tabellen werden im folgenden Abschnitt behandelt.

Der Selfjoin kann unperformant sein, insbesondere wenn es in den Join-Spalten viele Werte gibt, die mehrfach auftreten. Spalten, deren Werte man nicht benötigt, sollte man normalerweise nicht in der Spaltenliste aufführen.

Mehrere Tabellen mit JOIN verknüpfen - gleiche Joinspalten

Ein einfaches Beispiel

Im vorhergehenden Abschnitt haben Sie für Ihre Marketingabteilung die Kundennummern der Kunden herausgefunden, die sowohl über eine 'Mastercard' als auch eine 'American Express' verfügen. Nun möchte die Marketingabteilung wissen, um welche Person es sich handelt, d. h. Nachname, Vorname, Anschrift, … Diese Aufgabe können Sie mit einer SQL-Anweisung lösen, indem Sie einen Mehrfachjoin verwenden:

Beispiel

SELECT
    KK1.KndNr,
    Nachname,
    Vorname,
    Strasse,
    PLZ,
    Ort
FROM (
    Kreditkarten KK1
INNER JOIN 
    Kreditkarten KK2
ON 
    KK1.KndNr = KK2.KndNr
)
INNER JOIN 
    Kunden
ON 
    KK1.KndNr = Kunden.KndNr
WHERE 
    KK1.Firma = 'Mastercard' AND KK2.Firma = 'American Express'

Abfrageergebnis mit Detailangaben
KndNr	Nachname	Vorname	Strasse	PLZ	Ort
123459	Schmidt	Hans	Hauptstraße 2	98765	Anderswo

(1 row(s) affected)

Aus der Anweisung vom vorhergehenden Beispiel wurden die nicht benötigten Spalten (2x Firma, KK2.KndNr) gestrichen, siehe auch die dortige Abschlussbemerkung. Durch den zweiten INNER JOIN wird das Ergebnis der ersten JOIN-Operation mit der Tabelle "Kunden" verknüpft. Wiederum dient die Spalte KndNr zur Verknüpfung. Ihre Marketingabteilung ist glücklich, sie hat genau die benötigten Daten für das geplante Mailing.

Beachten Sie: Die SQL-Syntax weist klare Richtlinien für die Reihenfolge der einzelnen Klauseln auf. Die WHERE-Klausel kommt (lange) nach der JOIN-Klausel. Deswegen kann die zweite JOIN-Klausel nicht einfach an die bisherige Anweisung angehängt werden.

Bei beiden JOIN-Operationen im vorliegenden Beispiel handelt es sich jeweils um einen INNER JOIN. Wie Sie dem Grundlagenartikel von Rouven Thimm entnehmen können, ist beim INNER JOIN die Reihenfolge der Operanden, d. h. der am JOIN beteiligten Tabellen irrelevant. D.h. statt FROM (Kreditkarten AS KK1 INNER JOIN Kreditkarten AS KK2) INNER JOIN Kunden hätte man genauso gut FROM Kunden INNER JOIN (Kreditkarten AS KK1 INNER JOIN Kreditkarten KK2) schreiben können, ON-Klausel mal vernachlässigt. Da KK1.KndNr den gleichen Wert besitzt wie KK2.KndNr ist es auch unwichtig, welche dieser beiden Spalten für die äußere JOIN-Bedingung verwendet wird. Die Klammern (hinter FROM und nach der ersten ON-Klausel) legen die Reihenfolge der JOIN-Operationen fest. Verzichten Sie auf Klammern, so entscheidet das Datenbankmanagementsystem (DBMS), in welcher Reihenfolge es die JOINs abarbeitet.

Ein etwas komplexerer Fall

Die Marketingabteilung hat schon wieder einen Auftrag für Sie: Diesmal benötigt sie die Adressen von allen Kunden, von den Kreditkarteninhabern zusätzlich die Kreditkarteninformationen aber nur, wenn sie Mitglied im Vorteilsclub sind. Ihnen ist klar, dass Sie für die Lösung dieser Aufgabe die Tabellen 'Kunden', 'Kreditkarten' und 'Vorteilsclub' miteinander verknüpfen müssen:

2 - erster Zwischenschritt

In einem ersten Zwischenschritt ermitteln Sie die Kreditkarteninformationen der Mitglieder des Vorteilsclub:

SELECT
    Kreditkarten.KndNr,
    Firma,
    KartenNr,
    Ablaufdatum
FROM 
    Kreditkarten  
INNER JOIN 
    Vorteilsclub
ON 
    Kreditkarten.KndNr = Vorteilsclub.KndNr

Kreditkarteninformationen der Mitglieder des Vorteilsclub
KndNr	Firma	KartenNr	Ablaufdatum
123458	Diners Club	12348	03/2012
123458	VISA	12349	07/2011

(2 row(s) affected)

Der Kunde muss über eine Kreditkarte verfügen, somit seine Kundennummer in der Tabelle "Kreditkarten" aufgeführt sein; gleiches gilt für den Vorteilsclub. Somit liefert der INNER JOIN dieser beiden Tabellen die Kreditkarteninformationen der Mitglieder im Vorteilsclub. In diesem Fall ist es ein einziger Kunde, der über zwei Kreditkarten verfügt.

Beachten Sie: Obwohl Sie überhaupt keine Spalte aus der Tabelle "Vorteilsclub" in der Ergebnistabelle verwenden, wird die Ergebnismenge durch den JOIN bereits eingeschränkt.

Resultierende Abfrage

Das Ergebnis dieses Zwischenschrittes müssen Sie nun noch auf geeignete Weise mit der Tabelle "Kunden" verknüpfen. Da sie von sämtlichen Kunden die Detaildaten von "Kunden" benötigen, auch wenn keine Entsprechungen in der Menge der Kreditkarteninformationen (der Vorteilsclubmitglieder) vorliegen, können Sie diese Aufgabe z. B. mit einem LEFT JOIN der Tabellen "Kunden" und dem Zwischenergebnis lösen:

Beispiel

SELECT
    Kunden.KndNr,
    Nachname,
    Vorname,
    Strasse,
    PLZ,
    Ort,
    Firma,
    KartenNr,
    Ablaufdatum
FROM 
    Kunden
LEFT JOIN (
    Kreditkarten  
INNER JOIN 
    Vorteilsclub
ON 
    Kreditkarten.KndNr = Vorteilsclub.KndNr
)
ON 
    Kunden.KndNr = Kreditkarten.KndNr

Kunden und Kreditkarteninformationen der Vorteilsclubmitglieder
KndNr	Nachname	Vorname	Straße	PLZ	Ort	Firma	KartenNr	Ablaufdatum
123456	Mustermann	Max	Musterweg 1	12345	Musterstadt	NULL	NULL	NULL
123457	Musterfrau	Katrin	Musterstraße 7	12345	Musterstadt	NULL	NULL	NULL
123458	Müller	Lieschen	Beispielweg 3	23987	Irgendwo	Diners Club	12348	03/2012
123458	Müller	Lieschen	Beispielweg 3	23987	Irgendwo	VISA	12349	07/2011
123459	Schmidt	Hans	Hauptstraße 2	98765	Anderswo	NULL	NULL	NULL
123460	Becker	Heinz	Mustergasse 4	12543	Musterdorf	NULL	NULL	NULL

(6 row(s) affected)

Im ersten Schritt erhalten Sie durch den INNER JOIN der Tabellen "Kreditkarten" und "Vorteilsclub" eine Tabelle derjenigen Kunden (genauer Kundennummern der Kunden), die sowohl Kreditkarteninhaber sind als auch dem Vorteilsclub angehören. Die Aufgabe, die Adressdaten aller Kunden, die Kreditkarteninformationen nur des gewünschten Kundenkreises auszugeben, wird vom äußeren LEFT JOIN der Tabelle "Kunden" mit dem Ergebnis der inneren Verknüpfung erledigt.

Variation der Reihenfolge und Joinspalte

Sobald ein LEFT oder RIGHT JOIN im Spiel ist, ist die Reihenfolge der Tabellen, die Auswahl der Joinspalten, ja sogar der Join-Operationen von Relevanz. Zur Illustration dieser Tatsache werden nun Variationen der gleichen Join-Operationen vorgestellt, die alle eines gemeinsam haben: sie liefern nicht das gewünschte Ergebnis.

3

Eine einfache Vertauschung der beiden "Partner" des äußeren LEFT JOIN führt dazu, dass viele gewünschte Datensätze verloren gehen:

SELECT
    Kunden.KndNr,
    Nachname,
    Vorname,
    Strasse,
    PLZ,
    Ort,
    Firma,
    KartenNr,
    Ablaufdatum
FROM (
    Kreditkarten  
INNER JOIN 
    Vorteilsclub
ON 
    Kreditkarten.KndNr = Vorteilsclub.KndNr
)
LEFT JOIN 
    Kunden
ON 
    Kunden.KndNr = Kreditkarten.KndNr

Abfrageergebnis
KndNr	Nachname	Vorname	Straße	PLZ	Ort	Firma	KartenNr	Ablaufdatum
123458	Müller	Lieschen	Beispielweg 3	23987	Irgendwo	Diners Club	12348	03/2012
123458	Müller	Lieschen	Beispielweg 3	23987	Irgendwo	VISA	12349	07/2011

(2 row(s) affected)

Das Verhalten des LEFT JOIN ist hier im Mehrfachjoin ganz analog zum Verhalten beim einfachen LEFT JOIN, wie in Rouven Thimms Artikel beschrieben. Beim LEFT JOIN spielt die Reihenfolge der beteiligten Tabellen eine Rolle. Dies gilt auch dann, wenn eine der beteiligten Tabellen das Ergebnis einer weiteren JOIN-Operation ist.

4

Im nächsten Beispiel wird die Reihenfolge der Operationen verändert: zuerst ein LEFT JOIN von "Kunden" und "Kreditkarten", anschließend ein INNER JOIN mit dem Vorteilsclub.

SELECT
    Kunden.KndNr,
    Nachname,
    Vorname,
    Strasse,
    PLZ,
    Ort,
    Firma,
    KartenNr,
    Ablaufdatum
FROM (
    Kunden
LEFT JOIN 
    Kreditkarten  
ON 
    Kunden.KndNr = Kreditkarten.KndNr)
INNER JOIN 
    Vorteilsclub
ON 
    Kreditkarten.KndNr = Vorteilsclub.KndNr

Abfrageergebnis
KndNr	Nachname	Vorname	Straße	PLZ	Ort	Firma	KartenNr	Ablaufdatum
123458	Müller	Lieschen	Beispielweg 3	23987	Irgendwo	Diners Club	12348	03/2012
123458	Müller	Lieschen	Beispielweg 3	23987	Irgendwo	VISA	12349	07/2011

(2 row(s) affected)

Das Ergebnis ist das gleiche wie im vorhergehenden Beispiel, was an den vorhandenen Daten liegt. Im allgemeinen Fall können aus beiden Abfragen unterschiedliche Ergebnistabellen resultieren. Zuerst werden jedem Kunden Kreditkarteninformationen zugeordnet, so welche vorhanden sind. Der INNER JOIN filtert die Kombinationen aus, die auch Mitglied im Vorteilsclub sind.

5

Im nächsten Beispiel ändern wir nur ein Detail gegenüber dem vorhergehenden: Statt die Kundennummer der Tabelle "Kreditkarten" für den INNER JOIN zu verwenden, wird die Spalte der Tabelle "Kunden" verwendet:

SELECT
    Kunden.KndNr,
    Nachname,
    Vorname,
    Strasse,
    PLZ,
    Ort,
    Firma,
    KartenNr,
    Ablaufdatum
FROM (
    Kunden
LEFT JOIN 
    Kreditkarten  
ON 
    Kunden.KndNr = Kreditkarten.KndNr
)
INNER JOIN 
    Vorteilsclub
ON 
    Kunden.KndNr = Vorteilsclub.KndNr

Kunden und Kreditkarteninformationen der Vorteilsclubmitglieder
KndNr	Nachname	Vorname	Straße	PLZ	Ort	Firma	KartenNr	Ablaufdatum
123456	Mustermann	Max	Musterweg 1	12345	Musterstadt	NULL	NULL	NULL
123458	Müller	Lieschen	Beispielweg 3	23987	Irgendwo	Diners Club	12348	03/2012
123458	Müller	Lieschen	Beispielweg 3	23987	Irgendwo	VISA	12349	07/2011
123460	Becker	Heinz	Mustergasse 4	12543	Musterdorf	NULL	NULL	NULL

(4 row(s) affected)

Bei der Verwendung eines LEFT/RIGHT JOIN und Weiternutzung des Zwischenergebnisses dieser Operation in einem weiteren JOIN mit gemeinsamer JOIN-Spalte ist es von Belang, welche der beiden Spalten man für den weiteren JOIN nutzt. Sinnvollerweise nutzt man in solchen Fällen die Spalte, die keine NULL-Werte enthalten.

Zusammenfassung

Mehrfachjoins sind nützliche Techniken in Datenbankanwendungen. Dieser Abschnitt zeigte die grundlegende Vorgehensweise zum Verständnis des Mehrfachjoins auf. Zerlegen Sie das Problem in einzelne JOIN-Operationen. Da das Ergebnis einer JOIN-Operation wiederum eine Tabelle ist, verwenden Sie ein solches Ergebnis wie eine Tabelle in einer weiteren JOIN-Operation.

Sobald LEFT oder RIGHT JOINs in einem Mehrfachjoin auftreten, ist die Reihenfolge von Relevanz. Dies wurde an ausgewählten Beispielen demonstriert. Bereits bei nur drei beteiligten Tabellen mit gemeinsamer JOIN-Spalte und nur den Operationen INNER JOIN und LEFT JOIN gibt es insgesamt 45 verschiedene Möglichkeiten einen Mehrfachjoin zu formulieren mit 16 verschiedenen Ergebnissen. Diese im einzelnen auch nur aufzuführen, würde den Rahmen dieses Artikels sprengen, die ständige Wiederholung fast gleicher SQL-Anweisungen wäre ebenso anstrengend wie langweilig.

Die Analyse der Aufgabenstellung, d. h. welche Datensätze sollen zurückgegeben werden, führt jedoch meist schnell zum richtigen Ergebnis, wie die ersten beiden Beispiele in diesem Abschnitt aufzeigen. In vielen Fällen sind beim Mehrfachjoin die Tabellen nicht über eine einzige gemeinsame Spalte verknüpft, sondern über unterschiedliche Spalten. Der nächste Abschnitt beschäftigt sich mit dieser Aufgabenstellung.

Mehrere Tabellen mit JOIN verknüpfen - unterschiedliche Joinspalten

Unterschiedliche Join-Spalten nutzen

Ihr Chef will eine Übersicht über die im Monat Oktober bestellten Artikel haben, mit den Detailinformationen zu den Kunden, die diese Bestellungen getätigt haben. Die benötigten Daten verteilen sich auf die Tabellen 'Kunden', 'Bestellungen_Oktober' und 'Positionen'. Diese drei Tabellen sind über unterschiedliche Spalten miteinander verknüpft: 'Kunden' und 'Bestellungen_Oktober' über die Spalte 'KndNr', 'Bestellungen_Oktober' und Positionen über die Spalte 'BestellungsNr'. Somit gibt es nur zwei mögliche Reihenfolgen, die Sie in Betracht ziehen können:

Erst die Tabellen 'Kunden' und 'Bestellungen_Oktober' miteinander verknüpfen, anschließend das Resultat mit 'Positionen',
erst 'Positionen' mit 'Bestellungen_Oktober' verknüpfen, dann das Resultat mit 'Kunden'.

Sie sehen, es macht die Aufgabe einfacher, wenn die Joinspalten unterschiedlich sind.

Lösung der Aufgabe

SELECT
    Artikel,
    Anzahl,
    Preis,
    Datum,
    Nachname,
    Vorname,
    Strasse,
    PLZ,
    Ort
FROM 
    Positionen 
INNER JOIN (
    Bestellungen_Oktober
INNER JOIN 
    Kunden
ON 
    Bestellungen_Oktober.KndNr = Kunden.KndNr
)
ON  
    Positionen.BestellungsNr = Bestellungen_Oktober.BestellungsNr

Bestellungen im Oktober mit Kundendetails
Artikel	Anzahl	Preis	Datum	Nachname	Vorname	Strasse	PLZ	Ort
CD-Player	2	49.95	2009-10-15	Mustermann	Max	Musterweg 1	12345	Musterstadt
DVD-Player	3	59.95	2009-10-15	Mustermann	Max	Musterweg 1	12345	Musterstadt
CD xyz	10	15.95	2009-10-15	Mustermann	Max	Musterweg 1	12345	Musterstadt
DVD abc	5	9.95	2009-10-15	Mustermann	Max	Musterweg 1	12345	Musterstadt
CD-Player	1	51.20	2009-10-16	Mustermann	Max	Musterweg 1	12345	Musterstadt
CD xyz extra	20	16.25	2009-10-16	Mustermann	Max	Musterweg 1	12345	Musterstadt
DVD-Player	1	64.95	2009-10-16	Musterfrau	Katrin	Musterstraße 7	12345	Musterstadt

(7 row(s) affected)

Da Sie die Bestellpositionen aller im Oktober bestellten Artikel benötigen, käme für die Verknüpfung zwischen 'Bestellungen_Oktober' und 'Positionen' neben dem hier verwendeten INNER JOIN auch ein LEFT JOIN in Frage. Bestellungen ohne Bestellpositionen sind jedoch uninteressant (würden Sie eine Bestellung, bei der nichts bestellt wird, bearbeiten?), deswegen verlieren Sie durch den INNER JOIN keine Informationen. Ebenso uninteressant sind Bestellungen ohne Besteller, deswegen wird auch für diese Verknüpfung der INNER JOIN verwendet. Bei einer Abfrage, die nur INNER JOINs verwendet, spielt die Reihenfolge der JOIN-Operationen für das Endergebnis keine Rolle. In dieser Hinsicht können Sie somit keinen Fehler machen.

Ein Beispiel mit LEFT JOIN

Nun möchte Ihr Chef eine Übersicht über alle Kunden mit den Artikeln, die diese bestellt haben. Auch Kunden ohne Bestellungen sollen mit Vor- und Nachname aufgeführt werden; diese Spalten sollen vorne stehen.

Erweiterte Aufgabe mit LEFT JOIN

SELECT
    Nachname,
    Vorname,
    Artikel,
    Anzahl,
    Preis,
    Datum
FROM 
    Kunden 
LEFT JOIN (
    Bestellungen_Oktober
INNER JOIN 
    Positionen
ON 
    Positionen.BestellungsNr = Bestellungen_Oktober.BestellungsNr
)
ON 
    Kunden.KndNr = Bestellungen_Oktober.KndNr

Alle Kunden, ggf. mit bestellten Artikeln
Nachname	Vorname	Artikel	Anzahl	Preis	Datum
Mustermann	Max	CD-Player	2	49.95	2009-10-15
Mustermann	Max	DVD-Player	3	59.95	2009-10-15
Mustermann	Max	CD xyz	10	15.95	2009-10-15
Mustermann	Max	DVD abc	5	9.95	2009-10-15
Mustermann	Max	CD-Player	1	51.20	2009-10-16
Mustermann	Max	CD xyz extra	20	16.25	2009-10-16
Musterfrau	Katrin	DVD-Player	1	64.95	2009-10-16
Müller	Lieschen	NULL	NULL	NULL	NULL
Schmidt	Hans	NULL	NULL	NULL	NULL
Becker	Heinz	NULL	NULL	NULL	NULL

(10 row(s) affected)

Wenn Sie aus einer Tabelle alle Datensätze benötigen, so ist diese Tabelle ein guter Kandidat für die äußere Verknüpfung, d. h. den JOIN, der als letzter abgearbeitet wird. Da Sie alle Datensätze benötigen, auch wenn es keine Entsprechungen in anderen Tabellen gibt, bietet sich die Verwendung des LEFT JOIN an. Die weitere Argumentation ist die gleiche wie beim vorhergehenden Beispiel, da sich diese Aufgabe nur in diesem Punkt von diesem unterscheidet.

Zusammenfassung

Diese Variante des Mehrfachjoins ist sehr häufig. Die meisten aktuellen relationalen DBMS können n:m-Beziehungen zwischen zwei Tabellen nur mit Hilfe einer Verknüpfungstabelle, also nicht direkt darstellen. In unserem Fall: Ein Kunde kann mehrere Artikel bestellen, der gleiche Artikel kann von mehreren Kunden bestellt werden. Das Auflösen solcher Verknüpfungstabellen erfolgt typischerweise über zwei Joins, die unterschiedliche Spalten verwenden. Sie haben gesehen, dass solche Fälle meist einfacher zu handhaben sind als Mehrfachjoins, die nur eine einzige gemeinsame Spalte nutzen.

Noch komplexere Verknüpfungen von mehr als drei Tabellen lassen sich prinzipiell auf die gleiche Methode lösen, indem Sie das Gesamtproblem schrittweise in einzelne JOIN-Operationen zerlegen. Bedenken Sie aber auch, dass sich nicht jede Aufgabenstellung mit einer einzigen Anweisung lösen lassen muss.

Thetajoin

Bisher haben wir in allen Beispielen in der JOIN-Bedingung nur den Gleichheitsoperator "=" verwendet. Der Thetajoin ist eine Verallgemeinerung dieses Falles, als Bedingung können auch andere Operatoren, z. B. die Vergleichsoperatoren verwendet werden. Der Equijoin (Bedingung: Gleichheit) ist ein Spezialfall des Thetajoins.

1: Eine Einfachrunde

Sie haben eine Tabelle mit Teams und möchten diese in einer Einfachrunde gegeneinander antreten lassen (Das Heimrecht sei irrelevant). Ein Thetajoin liefert Ihnen alle Begegnungen:

Tabelle Teams
T_ID	Team
1	Hamburg
2	München
3	Berlin
4	Köln

SELECT
    h.Team AS Heim,
    g.Team AS Gast
FROM 
    Teams h
INNER JOIN 
    Teams g
ON 
    h.T_ID > g.T_ID

Abfrage der Begegnungen
Heim	Gast
München	Hamburg
Berlin	Hamburg
Köln	Hamburg
Berlin	München
Köln	München
Köln	Berlin

(6 row(s) affected)

Genauso wie Sie Aliasnamen für Tabellen anlegen können, wie im Abschnitt Selfjoin vorgestellt, können Sie mit AS unterschiedliche Spaltenüberschriften erzeugen. Dies ist insbesondere dann nützlich, wenn zweimal auf gleichnamige Spalten zugegriffen wird. Der Selfjoin ermöglicht die Auswahl beider am Spiel beteiligten Mannschaften aus der Tabelle "Teams"; der Vergleichsoperator > sorgt dafür, dass es nicht zu Begegnungen einer Mannschaft mit sich selbst kommt und jede Mannschaft nur einmal auf einen bestimmten Gegner trifft.

2: Doppelrunde

Für eine Doppelrunde mit Hin- und Rückspiel nehmen Sie einfach den Ungleichheitsoperator <>:

SELECT
    h.Team AS Heim,
    g.Team AS Gast
FROM 
    Teams h
INNER JOIN 
    Teams g
ON 
    h.T_ID <> g.T_ID

Doppelrunde
Heim	Gast
München	Hamburg
Berlin	Hamburg
Köln	Hamburg
Hamburg	München
Berlin	München
Köln	München
Hamburg	Berlin
München	Berlin
Köln	Berlin
Hamburg	Köln
München	Köln
Berlin	Köln

(12 row(s) affected)

Abschließende Bemerkung:

Thetajoins sind selten. Wenn sie auftreten, dann häufig wie hier in den Beispielen in Kombination mit dem Selfjoin.

Dump der Beispieldatenbank

SQL-Dump zum Einlesen in die Datenbank mit mysql -u USER -p DATENBANK < datei.sql

# CREATE DATABASE  IF NOT EXISTS `selfhtml2`;
# USE `selfhtml2`;

DROP TABLE IF EXISTS `Bestellungen_Oktober`;
CREATE TABLE `Bestellungen_Oktober` (
  `KndNr` INT(11) NOT NULL,
  `BestellungsNr` INT(11) NOT NULL AUTO_INCREMENT,
  `Datum` DATE NOT NULL,
  PRIMARY KEY (`BestellungsNr`),
  KEY `KndNr` (`KndNr`),
  KEY `Datum` (`Datum`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
LOCK TABLES `Bestellungen_Oktober` WRITE;
INSERT INTO `Bestellungen_Oktober` VALUES
(123456, 987654, '2014-10-15'),
(123456, 987655, '2014-10-16'),
(123457, 987656, '2014-10-16');
UNLOCK TABLES;

DROP TABLE IF EXISTS `Kreditkarten`;
CREATE TABLE `Kreditkarten` (
  `KartenNr` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `Firma` tinytext COLLATE utf8_bin NOT NULL,
  `KndNr` BIGINT(20) NOT NULL,
  `Ablaufdatum` DATE NOT NULL,
  PRIMARY KEY (`KartenNr`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
LOCK TABLES `Kreditkarten` WRITE;
INSERT INTO `Kreditkarten` VALUES
(12345, 'VISA', 123457, '2019-05-01'),
(12346, 'Mastercard', 123459, '2020-01-01'),
(12348, 'American Express', 123459, '2019-05-01'),
(12349, 'Diners Club', 123458, '2022-02-01'),
(12350, 'VISA', 123458, '2017-03-01');
UNLOCK TABLES;

DROP TABLE IF EXISTS `Kunden`;
CREATE TABLE `Kunden` (
  `KndNr` BIGINT(20) NOT NULL,
  `Nachname` tinytext COLLATE utf8_bin NOT NULL,
  `Vorname` tinytext COLLATE utf8_bin NOT NULL,
  `Strasse` tinytext COLLATE utf8_bin NOT NULL,
  `PLZ` text COLLATE utf8_bin NOT NULL,
  `Ort` tinytext COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`KndNr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
LOCK TABLES `Kunden` WRITE;
INSERT INTO `Kunden` VALUES
(123456, 'Mustermann', 'Max', 'Musterweg 1', '12345', 'Musterstadt'),
(123457, 'Musterfrau', 'Katrin', 'Musterstraße 7', '12345', 'Musterstadt'),
(123458, 'Müller', 'Lieschen', 'Beispielweg 3', '23987', 'Irgendwo'),
(123459, 'Schmidt', 'Hans', 'Hauptstraße 2', '98765', 'Anderswo'),
(123460, 'Becker', 'Heinz', 'Mustergasse 4', '12543', 'Musterdorf');
UNLOCK TABLES;

DROP TABLE IF EXISTS `Positionen`;
CREATE TABLE `Positionen` (
  `PositionsNr` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `BestellungsNr` BIGINT(20) NOT NULL,
  `Artikel` tinytext COLLATE utf8_bin NOT NULL,
  `Anzahl` INT(11) NOT NULL,
  `Preis` DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`PositionsNr`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
LOCK TABLES `Positionen` WRITE;
INSERT INTO `Positionen` VALUES
(10241, 987654, 'CD-Player', 2, 49.95),
(10242, 987654, 'DVD-Player', 3, 59.95),
(10243, 987654, 'CD xyz', 10, 15.95),
(10244, 987654, 'DVD abc', 5, 9.95),
(10245, 987655, 'CD-Player', 1, 51.20),
(10246, 987655, 'CD xyz extra   ', 20, 16.25),
(10247, 987656, 'DVD-Player', 1, 64.95);
UNLOCK TABLES;

DROP TABLE IF EXISTS `Vorteilsclub`;
CREATE TABLE `Vorteilsclub` (
  `KndNr` BIGINT(20) NOT NULL,
  `ClubNr` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `Kategorie` tinyint(4) NOT NULL,
  PRIMARY KEY (`ClubNr`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
LOCK TABLES `Vorteilsclub` WRITE;
INSERT INTO `Vorteilsclub` VALUES
(123458, 1414, 3),
(123456, 1415, 1),
(123460, 1416, 1);
UNLOCK TABLES;

DROP TABLE IF EXISTS `Teams`;
CREATE TABLE `Teams` (
  `T_ID` INT(3) NOT NULL AUTO_INCREMENT,
  `Team` tinytext COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`T_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
LOCK TABLES `Teams` WRITE;
INSERT INTO `Teams` VALUES
(1, 'Hamburg'),
(2, 'München'),
(3, 'Berlin'),
(4, 'Köln');
UNLOCK TABLES;

Datenbank/Fortgeschrittene Jointechniken

Inhaltsverzeichnis

Die Beispieldatenbank

Besonderheiten

Der Selfjoin

1. Schritt - SELFJOIN

2. Schritt - SELFJOIN mit einschränkender WHERE-Klausel

Mehrere Tabellen mit JOIN verknüpfen - gleiche Joinspalten

Ein einfaches Beispiel

Ein etwas komplexerer Fall

Variation der Reihenfolge und Joinspalte

Zusammenfassung

Mehrere Tabellen mit JOIN verknüpfen - unterschiedliche Joinspalten

Unterschiedliche Join-Spalten nutzen

Ein Beispiel mit LEFT JOIN

Thetajoin

Dump der Beispieldatenbank

Navigationsmenü

Meine Werkzeuge

Namensräume

Varianten

Ansichten

Mehr

Suche

Starten

Mitmachen

Werkzeuge

SELFHTML