Join Datenbanken Beispiel Essay

Normalisierung & Vorstellung des Beispiels[Bearbeiten]

Bevor genauer auf Joins und Join-Arten eingegangen wird, soll zunächst das Problem vorgestellt bzw. eine Motivation für die künstliche Herbeiführung des Problems gegeben werden:
Frage: Warum liegen nicht alle Datensätze in einer Tabelle?
Antwort: Weil eine Normalisierung vorgenommen wurde.

Die Normalisierung ist ein Prozess der Umgestaltung von Datenbankstrukturen in der relationalen Welt. Ziel ist die Vermeidung zahlreicher Probleme, die sich aus der einfachen Speicherung „in einer großen Tabelle“ ergeben. Das Ergebnis der Normalisierung sind zahlreiche, kleinere Relationen, in denen weniger bis keine Abhängigkeiten zwischen einzelnen Feldern bestehen und Redundanzen nicht mehr auftreten.

Nicht normalisierte Tabelle[Bearbeiten]

Nachfolgend soll ein fiktiver Teil einer Unternehmenssoftware betrachtet werden. Das Unternehmen besitzt bereits eine Kundendatei, in der zu jedem Kunden eine eindeutige Kundennummer festgehalten ist. Die zu erstellende Datenlandschaft soll folgende Sachverhalte abdecken:

  1. Kunden kaufen bei dem Unternehmen verschiedene Waren ein. Der Gesamtrechnungsbetrag soll unter einer Rechnungsnummer zusammen mit der Kundennummer abgelegt werden.
  2. Falls ein Kunde die Rechnung per Kreditkarte begleicht, so sind die nötigen Kartendaten wie Nummer, Firma, Inhaber und Ablaufdatum festzuhalten. Für anderweitig beglichene Rechnungen entfallen diese Angaben.
  3. Um dem Kunden bei einem erneuten Einkauf die Eingabe der Kartendaten zu ersparen, müssen sämtliche bekannten Kreditkarteninformationen archiviert werden.
  4. Die Rechnungstabelle wird monatsweise geführt, d.h. sie enthält immer nur die Rechnungen eines Monats.

Beispiel: – Tabelle Rechnungen

RechnungsNrKundenNrBetragKartennummerFirmaInhaberAblaufdatum
98765ABX03949,9512345VISAMax Mustermann05/2007
98766ABX03912,95----
---12346MastercardKatrin Musterfrau01/2008
98767ABX04012347American ExpressJohn Doe01/2007
98768ABX05059,9912347American ExpressJohn Doe01/2007
98769ABX05029,9912348Diners ClubJohn Doe03/2008
98770ABX06099,99----

Die vorstehende Tabelle erfüllt die obigen Anforderungen. Sie ist allerdings zwangsläufig suboptimal, da sie sich mit zwei Sachverhalten gleichzeitig befasst. Zum einen hält sie fest, welcher Kunde unter welcher Rechnungsnummer welchen Betrag beglichen hat. Zum anderen speichert sie sämtliche Kreditkarteninformationen, die dem Unternehmen vorliegen. Rein logisch liegt zwischen beiden Informationen zunächst keine Beziehung vor.

Woran ist der Sachverhalt zu erkennen?

Man betrachte die Rechnung mit der Nummer . Keines der Felder , , oder ist befüllt (sog. NULL-Werte). In diesem Szenario könnte dies z.B. bedeuten, dass die entsprechende Rechnung noch nicht oder nicht mit einer Kreditkarte beglichen wurde. Betrachtet man weiterhin die Kartennummer , so zeigt sich die umgekehrte Situation: Es liegen keine Rechnungsinformationen vor.

Probleme dieses Ansatzes[Bearbeiten]

Der unerfahrene Designer mag diese Tabelle ansehen und sich fragen, wo denn nun das eigentliche Problem liegt, denn offensichtlich werden ja alle Daten geliefert, die das Unternehmen in diesem Zusammenhang benötigt. Der Hauptfeind dieses Modells sind Änderungen im Laufe der Zeit.

  • Man stelle sich beispielsweise vor, dass die Zahlungen von fehlschlagen, weil die Kreditkarteninformationen zu seiner American Express-Karte ungültig sind. Auf Rückfrage bestätigt der Kunde, dass das Ablaufdatum eigentlich nicht , sondern ist. Anstatt diese Information an nur einer Stelle zu ändern muss sichergestellt werden, dass bei sämtlichen (hier: 2; im Laufe der Zeit sicher deutlich mehr) Einträgen die Information geändert wird. Wird die Änderung nicht bei allen Sätzen durchgeführt oder wird lediglich bei neuen Rechnungen der richtige Eintrag vorgenommen, kann zu einem späteren Zeitpunkt keiner feststellen, welche der Angaben korrekt ist. Es kommt zur sog. Update-Anomalie.
  • Ein weiteres Problem ergibt sich, wenn z.B. nach einem Monat die Rechnungsinformationen gelöscht werden, weil das Unternehmen sie in dieser Form nicht mehr benötigt. Anstatt einfach alle entsprechenden Rechnungen zu löschen, müssen Konsistenzprüfungen ausgeführt werden, um nicht aus Versehen eine der Karteninformationen aus dem Bestand zu löschen. Geht eine Karteninformation (zwangsweise) verloren, nur weil die letzte Rechnung gelöscht wurde, spricht man von der Lösch-Anomalie.
  • Die Einfüge-Anomalie wurde im obigen Beispiel durch die unsaubere Lösung mit NULL-Werten bekämpft: Eine Karteninformation kann ohne Rechnung eigentlich nicht eingetragen werden, man behilft sich daher mit leeren Angaben.

