Datenbank/Fortgeschrittene Jointechniken

Aus SELFHTML-Wiki
Wechseln zu: Navigation, Suche

Der vorliegende Artikel sieht sich als Ergänzung und gleichzeitig Fortführung zur Einführung in Joins. Die dort vermittelten Kenntnisse werden hier vorausgesetzt. Zielgruppe beider Artikel sind Quereinsteiger sowie Anfänger im Umgang mit relationalen Datenbanken. Hostingangebote mit Datenbankunterstützung sind bereits seit mehreren Jahren Standard geworden, seit Jahren gibt es sogar Gratishosting mit Datenbankunterstützung. Entsprechend zahlreich sind die Anfragen im SELFHTML-Forum zu dieser Thematik.

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

[Bearbeiten] 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 - ist hier ein Hinweis auf Markenrechte erforderlich?), Ähnlichkeiten mit realen Daten können nur durch Zufall entstanden sein. Die Beispieldaten orientieren sich am Artikel Einführung in Joins.

[Bearbeiten] Tabellen

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

[Bearbeiten] Erläuterung

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, so dass bei einem zukünftigen Einkauf die Informationen als Vorbefüllung des Bestellformulares genutzt werden können.
  • Die Tabelle 'Bestellungen_Oktober' umfaßt 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.

[Bearbeiten] 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.


[Bearbeiten] 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.

[Bearbeiten] 1. Schritt - SELFJOIN

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

Beispiel: 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)
Erläuterung: 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:

[Bearbeiten] 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.

Beispiel: 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)
Erläuterung: 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.

[Bearbeiten] Abschlussbemerkung

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.

[Bearbeiten] Mehrere Tabellen mit JOIN verknüpfen - gleiche Joinspalten

[Bearbeiten] 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)
Erläuterung: 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 genausogut 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.

[Bearbeiten] 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:

Beispiel: 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)
Erläuterung: 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)
Erläuterung 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.

[Bearbeiten] 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.

Beispiel: 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)
Erläuterung: 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.
Beispiel: 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)
Erläuterung: Das Ergebnis ist das gleiche wie im vorhergenden 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.
Beispiel: 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)
Erläuterung: 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.

[Bearbeiten] 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.

[Bearbeiten] Mehrere Tabellen mit JOIN verknüpfen - unterschiedliche Joinspalten

[Bearbeiten] 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.

Beispiel: 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)
Erläuterung: 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.

[Bearbeiten] 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.

Beispiel: 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)
Erläuterung: 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.

[Bearbeiten] 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.

Beispiel: 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.
Beispiel: 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.

[Bearbeiten] Dump der Beispieldatenbank

Beispiel: 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;
Meine Werkzeuge
Namensräume

Varianten
Aktionen
Übersicht
Schnell‑Index
Mitmachen
Werkzeuge
Spenden
SELFHTML