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
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.
| 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 |
| 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 |
| KndNr | BestellungsNr | Datum |
|---|---|---|
| 123456 | 987654 | 2009-10-15 |
| 123456 | 987655 | 2009-10-16 |
| 123457 | 987656 | 2009-10-16 |
| 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 |
| 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:
SELECT
KK1.KndNr,
KK1.Firma,
KK2.KndNr,
KK2.Firma
FROM
Kreditkarten KK1
INNER JOIN
Kreditkarten KK2
ON
KK1.KndNr = KK2.KndNr
| 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.
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'
| 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:
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'
| 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.
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:
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
| KndNr | Firma | KartenNr | Ablaufdatum |
|---|---|---|---|
| 123458 | Diners Club | 12348 | 03/2012 |
| 123458 | VISA | 12349 | 07/2011 |
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.
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:
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
| 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.
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
| 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.
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
| 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.
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
| 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.
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
| 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.
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
| 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.
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:
1: Eine Einfachrunde
| 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
| 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.}}
Für eine Doppelrunde mit Hin- und Rückspiel nehmen Sie einfach den Ungleichheitsoperator <>:
{{Beispiel|titel=2: Doppelrunde|
SELECT
h.Team AS Heim,
g.Team AS Gast
FROM
Teams h
INNER JOIN
Teams g
ON
h.T_ID <> g.T_ID
| 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
# 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;