All dies ist hinfällig, wenn die Datenbankstruktur nur leicht angepasst wird.

Normalisierte Tabellen[Bearbeiten]

Um den zuvor genannten Problemen aus dem Weg zu gehen, sollte eine Normalisierung der Daten angestrebt werden. Eine mögliche Normalisierung könnte etwa wie folgt aussehen:

Beispiel: – Normalisierte Tabellen

KartennummerFirmaInhaberAblaufdatum
12345VISAMax Mustermann05/2007
12346MastercardKatrin Musterfrau01/2008
12347American ExpressJohn Doe01/2007
12348Diners ClubJohn Doe03/2008
RechnungsNrKundenNrBetragKartennummer
98765ABX03949,9512345
98766ABX03912,95-
98767ABX04079,9512347
98768ABX05059,9912347
98769ABX05029,9912348
98770ABX06099,99-

In der nun vorliegenden Struktur wurde für jeden Sachverhalt eine eigene Tabelle gebildet. Über das eindeutige Feld, den sogenannten Primärschlüssel, in diesem Fall bzw. , kann ein Datensatz identifiziert werden. Zwischen der Tabelle und der Tabelle besteht eine sog. Fremdschlüsselbeziehung: Durch den Eintrag der Kartennummer kann einer Rechnung eine Kreditkarteninformation zugeordnet werden. Keiner der Einträge ist redundant, die zuvor genannten Anomalien sind beseitigt.

Gibt es Nachteile dieser Speicherform?[Bearbeiten]

Nachteile ergeben sich sowohl für einen menschlichen Leser als auch für die maschinelle Verarbeitung der Daten. Das menschliche Gehirn ist in der Lage, ohne weiteres relevante Informationen wahrzunehmen und irrelevantes auszublenden. Möchte der Mensch also alle Rechnungen sehen, so ist es ihm einigermaßen egal, ob er dabei einen Satz übergehen muss oder nicht. Erst bei zahlreichen NULL-Werten in der Ausgangstabelle wäre es ggf. umständlicher diese zu lesen. Bei den Kreditkarten verhält es sich schon anders, hier ist die normalisierte Tabelle in jedem Fall zugänglicher. Um in der Urtabelle herauszufinden, welche Karten bekannt sind, muss mühsam jede Rechnung betrachtet werden, nun existieren alle Karten getrennt. Knackpunkt bei der Umstrukturierung bleibt allerdings die reine Kernfunktionalität Welche Rechnung wurde mit welcher Kreditkarte beglichen?. In der Ausgangslage musste hierzu lediglich der Datensatz angeschaut werden. Nach der Umstrukturierung muss erstens die Rechnung angeschaut, zweitens die Kartennummer ermittelt und drittens die Karteninformation an anderer Stelle nachgeschlagen werden.

Was sich hier noch als Problem der menschlichen Lesbarkeit darstellt, birgt auch für die maschinelle Verarbeitung ein Problem: Zwar können alle Informationen wiederhergestellt werden, aber der Suchaufwand ist ungleich höher. Aus einer einzigen Abfrage (Gib mir alle Rechnungen mit Zahlungsinformation) sind entweder 2 Abfragen geworden (Gib mir alle Rechnungen, die mit Karte gezahlt wurden und Gib mir zu jeder dieser Kartennummern die Karteninformationen), oder die Tabellen müssen wieder in ihre Ausgangsform zusammengefügt werden. Dieser JOIN kann bei großen Tabellen sehr aufwändig werden. Bei zwei Tabellen ist das Problem noch nicht unmittelbar vorhanden, stellt man sich aber komplexere Beziehungen vor, so können die Operationen relativ viel Zeit beanspruchen. Bei großen Datenbanken wird daher nicht immer bis zur letzten Stufe normalisiert, sondern einzelne Anomalien akzeptiert und durch Programmlogik oder andere Datenbankmechanismen ausgeglichen.

Im Netz finden sich zahlreiche Seiten, die sich mit der Normalisierung von Tabellen befassen. Da dies nicht der eigentliche Inhalt dieses Artikels ist, sei dem interessierten Leser die Recherche selbst nahegelegt. Ein guter Startpunkt ist der Wikipedia-Artikel zur Normalisierung.

Joins allgemein[Bearbeiten]

Ein Join (zu Deutsch: Verbund oder Verbindung) dient der Zusammenführung von zwei Tabellen unter bestimmten Kriterien. Durch verschiedene Arten von Joins werden dabei zusätzlich zu den eigentlichen Kriterien noch Grundregeln für die Ergebnismenge festgelegt.

Joins basieren, wie relationale Datenbanksysteme insgesamt, auf dem Konstrukt der Relation in der Mathematik. In der Theorie existieren daher eine ganze Reihe von Joins, die im Datenbankalltag keine Rolle spielen. Um langsam an das Thema heranzuführen, sollen sie hier trotzdem genannt werden.

