Datenbank/Einführung in Joins

Aus SELFHTML-Wiki
< Datenbank(Weitergeleitet von Left join)
Wechseln zu: Navigation, Suche

Normalisierung & Vorstellung des Beispiels

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

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.
– Tabelle Rechnungen
RechnungsNr KundenNr Betrag Kartennummer Firma Inhaber Ablaufdatum
98765 ABX039 49,95 12345 VISA Max Mustermann 05/2007
98766 ABX039 12,95 - - - -
- - - 12346 Mastercard Katrin Musterfrau 01/2008
98767 ABX040 79,95 12347 American Express John Doe 01/2007
98768 ABX050 59,99 12347 American Express John Doe 01/2007
98769 ABX050 29,99 12348 Diners Club John Doe 03/2008
98770 ABX060 99,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 98766. Keines der Felder Kartennummer, Firma, Inhaber oder Ablaufdatum 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 12346, so zeigt sich die umgekehrte Situation: Es liegen keine Rechnungsinformationen vor.

Probleme dieses Ansatzes

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 John Doe fehlschlagen, weil die Kreditkarteninformationen zu seiner American Express-Karte ungültig sind. Auf Rückfrage bestätigt der Kunde, dass das Ablaufdatum eigentlich nicht 01/2007, sondern 02/2007 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

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:

– Normalisierte Tabellen /Tabelle Kreditkarte
Tabelle Rechnungen
RechnungsNr KundenNr Betrag Kartennummer
98765 ABX039 49,95 12345
98766 ABX039 12,95 -
98767 ABX040 79,95 12347
98768 ABX050 59,99 12347
98769 ABX050 29,99 12348
98770 ABX060 99,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 Kartennummer bzw. RechnungsNr, kann ein Datensatz identifiziert werden. Zwischen der Tabelle Rechnungen und der Tabelle Kreditkarte 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?

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

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

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

XA XB
1 2
2 3
3 4

Y

YA YB YC
1 2 3
2 3 4
3 4 5

X, Y

XA XB YA YB YC
1 2 1 2 3
1 2 2 3 4
1 2 3 4 5
2 3 1 2 3
2 3 2 3 4
2 3 3 4 5
3 4 1 2 3
3 4 2 3 4
3 4 3 4 5

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

Equi-Join

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

XA XB
1 2
2 3
3 4

Y

YA YB YC
1 2 3
2 3 4
3 4 5

X, Y mit XA=YA und XB=YB

XA XB YA YB YC
1 2 1 2 3
2 3 2 3 4
3 4 3 4 5

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

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 A und eine Spalte B existiert. Genau wie beim Equi-Join werden A=A und B=B geprüft. Im Anschluss an die Zusammenführung werden die Spalten paarweise zu einer einzigen A- bzw. B-Spalte zusammengefasst.

X

A B
1 2
2 3
3 4

Y

A B C
1 2 3
2 3 4
3 4 5

Natural Join von X, Y

A B C
1 2 3
2 3 4
3 4 5

oder auch:

A B C
1 2 3
2 3 4
3 4 5

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

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

implizite Schreibweise: 
SELECT X.a, X.b, Y.a, Y.b, c FROM X, Y
explizite Schreibweise: 
SELECT X.a, X.b, Y.a, Y.b, c FROM X JOIN Y

Zunächst fällt auf, dass die Spalten a und b jeweils mit dem Tabellennamen angegeben werden. Der Grund liegt darin, dass das Datenbanksystem bei der Angabe a nicht entscheiden kann, welche der Spalten gemeint ist, die aus Tabelle X oder die aus Tabelle Y. 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

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 WHERE-Klausel einer Abfrage anzugeben.

Beispiel 
SELECT c FROM X, Y WHERE X.a = Y.a AND X.b = Y.b

Im Fall der impliziten Schreibweise müssen die Kriterien hier platziert werden, bei der expliziten können sie so platziert werden.

Beachten Sie: Machen Sie sich den Unterschied klar: Wenn das DBMS die Kriterien nicht dem Join zuordnen kann, so wird zunächst ein vollständiges Kreuzprodukt gebildet und erst am Ende der Abfrage per WHERE auf die relevante Menge reduziert. Speziell bei einer Abfolge von mehreren Joins steigt dadurch die Datenmenge unnötig an.

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

Beispiel 
SELECT c FROM X JOIN Y ON X.a = Y.a AND X.b = Y.b

Das Schlüsselwort ON leitet einen Kriterienblock ein, der dem WHERE-Block des gesamten SELECT-Statements ähnlich ist. Zu jedem Join gehört genau ein oder kein ON-Block.

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 
SELECT c FROM X JOIN Y USING (a, b)

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

