Skip navigation links
13.2.7.2 UNION »
Section Navigation      [Toggle]

13.2.7.1. JOIN

MySQL unterstützt die folgenden JOIN-Syntaxen für den table_references-Teil von SELECT-Anweisungen sowie von DELETE- und UPDATE-Anweisungen für mehrere Tabellen: 

table_references:
    table_reference [, table_reference] ...

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [[AS] alias]
        [{USE|IGNORE|FORCE} INDEX (key_list)]
  | ( table_references )
  | { OJ table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }

join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON condition
  | table_reference LEFT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
  | table_reference RIGHT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

join_condition:
    ON conditional_expr
  | USING (column_list)

Eine Tabellenreferenzierung heißt auch Join-Ausdruck.

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. MySQL unterstützt auch verschachtelte Joins (siehe auch Abschnitt 7.2.10, „Optimierung verschachtelter Joins“).

Im ON-Teil sollten keine Bedingungen vorhanden sein, die zur Beschränkung der Datensätze in der Ergebnismenge verwendet werden; geben Sie solche Bedingungen besser in der WHERE-Klausel an. Es gibt aber Ausnahmen zu dieser Regel.

Die oben gezeigte Syntax { OJ ... LEFT OUTER JOIN ...} ist nur aus Gründen der Kompatibilität mit ODBC vorhanden. Die geschweiften Klammern in der Syntax sollten literal notiert werden, sind also keine Metasyntax, wie sie andernorts in Syntaxbeschreibungen verwendet wird.

  • Für einen Tabellenverweis kann mit tbl_name AS alias_name oder tbl_name alias_name ein Alias erstellt werden: 

    SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
  WHERE t1.name = t2.name;

SELECT t1.name, t2.salary FROM employee t1, info t2
  WHERE t1.name = t2.name;

  • Die Bedingungsanweisung ON ist ein beliebiger Bedingungsausdruck der Form, die in einer WHERE-Klausel verwendet werden kann.

  • Ist für die rechte Tabelle im ON- oder USING-Teil eines LEFT JOIN kein passender Datensatz vorhanden, dann wird für die rechte Tabelle ein Datensatz verwendet, bei dem alle Spalten auf NULL gesetzt sind. Sie können diesen Umstand nutzen, um Datensätze in einer Tabelle zu finden, die kein Gegenstück in einer anderen Tabelle aufweisen: 

    SELECT table1.* FROM table1
  LEFT JOIN table2 ON table1.id=table2.id
  WHERE table2.id IS NULL;

    Dieses Beispiel findet alle Datensätze in table1 mit einem id-Wert, der nicht in table2 vorhanden ist (d. h. alle Datensätze in table1 ohne entsprechenden Datensatz in table2). Dies setzt voraus, dass table2.id als NOT NULL deklariert wurde. Siehe auch Abschnitt 7.2.9, „Optimierung von LEFT JOIN und RIGHT JOIN“.

  • Die USING(column_list)-Klausel benennt eine Liste mit Spalten, die in beiden Tabellen vorhanden sein müssen. Wenn die Tabellen a und b jeweils die Spalten c1, c2 und c3 enthalten, dann vergleicht der folgende Join die entsprechenden Spalten der beiden Tabellen: 

    a LEFT JOIN b USING (c1,c2,c3)

  • Der NATURAL [LEFT] JOIN beider Tabellen wird als semantisch äquivalent zu einem INNER JOIN oder einem LEFT JOIN mit einer USING-Klausel definiert, die alle Spalten aufführt, die in beiden Tabellen vorhanden sind.

  • INNER JOIN und , (Komma) sind semantisch gleichwertig, wenn keine Join-Bedingung vorhanden ist: Beide erzeugen ein kartesisches Produkt zwischen den angegebenen Tabellen (d. h., jeder Datensatz in der ersten Tabelle wird mit jedem Datensatz in der zweiten Tabelle verknüpft).

  • RIGHT JOIN funktioniert analog zu LEFT JOIN. Um den Code datenbankübergreifend portierbar zu halten, wird empfohlen, LEFT JOIN anstelle von RIGHT JOIN zu verwenden.

  • STRAIGHT_JOIN ist bis auf die Tatsache, dass die linke Tabelle immer vor der rechten gelesen wird, identisch mit JOIN. Dies kann für die (wenigen) Fälle genutzt werden, in denen der Join-Optimierer die Tabellen in der falschen Reihenfolge anordnet.

Sie können Hinweise dazu angeben, welchen Index MySQL beim Abrufen von Informationen aus einer Tabelle verwenden soll. Durch Angabe von USE INDEX (key_list) können Sie MySQL anweisen, nur einen der möglichen Indizes zum Ermitteln von Datensätzen in der Tabelle zu verwenden. Mit der alternativen Syntax IGNORE INDEX (key_list) können Sie MySQL anweisen, einen bestimmten Index nicht zu verwenden. Solche Hinweise sind nützlich, wenn EXPLAIN zeigt, dass MySQL aus einer Liste möglicher Indizes den falschen verwendet.