Join als Kreuzprodukt[Bearbeiten]

In der allereinfachsten Form des Joins werden sämtliche Datensätze der ersten Tabelle mit sämtlichen Datensätzen der zweiten Tabelle zusammengeführt, indem man das sogenannte Kreuzprodukt der Tabellen bildet.

X

Y

YAYBYC
123
234
345

X, Y

XAXBYAYBYC
12123
12234
12345
23123
23234
23345
34123
34234
34345

Für den praktischen Datenbankeinsatz ist diese Reinform in der Regel unbrauchbar, da keinerlei Beziehungen zwischen den Daten beachtet werden.

Equi-Join[Bearbeiten]

Während beim Kreuzprodukt keinerlei Anforderungen an die Kombination der Datensätze gestellt werden, führt der Equi-Join eine solche ein: Die Gleichheit von zwei Spalten.

X

Y

YAYBYC
123
234
345

X, Y mit XA=YA und XB=YB

XAXBYAYBYC
12123
23234
34345

Im Gegensatz zur ersten Variante sind hier also nur noch die Datensätze in der Ergebnismenge, die das Kriterium der Gleichheit erfüllt haben.

Natural Join[Bearbeiten]

Ein Natural Join ist eine Kombination von zwei Tabellen, in denen Spalten gleichen Namens existieren. Die Werte in diesen Spalten werden sodann auf Übereinstimmungen geprüft (analog Equi-Join), Das vorliegende Beispiel ist genau so gewählt, dass in beiden Relationen eine Spalte und eine Spalte existiert. Genau wie beim Equi-Join werden und geprüft. Im Anschluss an die Zusammenführung werden die Spalten paarweise zu einer einzigen - bzw. -Spalte zusammengefasst.

X

Y

Natural Join von X, Y

oder auch:

Einige Datenbanksysteme erkennen das Schlüsselwort NATURAL und eliminieren entsprechend automatisch doppelte Spalten.

Die in Datenbanken weiterhin gebräuchlichen Joins werden in nachfolgenden Abschnitten separat behandelt:

Syntax[Bearbeiten]

Datenbanksysteme unterstützen verschiedene Schreibweisen für Joins, die sich zum einen in ihrer Anwendbarkeit und zum anderen in der Lesbarkeit unterscheiden.

implizite Schreibweise:
SELECTX.a,X.b,Y.a,Y.b,cFROMX,Y
explizite Schreibweise:
SELECTX.a,X.b,Y.a,Y.b,cFROMXJOINY

Zunächst fällt auf, dass die Spalten und jeweils mit dem Tabellennamen angegeben werden. Der Grund liegt darin, dass das Datenbanksystem bei der Angabe nicht entscheiden kann, welche der Spalten gemeint ist, die aus Tabelle oder die aus Tabelle . Um die Doppeldeutigkeit aufzulösen, muss der Tabellenname angegeben werden.

Des Weiteren ist die implizite Schreibweise kürzer als die explizite. Sollen allerdings in einer Abfrage einmal mehrere Tabellen gejoint werden, kann es anschließend schwierig herauszufinden sein, welche Tabelle wann mit welcher und unter welchen Bedingungen gejoint wird. Deswegen wird oft die explizite Schreibweise empfohlen.

Platzierung von Kriterien[Bearbeiten]

In der Regel sollen für den Join Kriterien festgelegt werden (siehe z.B. Equi-Join weiter oben). Grundsätzlich besteht die Möglichkeit, diese Kriterien im Bereich der -Klausel einer Abfrage anzugeben.

Beispiel

SELECTcFROMX,YWHEREX.a=Y.aANDX.b=Y.b

Bei der expliziten Schreibweise ist daher auch die gezielte Angabe der Bedingungen anzustreben, zumal sie auch die Lesbarkeit der Abfrage erhöht.

Beispiel

SELECTcFROMXJOINYONX.a=Y.aANDX.b=Y.b

In allen bisherigen Beispielen waren die Spaltennamen, die als Kriterien dienen, identisch. Außerdem wurde die Gleichheit der Werte in beiden Spalten gefordert. Einige Datenbanksysteme bieten für diesen „Spezialfall“ auch eine gesonderte Schreibweise an:

Beispiel

SELECTcFROMXJOINYUSING(a,b)

Anmerkung: Sämtliche Beispiele haben eine Gleichheit der Spalten und gefordert. In der Praxis ist dies wohl auch die am häufigsten benötigte Variante. Es spricht allerdings nichts dagegen, beispielsweise einen Join auszuführen.

Mehrfachjoins[Bearbeiten]

Ein einzelner Join führt immer genau zwei Tabellen zusammen. Die Namen LEFT (OUTER) JOIN bzw. RIGHT (OUTER) JOIN sowie die nachfolgend verwendete Bezeichnung linke bzw. rechte Tabelle beziehen sich auf die Reihenfolge, in der die Tabellen am Join beteiligt sind. Bei ist die linke, die rechte Tabelle.

Beispiel: implizite Schreibweise

SELECTa,b,c,d,eFROMX,Y,Z

Beispiel: explizite Schreibweise

SELECTa,b,c,d,eFROMXJOINYJOINZ

