Die Syntax zum Ausdrücken von Joins gestattet auch
verschachtelte Joins. Die nachfolgende Beschreibung bezieht sich
auf die in Abschnitt 13.2.7.1, „JOIN
“, beschriebene Join-Syntax.
Die Syntax von table_factor
ist im
Vergleich zum SQL-Standard erweitert. SQL akzeptiert nur
table_reference
, nicht aber eine in
Klammern gesetzte Liste mit Referenzierungen. Dies ist eine
konservative Erweiterung, sofern wir jedes Komma in einer Liste
mit table_reference
-Elementen als
äquivalent zu einem inneren Join betrachten. Zum Beispiel:
SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
ist äquivalent mit
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
In MySQL ist CROSS JOIN
syntaktisch ein
Äquivalent zu INNER JOIN
(diese lassen sich
gegeneinander austauschen). Nach SQL-Standard hingegen sind
beide nicht äquivalent. INNER JOIN
wird bei
einer ON
-Klausel und CROSS
JOIN
andernfalls verwendet.
Klammern können in Join-Ausdrücken, die nur innere Join-Operationen enthalten, ignoriert werden. Betrachten Sie folgenden Ausdruck:
t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ON t1.a=t2.a
Nach Entfernen der Klammern und Gruppieren der Operationen auf der linken Seite entsteht folgender Ausdruck:
(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL
Trotzdem sind die beiden Ausdrücke nicht äquivalent. Um dies
nachzuweisen, nehmen wir an, dass die Tabellen
t1
, t2
und
t3
die folgenden Zustände haben:
Tabelle t1
enthält die Datensätze
{1}
, {2}
.
Tabelle t2
enthält den Datensatz
{1,101}
.
Tabelle t3
enthält den Datensatz
{101}
.
In diesem Fall gibt der erste Ausdruck eine Ergebnismenge mit
den Datensätzen {1,1,101,101}
,
{2,NULL,NULL,NULL}
zurück; der zweite
Ausdruck hingegen gibt die Datensätze
{1,1,101,101}
,
{2,NULL,NULL,101}
zurück:
mysql>SELECT *
->FROM t1
->LEFT JOIN
->(t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
->ON t1.a=t2.a;
+------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | NULL | +------+------+------+------+ mysql>SELECT *
->FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)
->LEFT JOIN t3
->ON t2.b=t3.b OR t2.b IS NULL;
+------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | 101 | +------+------+------+------+
Im folgenden Beispiel wird ein äußerer Join gemeinsam mit einem inneren Join verwendet:
t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
Dieser Ausdruck kann nicht in den folgenden Ausdruck transformiert werden:
t1 LEFT JOIN t2 ON t1.a=t2.a, t3.
Für die gegebenen Tabellenzustände geben die beiden Ausdrücke verschiedene Datensatzmengen zurück:
mysql>SELECT *
->FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;
+------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | NULL | +------+------+------+------+ mysql>SELECT *
->FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;
+------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | 101 | +------+------+------+------+
Aus diesem Grund ändern wir, wenn wir in einem Join-Ausdruck mit äußeren Join-Operatoren die Klammern weglassen, unter Umständen die Ergebnismenge für den ursprünglichen Ausdruck.
Genauer formuliert: Wir dürfen die Klammern im rechten Operanden der linken äußeren Join-Operation und im linken Operanden einer rechten Join-Operation nicht ignorieren. Oder: Die Klammern der inneren Tabellenausdrücke äußerer Join-Operationen dürfen nicht unbeachtet bleiben. Klammern für den anderen Operanden (d. h. den Operanden der äußeren Tabelle) können hingegen ignoriert werden.
Der Ausdruck
(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)
ist äquivalent zu folgendem Ausdruck:
t1, t2 LEFT JOIN t3 ON P(t2.b,t3.b)
Dies gilt für alle Tabellen t1,t2,t3
und
alle Bedingungen P
für Attribute
t2.b
und t3.b
.
Immer dann, wenn die Join-Operationen in einem Join-Ausdruck
(join_table
) nicht von links nach
rechts ausgeführt werden, redet man von verschachtelten Joins.
Betrachten Sie einmal die folgenden Abfragen:
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a WHERE t1.a > 1 SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1
Diese Abfragen enthalten die folgenden verschachtelten Joins:
t2 LEFT JOIN t3 ON t2.b=t3.b t2, t3
Der verschachtelte Join wird in der ersten Abfrage mit einem Left-Join gebildet, während er in der zweiten Abfrage aufgrund einer inneren Join-Operation entsteht.
In der ersten Abfrage können die Klammern weggelassen werden:
Die grammatische Struktur des Join-Ausdrucks schreibt dieselbe
Ausführungsreihenfolge für Join-Operationen vor. Bei der
zweiten Abfrage dürfen die Klammern nicht weggelassen werden,
obwohl der Join-Ausdruck hier auch ohne sie eindeutig
interpretiert werden könnte. (In unserer erweiterten Syntax
sind die Klammern im Ausdruck (t2, t3)
der
zweiten Abfrage erforderlich, obwohl die Abfrage theoretisch
auch ohne sie analysiert werden könnte: Wir hätten immer noch
eine eindeutige syntaktische Struktur für die Abfrage, da
LEFT JOIN
und ON
die Rolle
der linken und rechten Begrenzungen für den Ausdruck
(t2,t3)
übernähmen.)
Die obigen Beispiele veranschaulichen die folgenden Aspekte:
Bei Join-Ausdrücken, die nur innere Joins (und keine äußeren Joins) enthalten, können die Klammern entfernt werden. Sie können die Klammern entfernen und die Auswertung von links nach rechts vornehmen (tatsächlich kann die Auswertung der Tabellen sogar in beliebiger Reihenfolge erfolgen).
Dies gilt allerdings in der Regel nicht für äußere Joins oder mit inneren Joins gemischte äußere Joins. Ein Entfernen der Klammern kann das Ergebnis ändern.
Abfragen mit verschachtelten äußeren Joins werden in derselben
Weise ausgeführt wie Abfragen mit inneren Joins. Genauer
gesagt, wird eine Abwandlung des Join-Algorithmus mit
verschachtelten Schleifen benutzt. Vergegenwärtigen Sie sich,
mit welchem Algorithmusschema ein Join mit verschachtelten
Schleifen eine Abfrage ausführt. Angenommen, wir haben eine
Join-Abfrage über drei Tabellen T1,T2,T3
der
folgenden Form:
SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2) INNER JOIN T3 ON P2(T2,T3) WHERE P(T1,T2,T3).
Hierbei seien P1(T1,T2)
und
P2(T3,T3)
Join-Bedingungen (für Ausdrücke),
während P(t1,t2,t3)
eine Bedingung über
Spalten der Tabellen T1,T2,T3
ist.
Der Algorithmus für Joins mit verschachtelten Schleifen würde die Abfrage wie folgt ausführen:
FOR each row t1 in T1 { FOR each row t2 in T2 such that P1(t1,t2) { FOR each row t3 in T3 such that P2(t2,t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } } } }
Die Notation t1||t2||t3
bedeutet einen
„Datensatz, der durch Verkettung der Spalten der
Datensätze t1
, t2
und
t3
entsteht“. In einigen der folgenden
Beispiele bezeichnet, wenn ein Datensatzname erscheint,
NULL
die Tatsache, dass
NULL
für jede Spalte dieses Datensatzes
benutzt wird. Beispielsweise bedeutet
t1||t2||NULL
„einen Datensatz, der aus
den verketteten Spalten der Datensätze t1
und t2
sowie NULL
für
jede Spalte von t3
besteht“.
Betrachten wir nun eine Abfrage mit verschachtelten äußeren Joins:
SELECT * FROM T1 LEFT JOIN (T2 LEFT JOIN T3 ON P2(T2,T3)) ON P1(T1,T2) WHERE P(T1,T2,T3).
Für diese Abfrage ändern wir das Muster für verschachtelte Schleifen ab und erhalten Folgendes:
FOR each row t1 in T1 { BOOL f1:=FALSE; FOR each row t2 in T2 such that P1(t1,t2) { BOOL f2:=FALSE; FOR each row t3 in T3 such that P2(t2,t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } f2=TRUE; f1=TRUE; } IF (!f2) { IF P(t1,t2,NULL) { t:=t1||t2||NULL; OUTPUT t; } f1=TRUE; } } IF (!f1) { IF P(t1,NULL,NULL) { t:=t1||NULL||NULL; OUTPUT t; } } }
Generell wird für jede verschachtelte Schleife für die erste
innere Tabelle einer äußeren Join-Operation ein Flag
eingeführt, welches vor der Schleife gelöscht und danach
wieder gesetzt wird. Das Flag wird gesetzt, wenn für den
aktuellen Datensatz aus der äußeren Tabelle eine Entsprechung
in der Tabelle gefunden wird, die den inneren Operanden
darstellt. Wenn das Flag am Ende des Schleifenzyklus immer noch
nicht gelöscht ist, dann wurde für den aktuellen Datensatz in
der äußeren Tabelle keine Entsprechung gefunden. In diesem
Fall wird der Datensatz mit NULL
-Werten für
die Spalten der inneren Tabellen ergänzt. Der Ergebnisdatensatz
wird zur letzten Überprüfung an die Ausgabe oder in die
nächste verschachtelte Schleife übergeben – aber nur dann,
wenn der Datensatz die Join-Bedingung für alle eingebetteten
äußeren Joins erfüllt.
In unserem Beispiel ist die mit dem folgenden Ausdruck beschriebene äußere Join-Tabelle eingebettet:
(T2 LEFT JOIN T3 ON P2(T2,T3))
Beachten Sie, dass der Optimierer für die Abfrage mit inneren Joins eine andere Reihenfolge für verschachtelte Schleifen wählen könnte – etwa die folgende:
FOR each row t3 in T3 { FOR each row t2 in T2 such that P2(t2,t3) { FOR each row t1 in T1 such that P1(t1,t2) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } } } }
Bei Abfragen mit äußeren Joins kann der Optimierer nur eine Reihenfolge wählen, bei der Schleifen für äußere Tabellen den Schleifen für die inneren Tabellen vorangehen. Insofern ist für unsere Abfrage mit äußeren Joins nur eine Verschachtelungsreihenfolge möglich. Bei der folgenden Abfrage wird der Optimierer zwei verschiedene Verschachtelungen auswerten:
SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3) WHERE P(T1,T2,T3)
Die Verschachtelungen sind
FOR each row t1 in T1 { BOOL f1:=FALSE; FOR each row t2 in T2 such that P1(t1,t2) { FOR each row t3 in T3 such that P2(t1,t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } f1:=TRUE } } IF (!f1) { IF P(t1,NULL,NULL) { t:=t1||NULL||NULL; OUTPUT t; } } }
und
FOR each row t1 in T1 { BOOL f1:=FALSE; FOR each row t3 in T3 such that P2(t1,t3) { FOR each row t2 in T2 such that P1(t1,t2) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } f1:=TRUE } } IF (!f1) { IF P(t1,NULL,NULL) { t:=t1||NULL||NULL; OUTPUT t; } } }
In beiden Verschachtelungen muss T1
in der
äußeren Schleife verarbeitet werden, weil sie in einem
äußeren Join verwendet wird. T2
und
T3
werden hingegen in einem inneren Join
benutzt, d. h., der Join muss in der inneren Schleife
verarbeitet werden. Allerdings können, weil der Join ein
innerer Join ist, T2
und
T3
in beliebiger Ordnung verarbeitet werden.
Bei der Beschreibung des Algorithmus mit verschachtelten
Schleifen für innere Joins haben wir ein paar Details
übergangen, deren Wirkung auf die Leistungsfähigkeit der
Abfrageausführung beträchtlich sein kann. Wir haben
beispielsweise so genannte „Pushdown-Bedingungen“
nicht erwähnt. Angenommen, unsere
WHERE
-Bedingung
P(T1,T2,T3)
könnte mit einer konjunktiven
Formel dargestellt werden:
P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3).
In diesem Fall verwendet MySQL in der Tat das folgende Schema mit verschachtelten Schleifen zur Ausführung der Abfrage mit inneren Joins:
FOR each row t1 in T1 such that C1(t1) { FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2) { FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } } } }
Sie erkennen, dass die Konjunkte C1(T1)
,
C2(T2)
und C3(T3)
aus der
innersten in die äußerste Schleife verschoben werden, wo sie
dann ausgewertet werden können. Wenn C1(T1)
eine sehr restriktive Bedingung ist, dann kann dieser
Bedingungs-Pushdown die Anzahl der Datensätze aus Tabelle
T1
, die an die inneren Schleifen
weitergegeben werden, erheblich verringern. Hieraus ergibt sich
eine beachtliche Verbesserung bei der Ausführungszeit für die
Abfrage.
Bei einer Abfrage mit äußeren Joins muss die
WHERE
-Bedingung erst geprüft werden, wenn
festgestellt wurde, dass für den aktuellen Datensatz in der
äußeren Tabelle eine Entsprechung in den inneren Tabellen
vorhanden ist. Aufgrund dessen kann die Optimierung mit einer
Pushdown-Bedingung aus den inneren verschachtelten Schleifen
nicht direkt auf Abfragen mit äußeren Joins angewendet werden.
Deswegen müssen an dieser Stelle konditionale
Pushdown-Prädikate in Verbindung mit Überwachungs-Flags
eingeführt werden, die gesetzt werden, sobald eine
Übereinstimmung gefunden wurde.
Betrachten Sie noch einmal unser Beispiel mit äußeren Joins:
P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)
Hier sieht das Schema der verschachtelten Schleifen unter Verwendung überwachter Pushdown-Bedingungen wie folgt aus:
FOR each row t1 in T1 such that C1(t1) { BOOL f1:=FALSE; FOR each row t2 in T2 such that P1(t1,t2) AND (f1?C2(t2):TRUE) { BOOL f2:=FALSE; FOR each row t3 in T3 such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) { IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) { t:=t1||t2||t3; OUTPUT t; } f2=TRUE; f1=TRUE; } IF (!f2) { IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) { t:=t1||t2||NULL; OUTPUT t; } f1=TRUE; } } IF (!f1 && P(t1,NULL,NULL)) { t:=t1||NULL||NULL; OUTPUT t; } }
Generell können Pushdown-Prädikate aus Join-Bedingungen wie
P1(T1,T2)
und P(T2,T3)
extrahiert werden. In diesem Fall wird ein Pushdown-Prädikat
auch von einem Flag überwacht, das eine Überprüfung des
Prädikats auf den mit NULL
ergänzten
Datensatz verhindert, der im Zuge der entsprechenden
Join-Operation erzeugt wurde.
Beachten Sie, dass ein Zugriff nach Schlüssel aus einer inneren
Tabelle auf eine andere Tabelle im selben verschachtelten Join
unzulässig ist, wenn er durch ein Prädikat aus der
WHERE
-Bedingung herbeigeführt wird. (Wir
könnten in diesem Fall einen bedingten Schlüsselzugriff
verwenden, aber diese Methode ist in MySQL 5.1 noch
nicht implementiert.)
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.