Sie können auch FORCE INDEX verwenden. Diese Option agiert wie USE INDEX (key_list), es wird aber zusätzlich vorausgesetzt, dass ein Tabellenscan sehr kostspielig ist. Anders gesagt: Ein Tabellenscan wird nur verwendet, wenn die Datensätze in der Tabelle nicht unter der Verwendung eines der gegebenen Indizes gefunden werden können.

USE INDEX, IGNORE INDEX und FORCE INDEX wirken sich erst darauf aus, welche Indizes verwendet werden, wenn MySQL entschieden hat, wie Datensätze in der Tabelle ermittelt werden und wie der Join durchgeführt wird. Sie haben keine Auswirkungen darauf, ob ein Index benutzt wird, wenn eine ORDER BY- oder GROUP BY-Klausel aufgelöst wird.

USE KEY, IGNORE KEY und FORCE KEY sind Synonyme von USE INDEX, IGNORE INDEX und FORCE INDEX.

Hier einige Beispiele für Joins: 

SELECT * FROM table1,table2 WHERE table1.id=table2.id;

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;

SELECT * FROM table1 LEFT JOIN table2 USING (id);

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
  LEFT JOIN table3 ON table2.id=table3.id;

SELECT * FROM table1 USE INDEX (key1,key2)
  WHERE key1=1 AND key2=2 AND key3=3;

SELECT * FROM table1 IGNORE INDEX (key3)
  WHERE key1=1 AND key2=2 AND key3=3;

Hinweis: Natürliche Joins und Joins mit USING (einschließlich Varianten mit äußeren Joins) werden entsprechend Standard SQL:2003 verarbeitet. Diese Änderungen erhöhen die Kompatibilität von MySQL mit dem SQL-Standard. Allerdings kann es bei manchen Joins zu unterschiedlichen Ausgabespalten kommen. Ferner müssen einige Abfragen, die in älteren Versionen (vor 5.0.12) zu funktionieren schienen, neu geschrieben werden, um dem Standard zu entsprechen. Die folgende Liste enthält weitere Angaben zu verschiedenen Auswirkungen der aktuellen Join-Verarbeitung im Vergleich zur Verarbeitung in älteren Versionen. Der Begriff „älter“ bezeichnet hier Versionen vor MySQL 5.0.12.

  • Die Spalten eines NATURAL- oder USING-Joins können sich von denen älterer Versionen unterscheiden. Insbesondere erscheinen keine redundanten Ausgabespalten mehr, und die Reihenfolge der Spalten für die Erweiterung SELECT * kann anders aussehen.

    Beachten Sie die folgenden Anweisungen: 

    CREATE TABLE t1 (i INT, j INT);