Beide Schreibweisen können zu dem Eindruck führen, es würden mehr als zwei Tabellen gejoint. In Wahrheit ist dies nicht der Fall. Der Ausdruck wird i.d.R. von links nach rechts abgearbeitet, so dass zunächst und gejoint werden. Das Ergebnis dieses Joins wird sodann mit gejoint. Dies sollte man im Hinterkopf haben, da die Reihenfolge der Tabellen und Platzierung von Kriterien sowohl die Performance als auch das Ergebnis beeinflussen können. Zum Thema Mehrfach-JOINs sei der weiterführende Artikel Fortgeschrittene Joins empfohlen.

Die Beispiel-Tabellen als MySQL-Dump[Bearbeiten]

Für den Fall, dass Sie die unten stehenden Beispiele nachvollziehen wollen finden Sie hier den Dump-Code, den Sie als Datei.sql abspeichern und dann mit mysql -u USER -p DATENBANK < Datei.sql einlesen können.

Beispiel: Datei.sql

#CREATEDATABASEIFNOTEXISTS`selfhtml`;#USE`selfhtml`;DROPTABLEIFEXISTS`Kreditkarte`;CREATETABLE`Kreditkarte`(`Kartennummer`BIGINT(20)NOTNULL,`Firma`tinytextNOTNULL,`Inhaber`tinytextNOTNULL,`Ablaufdatum`DATENOTNULL,KEY`Kartennummer`(`Kartennummer`))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin;LOCKTABLES`Kreditkarte`WRITE;INSERTINTO`Kreditkarte`VALUES(12345,'VISA','Max Mustermann','2017-05-01'),(12346,'Mastercard','Katrin Musterfrau','2018-01-01'),(12347,'American Express','John Doe','2015-02-01'),(12348,'Diners Club','John Doe','2020-03-01');UNLOCKTABLES;DROPTABLEIFEXISTS`Rechnungen`;CREATETABLE`Rechnungen`(`RechnungsNr`BIGINT(20)NOTNULL,`KundenNr`tinytextNOTNULL,`Betrag`DECIMAL(10,2)NOTNULL,`Kartennummer`BIGINT(20)DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin;LOCKTABLES`Rechnungen`WRITE;INSERTINTO`Rechnungen`VALUES(98765,'ABX039',49.95,12345),(98766,'ABX039',12.95,NULL),(98767,'ABX040',79.95,12347),(98768,'ABX050',59.99,12347),(98769,'ABX050',29.99,12348),(98770,'ABX060',99.99,NULL);UNLOCKTABLES;

INNER JOIN[Bearbeiten]

Der INNER JOIN führt Datensätze aus der linken und rechten Tabelle genau dann zusammen, wenn die angegebenen Kriterien alle erfüllt sind. Ist eines oder mehrere der Kriterien nicht erfüllt, so entsteht kein Datensatz in der Ergebnismenge. Durch den Einsatz dieses JOINS reduziert sich das Ergebnis des Kreuzprodukts auf ein Minimum (vergleiche auch nachfolgende Join-Varianten).

Gesucht werden alle Rechnungen, die mit Kreditkarte beglichen wurden.

Beispiel: INNER JOIN

RechnungsNrKundenNrBetragKartennummerFirmaInhaberAblaufdatum
98765ABX03949,9512345VISAMax Mustermann05/2007
98767ABX04079,9512347American ExpressJohn Doe01/2007
98768ABX05059,9912347American ExpressJohn Doe01/2007
98769ABX05029,9912348Diners ClubJohn Doe03/2008
SELECTRechnungsNr,KundenNr,Betrag,Rechnungen.Kartennummer,Firma,Inhaber,AblaufdatumFROMKreditkarte,RechnungenWHEREKreditkarte.Kartennummer=Rechnungen.Kartennummer
SELECTRechnungsNr,KundenNr,Betrag,Rechnungen.Kartennummer,Firma,Inhaber,AblaufdatumFROMKreditkarteINNERJOINRechnungenONKreditkarte.Kartennummer=Rechnungen.Kartennummer

Beachten Sie, dass die Reihenfolge, in der die Tabellen genannt werden, bei diesem Join egal ist.

Zunächst fällt auf, dass von ursprünglich 6 Rechnungen nun nur noch 4 übrig sind. Der Grund liegt darin, dass zu den beiden Barzahlungseinträgen (Kartennummer ) keine passende Kreditkarte gefunden werden konnte. Damit ist das -Kriterium des Joins nicht erfüllt und der Datensatz entfällt. Analog ist die Kreditkarte nicht im Ergebnis enthalten, da sie keiner Rechnung zugeordnet werden konnte.

Im Vergleich zum Kreuzprodukt entfallen darüber hinaus all diejenigen Datensätze, bei denen zwar in beiden Tabellen eine Kreditkartennummer vorhanden ist, diese aber nicht übereinstimmt.

Beachten Sie: Die Spalte muss zusammen mit einem Tabellennamen angegeben werden, da sie durch den Join im Ergebnis doppelt vorhanden ist, einmal aus jeder Tabelle (wenn auch mit identischem Wert). Das DBMS fordert daher zur Auflösung dieser Mehrdeutigkeit auf.

