Artikel:DBMS und SQL/Fortgeschrittene Jointechniken

Aus SELFHTML-Wiki

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.

[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

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

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

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

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'

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

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'

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:

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

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

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

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

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

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

[Bearbeiten] Zusammenfassung

Mehrfachjoins sind nützliche Tehniken 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 ein einziges gemeinsames 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.

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

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

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

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.

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

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

Abschließende Bemerkung:

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

Ansichten