CREATE TABLE t2 (k INT, j INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
SELECT * FROM t1 NATURAL JOIN t2;
SELECT * FROM t1 JOIN t2 USING (j);

    In den älteren Versionen erzeugten sie folgende Ausgabe: 

    +------+------+------+------+
| i    | j    | k    | j    |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
+------+------+------+------+
+------+------+------+------+
| i    | j    | k    | j    |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
+------+------+------+------+

    In der ersten SELECT-Anweisung erscheint Spalte i in beiden Tabellen und wird insofern eine Join-Spalte, d. h., sie sollte laut SQL-Standard nur einmal (und nicht zweimal) in der Ausgabe erscheinen. Ähnlich ist die Spalte j in der zweiten SELECT-Anweisung in der USING-Klausel aufgeführt und sollte ebenfalls nur einmal (und nicht zweimal) in der Ausgabe auftauchen. In beiden Fällen wird die redundante Spalte nicht beseitigt. Auch ist die Reihenfolge der Spalten nicht korrekt im Sinne des SQL-Standards.

    Heute erzeugen die Anweisungen folgende Ausgabe: 

    +------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+

    Die redundante Spalte ist beseitigt. Auch die Spaltenreihenfolge ist nun korrekt im Sinne des SQL-Standards:

    • Zuerst werden die Spalten, die in beiden Tabellen vorhanden sind, in der Reihenfolge aufgeführt, in der sie in der ersten Tabelle erscheinen.

    • Als Zweites erscheinen Spalten, die nur in der ersten Tabelle vorhanden sind, und zwar in der Reihenfolge, in der sie in der Tabelle erscheinen.

    • Als Drittes erscheinen Spalten, die nur in der zweiten Tabelle vorhanden sind, und zwar in der Reihenfolge, in der sie in der Tabelle erscheinen.

  • Die Auswertung eines natürlichen Vielfach-Joins unterscheidet sich auf eine Weise, die ein Neuformulieren von Abfragen erforderlich machen kann. Angenommen, Sie haben drei Tabellen t1(a,b), t2(c,b) und t3(a,c), die jeweils einen Datensatz aufweisen: t1(1,2), t2(10,2) und t3(7,10). Nehmen wir ferner an, dass Sie folgenden NATURAL JOIN auf die drei Tabellen haben: 

    SELECT ... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;

    In älteren Versionen wurde der linke Operand des zweiten Joins als t2 betrachtet, wohingegen er eigentlich der verschachtelte Join (t1 NATURAL JOIN t2) sein sollte. Infolgedessen werden die Spalten von t3 nur auf gemeinsame Spalten in t2 geprüft; hat t3 gemeinsame Spalten mit t1, dann werden diese nicht als Equi-Join-Spalten betrachtet. Insofern wurde obige Abfrage bei älteren Versionen in den folgenden Equi-Join transformiert: 

    SELECT ... FROM t1, t2, t3
  WHERE t1.b = t2.b AND t2.c = t3.c;

    Diesem Join fehlt aber ein weiteres Equi-Join-Prädikat (t1.a = t3.a). Aufgrund dessen wird als Ergebnis ein Datensatz ausgegeben – und nicht das korrekte leere Ergebnis. Die korrekte äquivalente Abfrage sieht wie folgt aus: 

    SELECT ... FROM t1, t2, t3
  WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;

    Wenn Sie in aktuellen MySQL-Versionen dasselbe Abfrageergebnis wie bei den älteren Versionen erhalten, dann formulieren Sie den natürlichen Join als Equi-Join um.

  • Früher hatten der Kommaoperator (,) und JOIN dieselbe Rangstufe, d. h., der Join-Ausdruck t1, t2 JOIN t3 wurde als ((t1, t2) JOIN t3) interpretiert. Jetzt hat JOIN Vorrang vor dem Komma, d. h., der Ausdruck wird als (t1, (t2 JOIN t3)) ausgewertet. Diese Änderung betrifft Anweisungen, die eine ON-Klausel verwenden, denn diese Klausel kann nur Spalten in den Operanden des Joins referenzieren, und die Änderung in der Rangstufe wirkt sich darauf aus, wie interpretiert wird, was diese Operanden sind.

    Beispiel: 

    CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t3 VALUES(1,1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

    In älteren Versionen war die SELECT-Anweisung aufgrund der impliziten Gruppierung von t1,t2 als (t1,t2) zulässig. Jetzt hat JOIN Vorrang, d. h., die Operanden für die ON-Klausel sind t2 und t3. Da t1.i1 keine Spalte in einem der Operanden ist, ist das Ergebnis der Fehler Unknown column 't1.i1' in 'on clause'. Um die Verarbeitung des Joins zuzulassen, gruppieren Sie die ersten beiden Tabellen explizit mithilfe von Klammern, sodass die Operanden für die ON-Klausel (t1,t2) und t3 sind: 

    SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

    Alternativ umgehen Sie die Verwendung des Kommaoperators und verwenden stattdessen JOIN: 

    SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

    Diese Änderung gilt auch für INNER JOIN, CROSS JOIN, LEFT JOIN und RIGHT JOIN: Sie alle haben nun Vorrang vor dem Kommaoperator.

  • Früher konnte die ON-Klausel Spalten in Tabellen referenzieren, die auf ihrer rechten Seite aufgeführt wurden. Jetzt kann eine ON-Klausel nur ihre Operanden referenzieren.

    Beispiel: 

    CREATE TABLE t1 (i1 INT);
CREATE TABLE t2 (i2 INT);
CREATE TABLE t3 (i3 INT);
SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;

    Früher war diese SELECT-Anweisung zulässig. Nun schlägt die Anweisung mit dem Fehler Unknown column 'i3' in 'on clause' fehl, weil i3 eine Spalte in t3 ist, die kein Operand der ON-Klausel ist. Die Anweisung sollte wie folgt umgeschrieben werden: 

    SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);

  • Früher konnte eine USING-Klausel als ON-Klausel neugeschrieben werden, die die entsprechenden Spalten vergleichen konnte. Die beiden folgenden Klauseln etwa sind semantisch identisch: 

    a LEFT JOIN b USING (c1,c2,c3)
a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

    Heute haben diese beiden Klauseln nicht mehr dieselbe Bedeutung:

    • Bezüglich der Ermittlung, welche Datensätze die Join-Bedingung erfüllen, bleiben beide Joins semantisch identisch.

    • Allerdings ist diese semantische Identität nicht mehr in Bezug auf die Frage vorhanden, wie die für die SELECT *-Erweiterung anzuzeigenden Spalten bestimmt werden. Der USING-Join wählt den zusammengefassten Wert der entsprechenden Spalten, während der ON-Join alle Spalten aus allen Tabellen auswählt. Bei obigem USING-Join wählt SELECT * die folgenden Werte aus: 

      COALESCE(a.c1,b.c1), COALESCE(a.c2,b.c2), COALESCE(a.c3,b.c3)

      Beim ON-Join wählt SELECT * die folgenden Werte aus: 

      a.c1, a.c2, a.c3, b.c1, b.c2, b.c3

      Bei einem inneren Join ist COALESCE(a.c1,b.c1) das Gleiche wie a.c1 oder b.c1, weil beide Spalten denselben Wert haben. Bei einem äußeren Join (wie LEFT JOIN) darf eine der beiden Spalten NULL sein. Diese Spalte wird im Ergebnis weggelassen.

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.