Das obige Ergebnis stimmt zudem mit einer nicht-normalisierten Speicherung aller Kreditkartenzahlungen überein, allerdings mit den bekannten Redundanzen (hier z.B. die Kreditkartendaten der Kartennummer ).

LEFT/RIGHT JOIN[Bearbeiten]

Die LEFT JOIN- bzw. RIGHT JOIN-Varianten sind auf den ersten Blick etwas schwieriger zu verstehen. Die Syntax ist dabei bis auf das jeweilige Schlüsselwort analog der Syntax des INNER JOIN. Der Unterschied liegt in den Datensätzen, die hinterher im Ergebnis zugelassen werden.

Die Logik lautet für den LEFT JOIN: Ein Datensatz aus der linken Tabelle kommt in jedem Fall in das Ergebnis. Wenn ein Datensatz der rechten Tabelle dem ON-Kriterium entspricht, so wird er entsprechend in den Spalten eingetragen, ansonsten bleiben die Spalten leer (). Der RIGHT JOIN arbeitet genau entgegengesetzt.

Beachten Sie: In diesem Fall spielt die Reihenfolge in der die Tabellen angesprochen werden sehr wohl eine Rolle, (alle Kreditkarten, ggf. Rechnungsdaten) liefert ein anderes Ergebnis als (alle Rechnungen, ggf. Kreditkarteninformation).

Beispiel LEFT JOIN[Bearbeiten]

Gesucht werden alle Rechnungen. Falls sie per Kreditkarte bezahlt wurden, so sollen die Kartendaten ebenfalls ausgegeben werden.

Beispiel: LEFT JOIN

SELECTRechnungsNr,KundenNr,Betrag,Rechnungen.Kartennummer,Firma,Inhaber,AblaufdatumFROMRechnungenLEFTJOINKreditkarteONKreditkarte.Kartennummer=Rechnungen.Kartennummer

Beachten Sie, dass im Unterschied zum INNER JOIN die Herkunft der selektierten Kartennummer eine Rolle spielt. Dies ist mehr eine syntaktische Feinheit als inhaltliche Notwendigkeit.

Für alle Datensätze trifft genau eines der nachfolgenden Szenarien zu:

  1. Rechnungen.Kartennummer = Kreditkarte.Kartennummer: Beide Kartennummern stimmen überein, damit ist es egal, aus welcher Tabelle die Kartennummer entstammt.
  2. Rechnungen.Kartennummer ist NULL (Barzahlung): Der Join bringt kein Ergebnis. Per Definition ergibt damit der Ausdruck Kreditkarte.Kartennummer , ebenso wie Rechnungen.Kartennummer.
  3. Rechnungen.Kartennummer ist eine nicht-existente Karte: Dies ist der kritische Sonderfall. Bei korrekt definierten Konsistenzbedingungen dürfte er nicht auftreten. Tut er dies doch, so ist Kreditkarte.Kartennummer , da keine Übereinstimmung gefunden wurde. Rechnungen.Kartennummer enthält jedoch die ungültige Kartennummer. Je nachdem welche Spalte im aufgeführt wurde, unterscheidet sich also das Ergebnis.

Das Ergebnis der Abfrage lautet:

Beispiel: – Ergebnis des LEFT JOIN

RechnungsNrKundenNrBetragKartennummerFirmaInhaberAblaufdatum
98765ABX03949,9512345VISAMax Mustermann05/2007
98766ABX03912,95----
98767ABX04079,9512347American ExpressJohn Doe01/2007
98768ABX05059,9912347American ExpressJohn Doe01/2007
98769ABX05029,9912348Diners ClubJohn Doe03/2008
98770ABX06099,99----

Es befinden sich nun alle 6 Rechnungen der Ausgangsmenge im Ergebnis, alle diejenigen zu denen es eine Karteninformation gab wurden um diese Informationen ergänzt.

Die Spalte muss zusammen mit dem Tabellennamen angegeben werden, da sie durch den Join im Ergebnis doppelt vorhanden ist, einmal aus jeder Tabelle (wenn auch mit identischem Wert). Das DBMS fordert daher zur Auflösung dieser Mehrdeutigkeit auf.

Dieses Ergebnis entspricht der nicht normalisierten Speicherung aller Rechnungsinformationen.

Beispiel RIGHT JOIN[Bearbeiten]

Gesucht werden alle Karteninformationen. Falls mit der entsprechenden Kreditkarte etwas bestellt wurde, sollen die Rechnungsinformationen beigefügt werden.

Beispiel: RIGHT JOIN

SELECTRechnungsNr,KundenNr,Betrag,Kreditkarte.Kartennummer,Firma,Inhaber,AblaufdatumFROMRechnungenRIGHTJOINKreditkarteONKreditkarte.Kartennummer=Rechnungen.Kartennummer

Beachten Sie, dass im Unterschied zum INNER JOIN die Herkunft der selektierte Kartennummer eine Rolle spielt. Dies ist mehr eine syntaktische Feinheit als inhaltliche Notwendigkeit.