Mehrfachjoins

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 X JOIN Y ist X die linke, Y die rechte Tabelle.

implizite Schreibweise 
SELECT a, b, c, d, e FROM X, Y, Z
explizite Schreibweise 
SELECT a, b, c, d, e FROM X JOIN Y JOIN Z

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, sodass zunächst X und Y gejoint werden. Das Ergebnis dieses Joins wird sodann mit Z 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

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.

Datei.sql 
# CREATE DATABASE  IF NOT EXISTS `selfhtml`;
# USE `selfhtml`;

DROP TABLE IF EXISTS `Kreditkarte`;
CREATE TABLE `Kreditkarte` (
  `Kartennummer` BIGINT(20) NOT NULL,
  `Firma` tinytext NOT NULL,
  `Inhaber` tinytext NOT NULL,
  `Ablaufdatum` DATE NOT NULL,
  KEY `Kartennummer` (`Kartennummer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
LOCK TABLES `Kreditkarte` WRITE;
INSERT INTO `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');
UNLOCK TABLES;

DROP TABLE IF EXISTS `Rechnungen`;
CREATE TABLE `Rechnungen` (
  `RechnungsNr` BIGINT(20) NOT NULL,
  `KundenNr` tinytext NOT NULL,
  `Betrag` DECIMAL(10,2) NOT NULL,
  `Kartennummer` BIGINT(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
LOCK TABLES `Rechnungen` WRITE;
INSERT INTO `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);
UNLOCK TABLES;

INNER JOIN

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.

Implizite Schreibweise des INNER JOIN 
SELECT
  RechnungsNr,
  KundenNr,
  Betrag,
  Rechnungen.Kartennummer,
  Firma,
  Inhaber,
  Ablaufdatum
FROM Kreditkarte, Rechnungen
WHERE Kreditkarte.Kartennummer = Rechnungen.Kartennummer
Explizite Schreibweise des INNER JOIN 
SELECT
  RechnungsNr,
  KundenNr,
  Betrag,
  Rechnungen.Kartennummer,
  Firma,
  Inhaber,
  Ablaufdatum
FROM Kreditkarte
INNER JOIN Rechnungen ON Kreditkarte.Kartennummer = Rechnungen.Kartennummer
Das Ergebnis der Abfrage lautet:
RechnungsNr KundenNr Betrag Kartennummer Firma Inhaber Ablaufdatum
98765 ABX039 49,95 12345 VISA Max Mustermann 05/2007
98767 ABX040 79,95 12347 American Express John Doe 01/2007
98768 ABX050 59,99 12347 American Express John Doe 01/2007
98769 ABX050 29,99 12348 Diners Club John Doe 03/2008
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 ON-Kriterium des Joins nicht erfüllt und der Datensatz entfällt. Analog ist die Kreditkarte 12346 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 Kartennummer 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 12347).

LEFT/RIGHT JOIN

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 (NULL). Der RIGHT JOIN arbeitet genau entgegengesetzt.

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

Beispiel LEFT JOIN

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

LEFT JOIN 
SELECT
  RechnungsNr,
  KundenNr,
  Betrag,
  Rechnungen.Kartennummer,
  Firma,
  Inhaber,
  Ablaufdatum
FROM Rechnungen
LEFT JOIN Kreditkarte ON Kreditkarte.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 NULL, 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 NULL, da keine Übereinstimmung gefunden wurde. Rechnungen.Kartennummer enthält jedoch die ungültige Kartennummer. Je nachdem welche Spalte im SELECT aufgeführt wurde, unterscheidet sich also das Ergebnis.

Das Ergebnis der Abfrage lautet:

– Ergebnis des LEFT JOIN
RechnungsNr KundenNr Betrag Kartennummer Firma Inhaber Ablaufdatum
98765 ABX039 49,95 12345 VISA Max Mustermann 05/2007
98766 ABX039 12,95 - - - -
98767 ABX040 79,95 12347 American Express John Doe 01/2007
98768 ABX050 59,99 12347 American Express John Doe 01/2007
98769 ABX050 29,99 12348 Diners Club John Doe 03/2008
98770 ABX060 99,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 Kartennummer 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

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

RIGHT JOIN 
SELECT
  RechnungsNr,
  KundenNr,
  Betrag,
  Kreditkarte.Kartennummer,
  Firma,
  Inhaber,
  Ablaufdatum
FROM Rechnungen RIGHT JOIN Kreditkarte
ON Kreditkarte.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 NULL, 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 Kartennummer aus der Tabelle Kreditkarte tritt als Fremdschlüssel in der Tabelle Rechnungen 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 NULL 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 NULL, da keine Übereinstimmung gefunden wurde. Kreditkarte.Kartennummer enthält jedoch die Nummer der nicht verwendeten Karte. Je nachdem welche Spalte im SELECT 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.
– Ergebnis RIGHT JOIN
RechnungsNr KundenNr Betrag Kartennummer Firma Inhaber Ablaufdatum
98765 ABX039 49,95 12345 VISA Max Mustermann 05/2007
- - - 12346 Mastercard Katrin Musterfrau 01/2008
98767 ABX040 79,95 12347 American Express John Doe 01/2007
98768 ABX050 59,99 12347 American Express John Doe 01/2007
98769 ABX050 29,99 12348 Diners Club John Doe 03/2008

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

Die Spalte Kartennummer 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

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 ON-Kriterium ein passender Partner werden beide zusammengefügt, andernfalls wird die jeweils fehlende Seite mit NULL aufgefüllt.

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

FULL OUTER JOIN 
SELECT
  RechnungsNr,
  KundenNr,
  Betrag,
  Rechnungen.Kartennummer,
  Firma,
  Inhaber,
  Ablaufdatum
FROM Rechnungen
OUTER JOIN Kreditkarte ON Kreditkarte.Kartennummer = Rechnungen.Kartennummer;
Das Ergebnis der Abfrage lautet:
RechnungsNr KundenNr Betrag Kartennummer Firma Inhaber Ablaufdatum
98765 ABX039 49,95 12345 VISA Max Mustermann 05/2007
98766 ABX039 12,95 - - - -
- - - 12346 Mastercard Katrin Musterfrau 01/2008
98767 ABX040 79,95 12347 American Express John Doe 01/2007
98768 ABX050 59,99 12347 American Express John Doe 01/2007
98769 ABX050 29,99 12348 Diners Club John Doe 03/2008
98770 ABX060 99,99 - - - -
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 Kartennummer 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

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 (ON) 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

1 
SELECT
  RechnungsNr,
  KundenNr,
  Betrag,
  Rechnungen.Kartennummer,
  Firma,
  Inhaber,
  Ablaufdatum
FROM Rechnungen
INNER JOIN Kreditkarte ON Kreditkarte.Kartennummer = Rechnungen.Kartennummer
WHERE Kreditkarte.Kartennummer = 12347

In der Ausgangsform ist diese Abfrage relativ ineffizient. Dank der Reihenfolge beim INNER JOIN werden alle Rechnungen mit allen Karteninformationen gegenübergestellt. Erst nach dem Join wird das WHERE-Kriterium angewandt. Unter Umständen optimiert das Datenbanksystem diese Abfrage automatisch, aber sie kann auch vom Programmierer selbst optimiert werden.

2 
SELECT
  RechnungsNr,
  KundenNr,
  Betrag,
  Rechnungen.Kartennummer,
  Firma,
  Inhaber,
  Ablaufdatum
FROM Kreditkarte
INNER JOIN Rechnungen ON Kreditkarte.Kartennummer = Rechnungen.Kartennummer
                      AND Kreditkarte.Kartennummer = 12347

Bei dieser Abfrage wurden die verwendeten Tabellen vertauscht und im ON-Kriterium eine Einschränkung platziert. Die WHERE-Klausel wurde somit weiter nach innen verlegt.

Vorteil dieser Schreibweise ist, dass sie von den meisten DBMS unterstützt wird und keinen Subselect verwendet.

3 
SELECT
  RechnungsNr,
  KundenNr,
  Betrag,
  Rechnungen.Kartennummer,
  Firma,
  Inhaber,
  Ablaufdatum
FROM
  (SELECT Kartennummer, Firma, Inhaber, Ablaufdatum
   FROM Kreditkarte
   WHERE Kartennummer = 12347) Karte
INNER JOIN Rechnungen ON Karte.Kartennummer = Rechnungen.Kartennummer

Dies ist die Subselect-Variante, die nicht von allen DBMS unterstützt wird (vgl. z. B. ältere Versionen von MySQL oder MS-Access).

Der Vorteil dieser Schreibweise gegenüber der vorherigen liegt in der Parametrisierbarkeit: Man stelle sich vor, man möchte eine Abfrage innerhalb einer Anwendung mehrfach verwenden, der Kriterienausdruck soll allerdings dynamisch bleiben. In PHP-Syntax könnte sodann der gesamte Teil SELECT … FROM … WHERE stehen bleiben, einzig und alleine hinter dem WHERE wird eine Variable $(kriterienausdruck) platziert, die beliebig komplex sein kann. Da es sich um ein gewöhnliches SELECT FROM WHERE handelt, können hier auch Gruppierungen o.ä. vorgenommen werden, die in der ON-Klausel unzulässig sind.


Views

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.

Abgerufen von „http://wiki.selfhtml.org/index.php?title=Datenbank/Einführung_in_Joins&oldid=98661“