Für alle Datensätze trifft genau eines der nachfolgenden Szenarien zu:

  1. Kreditkarte.Kartennummer = Rechnungen.Kartennummer: Beide Kartennummern stimmen überein, damit ist es egal, aus welcher Tabelle die Kartennummer entstammt.
  2. Kreditkarte.Kartennummer ist NULL: Der Join bringt kein Ergebnis. Per Definition ergibt damit der Ausdruck Rechnungen.Kartennummer , ebenso wie Kreditkarte.Kartennummer.
    Dieser Auflistungspunkt wurde nur aus Gründen der Vollständigkeit eingefügt. Das vorgeführte Beispiel arbeitet mit einer Fremdschlüsselbeziehung: Der Primärschlüssel aus der Tabelle tritt als Fremdschlüssel in der Tabelle auf. Beim LEFT JOIN sind diese Ausführungen demnach sinnvoll (es kann eine Rechnung geben, bei der keine Kreditkarte eingetragen ist). Dieses inverse RIGHT JOIN-Beispiel geht jedoch von einer Kreditkarte mit der Kartennummer aus, was aufgrund der Primärschlüsseleigenschaft der Kartennummer nicht passieren kann.
  3. Kreditkarte.Kartennummer ist eine nicht-verwendete Karte: Dies ist der kritische Sonderfall. Wird Rechnungen.Kartennummer abgefragt, so ist das Ergebnis , da keine Übereinstimmung gefunden wurde. Kreditkarte.Kartennummer enthält jedoch die Nummer der nicht verwendeten Karte. Je nachdem welche Spalte im aufgeführt wurde, unterscheidet sich also das Ergebnis.

Hinweis

Das MySQL-Handbuch rät von der Verwendung von RIGHT JOIN ab, da dieser Join nicht von allen DBMS unterstützt wird und durch simples Umsortieren durch einen LEFT JOIN ersetzt werden kann.

Beispiel: – Ergebnis RIGHT JOIN

RechnungsNrKundenNrBetragKartennummerFirmaInhaberAblaufdatum
98765ABX03949,9512345VISAMax Mustermann05/2007
---12346MastercardKatrin Musterfrau01/2008
98767ABX04079,9512347American ExpressJohn Doe01/2007
98768ABX05059,9912347American ExpressJohn Doe01/2007
98769ABX05029,9912348Diners ClubJohn Doe03/2008

In der Ergebnismenge befinden sich alle 4 Kreditkarten, die Karte taucht doppelt auf, da sie zwei Rechnungen zugeordnet ist. Die Karte hingegen wird ohne Rechnung gelistet.

Die Spalte muss zusammen mit dem Tabellennamen angegeben werden, da sie durch den Join im Ergebnis doppelt vorhanden ist, einmal aus jeder Tabelle (wenn auch mit identischem Wert). Das DBMS fordert daher zur Auflösung dieser Mehrdeutigkeit auf.

Dieses Ergebnis entspricht der nicht normalisierten Speicherung aller Kreditkarteninformationen.

FULL OUTER JOIN[Bearbeiten]

Der FULL OUTER JOIN kommt dem ursprünglichen Kreuzprodukt von allen Joins am nächsten. Er ist gewissermaßen die Kombination aus LEFT und RIGHT JOIN.

Die Logik für diesen Join: Jeder Datensatz der rechten und der linken Tabelle kommt in die Ergebnismenge. Findet sich über das -Kriterium ein passender Partner werden beide zusammengefügt, andernfalls wird die jeweils fehlende Seite mit aufgefüllt.

Gesucht werden sowohl alle Karteninformationen als auch alle Rechnungen. Sofern möglich sollen dabei Rechnungen und Karten kombiniert werden.

Beispiel: FULL OUTER JOIN

RechnungsNrKundenNrBetragKartennummerFirmaInhaberAblaufdatum
98765ABX03949,9512345VISAMax Mustermann05/2007
98766ABX03912,95----
---12346MastercardKatrin Musterfrau01/2008
98767ABX04079,9512347American ExpressJohn Doe01/2007
98768ABX05059,9912347American ExpressJohn Doe01/2007
98769ABX05029,9912348Diners ClubJohn Doe03/2008
98770ABX06099,99----
SELECTRechnungsNr,KundenNr,Betrag,Rechnungen.Kartennummer,Firma,Inhaber,AblaufdatumFROMRechnungenOUTERJOINKreditkarteONKreditkarte.Kartennummer=Rechnungen.Kartennummer;

Beachten Sie, dass in diesem Fall die Reihenfolge der Tabellen wiederum egal ist. Achtung: MySQL unterstützt diese Abfrage nicht.

In der Ergebnismenge befinden sich nun alle 6 Bestellungen sowie alle 4 Kreditkarten, sofern möglich mit Datensätzen der jeweils anderen Tabelle zusammengeführt.

Die Spalte muss zusammen mit dem Tabellennamen angegeben werden, da sie durch den Join im Ergebnis doppelt vorhanden ist, einmal aus jeder Tabelle (wenn auch mit identischem Wert). Das DBMS fordert daher zur Auflösung dieser Mehrdeutigkeit auf.

Das Ergebnis der Abfrage entspricht einer nicht normalisierten Speicherung aller Daten in einer Tabelle.

Abschließende Anmerkungen[Bearbeiten]

Im Zusammenhang mit Joins und normalisierten Tabellen sollte man einige Dinge im Hinterkopf behalten:

  • Ein Join ist und bleibt – je nach Tabellengröße – eine umfangreiche Aktion. Wenn die Performance ein kritischer Faktor ist, sollte man die verwendeten Joins dahingehend untersuchen, ob:
    • sämtliche in den Kriterien () verwendeten Spalten indiziert sind
    • der Join tatsächlich nötig ist und nicht aus reiner Bequemlichkeit einer zweiten Abfrage vorgezogen wird
    • die Tabellenreihenfolge optimal gewählt ist. Als Faustregel gilt: Immer mit der kleinsten Tabelle beginnen. Wenn das Datenbanksystem Subselects unterstützt, kann es effizienter sein statt der gesamten Tabelle nur eine durch Kriterien beschränkte Teilmenge der Datensätze im Join zu verwenden.

Beispiele zur Tabellenreihenfolge: Alle Rechnungen zur Kreditkarte 12347[Bearbeiten]

Beispiel: 1

SELECTRechnungsNr,KundenNr,Betrag,Rechnungen.Kartennummer,Firma,Inhaber,AblaufdatumFROMRechnungenINNERJOINKreditkarteONKreditkarte.Kartennummer=Rechnungen.KartennummerWHEREKreditkarte.Kartennummer=12347

Beispiel: 2

SELECTRechnungsNr,KundenNr,Betrag,Rechnungen.Kartennummer,Firma,Inhaber,AblaufdatumFROMKreditkarteINNERJOINRechnungenONKreditkarte.Kartennummer=Rechnungen.KartennummerANDKreditkarte.Kartennummer=12347

Beispiel: 3

SELECTRechnungsNr,KundenNr,Betrag,Rechnungen.Kartennummer,Firma,Inhaber,AblaufdatumFROM(SELECTKartennummer,Firma,Inhaber,AblaufdatumFROMKreditkarteWHEREKartennummer=12347)KarteINNERJOINRechnungenONKarte.Kartennummer=Rechnungen.Kartennummer

Views[Bearbeiten]

Es muss nicht immer ein Join sein. Wenn immer wieder die selbe Abfolge von Joins benötigt wird, kann Gebrauch von sog. Views gemacht werden. Ein View ist eine Art virtuelle Tabelle. Er basiert auf einer beliebig komplexen SQL-Abfrage und stellt das Ergebnis in Form einer Tabelle zur Verfügung. Das Datenbanksystem kann das Ergebnis dieser Abfrage unter Umständen cachen und somit beim zweiten Zugriff schneller zur Verfügung stellen als das Ergebnis des Joins. Beim Lesezugriff auf einen View gibt es i.d.R. keine Probleme, Schreiboperationen hingegen sind mit Vorsicht zu genießen.

Fortsetzung[Bearbeiten]

SQL ist nicht ganz einfach zu verstehen, speziell wenn es darum geht mehrere Tabellen mit joins zusammenzufassen bzw zu verknüpfen. Im Folgenden wird dem SQL Einsteiger sehr einfach mit Venn Diagrammen sowie einfachen SQL queries erklärt  wie die verschiedenen SQL joins funktionieren. Dazu werden zwei Beispieltabellen mit Daten benutzt und Joins beschrieben sowie deren Eingabedaten und Ergebnisse. Dateien zum Erstellen der Beispieldaten sowie die SQL queries können downloaded werden. Die Beispiele sind für mySQL und DB2.

 

 

 

Zur Einführung werden Venn Diagramme benutzt. Im streng mathematischen Sinne benutzt man Venn Diagramme nur für Mengen und deren Operationen wie Vereinigung, Schnitt, Differenz usw, aber sie sind auch sehr hilfreich um sql joins zu erklären und zu verstehen.

 

Genaugenommen gibt es vier verschiedene sql joins die man verstehen muss:

 

1) inner join

2) left/right outer join

3) full outer join

4) karthesisches Produkt

 

Wenn man Venn Diagramme benutzt um die Joins zu erklären gibt es noch zwei weitere Joins, die Entsprechungen in den Venn Diagrammen haben und das Verständnis von Joins fördern.

 

5) Left outer join where null

6) Full outer joinwhere null

 

In den folgenden Venn Diagrammen findet man immer zwei Tabellen TabelleA und TabelleB, die jeweils Zeilen enthalten, in denen Namen und weitere Informationen stehen. Der Join wird immer über die Namen gemacht, d.h. es werden Zeilen in beiden Tabellen gesucht, die gleiche Namen haben und deren Joinergebnisse betrachtet.

 

 

Bei den folgenden Beispielen enthalten die Tabellen A und B folgende Spalten und Inhalt:

Jede Tabelle hat einen Schlüssel id, eine Spalte name sowie eine Spalte nation mit der Nationalität der Person sowie eine Spalte table, die entweder A oder B enthält um zu kennzeichnen, in welcher Tabelle die Zeilenstehen, also Tabelle A oder Tabelle B. 

 

Die sql Befehle um die Tabelle anzulegen und zu füllen können hier für mySQL und hier für DB2 runtergeladen werden.  Die Queries um die Joins über die Tabelle auszuführen befinden sich hier für mySQL und hier für DB2 zum download.

 

Tabelle A:

 

                                                                                                                          

 
Tabelle B:

mysql> select * from TableB;
+----+--------+--------+-------+
| id | name   | nation | table |
+----+--------+--------+-------+
|  1 | Marc   | China  | B     |
|  2 | Albert | France | B     |
|  3 | Peter  | Japan  | B     |
|  4 | Paul   | Poland | B     |
+----+--------+--------+-------+ 

1) Inner join

 

Bei einem Inner join werden alle Zeilen aus der Tabelle A und der Tabelle B zusammengeführt, wo die Join Spaltenelemente gleich sind.

 








 

Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A und B zusammengefügt hat, wo jeweils der Name gleich ist, d.h. Albert und Paul.

 

 

2) Left outer join

 

Bei einem left outer join werden alle Zeilen aus der Tabelle A und der Tabelle B zusammengeführt, wo die Join Spaltenelemente gleich sind sowie alle Elemente aus der linken Tabelle, die in diesem Falle Tabelle A ist.

 










 

Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A und B zusammengefügt hat, wo jeweils der Name gleich ist, d.h. Albert und Paul aber auch die Elemente aus der Tabelle A, wo es keinen gleichen Namen in Tabelle B gibt (Elizabeth und Julia). Diese Elemente sind daran zu erkennen, dass der Teil der Ergebnisspalten, wo die Teile aus der Tabelle B stehen mit NULL gefüllt sind.

 

 


   

3) Full outer join

 

Bei einem full outer join werden alle Zeilen aus der Tabelle A und der Tabelle B zusammengeführt, wo die Join Spaltenelemente gleich sind sowie alle Elemente aus den beiden Tabellen, diekeine gleichen Elemente haben.

 

mysql: (Da mysql kein full outer join anbietet wird der full outer join über eine Vereinigung von dem left und right outer join vorgenommen, der dasselbe Ergebnis liefert)

 

 

DB2: (Richtiger full join)

 

 

Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A und B zusammengefügt hat, wo jeweils der Name gleich ist, d.h. Albert und Paul aber auch die Elemente aus der Tabelle A, wo es keinen gleichen Namen in Tabelle B gibt (Elizabeth und Julia) sowie die Elemente aus Tabelle B, wo es kein gleichen Namen in Tabelle A gibt (Marc und Peter). Diese Elemente sind daran zu erkennen, dass der Teil der Ergebnisspalten, wo die Teile aus der anderen Tabelle nicht existieren mit NULL gefüllt sind.Bei dem Zusammenfügen (UNION) der beiden Tabellen ist zu berücksichtigen, dass dabei eigentlich die Zeilen, die gleiche Namen in beiden Tabellen haben, in der Ergebniszeile doppelt auftreten, da sie sowohl im left als auch right join enthalten sind. Allerdings entfernt die UNION diese doppelten Elemente. Im folgenden Beispiel wird durch UNION ALL diese Entfernung verhindert. Die Zeilen mit Albert und Paul tauchen zweimal auf.

 

 

 


  

4) Cross join - Karthesisches Produkt

 

In diesem Falle gibt es keine Join Bedingung. Deshalb werden  bei diesem join jeweils alle Zeilen der linken Tabelle mit den jeweiligen Zeilen der rechten Tabelle  zusammengeführt. D.h. die Ergebnistabelle enthält immer (Anzahl Zeilen TabelleA) * (Anzahl Zeilen TabelleB) Ergebniszeilen. Diese kann bei großen Ausgangstabellen sehr schnell riesige Ergebnistabellen ergeben deren Berechnung sehr zeit- und speicher aufwendig ist und sollte deshalb vermieden werden.

 






















Man sieht also, dass das Ergebnis alle Zeilen aus der Tabelle A jeweils mit allen Elementen der Tabelle B zusammengefügt hat. Dabei tauchen keine Spalten mit NULL auf.

 

Eine Darstellung als Venn Diagramm gibt es dafür nicht.     

 


 

5) Left outer join where null

 

Bei diesem join werden alle Zeilen aus der Tabelle A mit der Tabelle B zusammengeführt, wo es keine gleichen Elemente gibt. Es entspricht also auch dem left outer join wo der inner join entfernt wurde.

 










Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A und B zusammengefügt hat, wo jeweils für Namen aus der linken Tabelle A keine Namen in der Tabelle B existieren. Da ein Join vorgenommen wird tauchen Spalten der Tabelle B im Ergebnis auf, die aber alle mit NULL gefüllt sind.

 

 


 

6) Full outer join where null

 

Bei diesem join werden alle Zeilen der beiden Tabellen  zusammengeführt, wo es keine gleichen Elemente in der anderen Tabelle gibt. Er entspricht also dem full outer join wo der inner join entfernt wurde.

 











+------+-----------+---------+-------+------+-------+--------+-------+

Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A und B zusammengefügt hat, wo jeweils für Namen aus der linken Tabelle A keine Namen in der Tabelle B existieren und für Namen aus der rechten Tabelle B keine Namen in der linken Tabelle A existieren. Da ein Join vorgenommen wird tauchen Spalten der Tabelle A und B im Ergebnis auf, die aber alle mit NULL gefüllt sind.

 

 


 

References: 

 

A visual explanation of SQL joins 

Wikipedia: Relational algebra

mySQL join examples

mySQL homepage

DB2 Express - full functional relational and XML DB  server for free

Warum DB2 express?

0 comments

Leave a Reply

Your email address will not be published. Required fields are marked *