MySQL Datenbankhandbuch: MySQL-Sprachreferenz mit vielen Beispielen: JOIN

28.9 JOIN

MySQL unterstützt die folgenden JOIN Ausdrücke in SELECT Statements:

table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr
table_reference LEFT [OUTER] JOIN table_reference USING (column_list)
table_reference NATURAL LEFT [OUTER] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }

Die zuletzt angegebene LEFT OUTER JOIN Syntax existiert nur aus Kompatibilitätgründen zu ODBC. Eine Tabellen-Referenz kann mit einem Alias versehen werden: tbl_name AS alias_name oder tbl_name alias_name :

 

      mysql> select t1.name, t2.salary from employee AS t1, info AS t2
                 where t1.name = t2.name;

JOIN und , (Komma) sind von der Syntax her völlig äquivalent. Beide führen einen vollen JOIN Befehl zwischen zwei Tabellen aus. Normalerweise sollten Tabellen mit Hilfe der WHERE Bedingung verbunden werden. Der Ausdruck ON beschreibt ein kondizionales "Wenn es übereinstimmt", und kann mit dem Ausdruck WHERE verbunden werden. Wenn es keinen passenden Eintrag für die richtige Tabelle in einem LEFT JOIN gibt, wird eine Reihe, wo alle Spalten auf NULL gesetzt sind, für die rechte Tabelle verwendet. Es können somit Einträge gefunden werden, die kein entsprechendes Gegenstück in einer anderen Tabelle besitzten:


      mysql> select table1.* from table1
                 LEFT JOIN table2 ON table1.id=table2.id
                 where table2.id is NULL;

Der einfachste JOIN ist der sogenannte "EQUI-JOIN". Ein Beispiel :


SELECT A.EineSpalte, B.EineAndereSpalte   
FROM Tabelle1 AS A, Tabelle2 AS B WHERE A.EinWert = B.EinAndererWert;

Man kann ihn aber auch ganz anders schreiben, und die Ergebnismenge wird die gleiche sein, nämlich so :


SELECT A.EineSpalte, B.EineAndereSpalte   
FROM Tabelle1 AS A JOIN Tabelle2 AS B       
ON A.EinWert = B.EinAndererWert;

Wenn die Ergebnismenge die gleiche ist, wo liegt dann der Unterschied zwischen diesen beiden Formen ? Gibt es überhaupt einen Unterschied ?

Der Unterschied liegt in der Laufzeit. Im ersten Beispiel wird zuerst das kartesische Produkt aus beiden Tabellen gebildet (jede Zeile aus Tabelle1 wird mit jeder Zeile aus Tabelle2 verknüpft), und wenn beide Tabellen nur jeweils 100 Zeilen enthalten, sind das schon 10.000 Zeilen in der temporären Zwischentabelle. Erst dann werden die Zeilen gelöscht, die nicht die WHERE-Bedingung erfüllen.

Im zweiten Fall wird zuerst die Bedingung im ON-Teil geprüft und nur solche Zeilen in die Zwischentabelle übernommen, bei denen die Bedingung erfüllt ist. In dem Beispiel mit den je 100 Zeilen pro Tabelle sind das wahrscheinlich nicht mehr als 100 Zeilen. Das ist ein Faktor von 10 !

Dieses Beispiel findet alle Reihen in Tabelle 1 mit einem ID Wert, der nicht in Tabelle 2 enthalten ist. Das setzt voraus, daß table2.id ist als NOT NULL definiert worden.

Die der Ausdruck USING column_list benennt eine Liste von Spalten, die in beiden Tabellen existieren müssen. Ein Beispiel:

LEFT JOIN B USING (C1,C2,C3,...) ist identisch mit dem Ausdruck:

A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...

Das NATURAL LEFT JOIN zweier Tabellen ist identisch zu dem Ausdruck LEFT JOIN mit USING. Es werden dann alle Spalten genommen, die in beiden Tabellen exstieren. STRAIGHT JOIN ist identich mit JOIN, abgesehen davon, daß die linke Tabelle vor der rechten Tabelle ausgelesen wird. Dies kann dazu benutzt werden, wenn der JOIN Optimizer die Tabellen in der falschen Reihenfolge ausgibt (in seltenen Fällen). Ein paar Beispiele:

mysql> select * from table1,table2 where table1.id=table2.id;
mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> select * from table1 LEFT JOIN table2 USING (id);
mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id
           LEFT JOIN table3 ON table2.id=table3.id;

Hier nun ein Beispiel für ein bestimmtes Problem, welches sich nur mit Hilfe eines LEFT JOINS lösen läßt. Es gibt hier zwei Tabellen, tabelle1 und tabelle2. Es sollen alle Elemente ausgegeben werden, die in tabelle1, aber nicht in tabelle2 enthalten sind. In den Tabellen sind jeweils mitdestens eine Spalte namens id enthalten. Dieses Konstrukt würde bei Datenbanken, die das Kommando SELECT ... MINUS SELECT .., also Subselects zulassen, in MySQL dasselbe bewirken:


SELECT table1.* from table1
LEFT JOIN table2 ON table1.id=table2.id
where table2.id is NULL

OUTER JOINs

Folgende Aufgabe : Sie wollen eine Preisliste drucken, und überall dort, wo es einen Lieferanten aus England gibt, soll dessen Name angedruckt werden. Bei den anderen Artikeln soll diese Ergebnis Spalte leer bleiben.

Ein einfacher JOIN reicht hier nicht mehr. Der Versuch, das Ergebnis so zu erzielen, wird scheitern:


 
SELECT A.ArtikelNr, B.Lieferant   
FROM Artikel AS A, Lieferanten AS B WHERE A.LieferantID = B.LieferantID     
AND B.Land = 'GB';

Dieses Statement macht nicht das, was es soll. Die Ergebnismenge enthält nur die Artikel mit englischen Lieferanten. Der Grund ist auch klar : nur bei diesen Artikeln ist die zweite WHERE-Bedingung hinter dem "AND" erfüllt. Wir brauchen also etwas anderes : den OUTER JOIN. Der sieht so aus :


 
SELECT A.ArtikelNr, B.Lieferant 
FROM Artikel AS A LEFT OUTER
JOIN Lieferanten AS B ON A.LieferantID = B.LieferantIDWHERE B.Land = 'GB';

Bei einem OUTER JOIN gibt es immer eine Tabelle, die als erhaltene Tabelle bezeichnet wird; alle ihre Zeilen bleiben erhalten. Wenn es sich um einen LEFT OUTER JOIN handelt, ist die linke Tabelle (in unserem Beispiel Tabelle1) die erhaltene Tabelle; bei einem RIGHT OUTER JOIN ist es die rechte Tabelle (Tabelle2).

Die ON-Bedingung muß keine Gleichheits-Bedingung sein. Nehmen wir an, Sie wollen für jeden Tag die Anzahl der Bestellungen zeigen, die vor diesem Tag fällig waren, und alle Tage sollen im Ergebnis enthalten sein. Das erfordert wieder einen LEFT OUTER JOIN, und könnte so aussehen :


SELECT A.Tag, COUNT(B.BestellNr)  
FROM Tage AS A LEFT OUTER JOIN
Bestellungen AS B ON A.Tag > B.FaelligkeitsDatum;

Firma Microsoft z.B., hält sich mal wieder nicht an Standards, und implementiert den OUTER JOIN so:


....  FROM Tabelle1 AS A, Tabelle2 AS B WHERE A.Code *= B.Code....

Tabelle1 ist diejenige Tabelle, die erhalten bleibt. Bei den EQUI-JOINS, bei denen die ON Bedingung die Gleichheit ausdrückt, funktioniert obiges Beispiel. Mit der Microsoft Syntax bei SQL Server 6.5 und 7.0 ist es leider nicht möglich.

Ein ganz konkretes Beispiel für die Problematik mit JOINS:


    mysql> SELECT * FROM a;       mysql> SELECT * FROM b; 
    +----+----------+             +----+                  
    | id | ort      |             | id |                  
    +----+----------+             +----+                  
    |  1 | passau   |             |  3 |                  
    |  2 | münchen  |             |  4 |                  
    |  3 | salzburg |             +----+            
    |  4 | passau   |             2 rows in set (0.00 sec)                  
    +----+----------+                               
    4 rows in set (0.00 sec)

In Tabelle a steht also, welche Personen in welchem Ort sind.

In Tabelle b steht, welche Personen einer Niederlassung angehören.

Nun sehen wir mal Schritt für Schritt, was bei "a.id != b.id" genau passiert. (Sortierungen mit ORDER BY mache ich jeweils nur der Übersichtlichkeit halber; an der Ergenismenge selbst ändert sich dadurch ja nichts.)

Erstmal das komplette Kreuzprodukt, das man bekommt, wenn man einen JOIN ohne Bedingung durchführt ("p_in_o" steht für "Person in Ort", "p_in_n" für "Person in Niederlassung"):


    mysql> SELECT a.ort, a.id AS p_in_o, b.id AS p_in_n
       --> FROM a, b
       --> ORDER BY p_in_o, p_in_n;
    +----------+--------+--------+
    | ort      | p_in_o | p_in_n |
    +----------+--------+--------+
    | passau   |      1 |      3 |
    | passau   |      1 |      4 |
    | münchen  |      2 |      3 |
    | münchen  |      2 |      4 |
    | salzburg |      3 |      3 |    <-- Gleichheit
    | salzburg |      3 |      4 |
    | passau   |      4 |      3 |
    | passau   |      4 |      4 |    <-- Gleichheit
    +----------+--------+--------+  
    8 rows in set (0.00 sec)

Das kombiniert also alle Tupel aus Tabelle a mit allen Tupeln aus Tabelle b.

Die markierten Zeilen haben die Bedeutung: diese Person wohnt in diesem Ort und ist in einer Niederlassung.

Die anderen Zeilen haben die Bedeutung: die Person p_in_o wohnt in diesem Ort und die andere Person p_in_n ist in einer Niederlassung. Das ergibt also keine Aussage über eine Person!

Mit dem Zusatz "WHERE a.id != b.id" fallen jetzt die Zeilen weg, wo z.B. "a.id = b.id" wäre:


    mysql> SELECT a.ort, a.id AS p_in_o, b.id AS p_in_n
       --> FROM a, b
       --> WHERE a.id != b.id
       --> ORDER BY a.id, b.id;
    +----------+--------+--------+
    | ort      | p_in_o | p_in_n |
    +----------+--------+--------+
    | passau   |      1 |      3 |
    | passau   |      1 |      4 |
    | münchen  |      2 |      3 |
    | münchen  |      2 |      4 |
    | salzburg |      3 |      4 |
    | passau   |      4 |      3 |
    +----------+--------+--------+
    6 rows in set (0.00 sec)

Diese Tabelle enthält in Spalte p_in_o NICHT nur die Leute, die in keiner Niederlassung sind .....

Und selbst bei den Personen in Spalte p_in_o, die tatsächlich in keiner Niederlassung sind, stimmen die Anzahlen nicht, die man per COUNT(*) ... GROUP BY ... ermitteln kann, da z.B. Person 1 zweimal erscheint. Gruppiert man nun nach dem Ort, würden sich z.B. für "passau" hier zusammen dann drei Zeilen ergeben. Korrekt wäre aber nur die Ausgabe einer Zeile !!!!!

Wie macht man es korrekt ? In Wirklichkeit ist die Denkweise falsch. Es müssen alle Personen gesucht werden, die nicht in der Tabelle b enthalten sind, also keiner Niederlassung angehören:

Erster Schritt: Personen, die einer Niederlassung angehören:


    mysql> SELECT a.ort, a.id AS p_in_o, b.id AS p_in_n
       --> FROM a, b
       --> WHERE a.id = b.id
       --> ORDER BY p_in_o, p_in_n;
    +----------+--------+--------+
    | ort      | p_in_o | p_in_n |
    +----------+--------+--------+
    | salzburg |      3 |      3 |
    | passau   |      4 |      4 |
    +----------+--------+--------+
    2 rows in set (0.00 sec)

Es sind also genau zwei Zeilen, die gleiche Einträge haben. Dummerweise fallen aber so die anderen Personen aus Tabelle a heraus. Genau diese hätten wir aber gerne ebenfalls noch mit ausgegeben. Hier hilft der LEFT JOIN !

Er bewirkt, daß auch die Werten aus der linken Tabelle ausgegeben werden, zu denen es keinen Join-Partner aus der anderen Tabelle gibt:


    mysql> SELECT a.ort, a.id AS p_in_o, b.id AS p_in_n
       --> FROM a LEFT JOIN b
       -->      ON a.id = b.id
       --> ORDER BY p_in_o, p_in_n;
    +----------+--------+--------+
    | ort      | p_in_o | p_in_n |
    +----------+--------+--------+
    | passau   |      1 |   NULL |
    | münchen  |      2 |   NULL |
    | salzburg |      3 |      3 |
    | passau   |      4 |      4 |
    +----------+--------+--------+
    4 rows in set (0.00 sec)

Nun dürfte klar sein, daß die Personen, die in keiner Niederlassung sind, man mit Hilfe des Zusatzes: WHERE b.id IS NULL ermitteln kann.

Im WHERE-Teil kann man leider nicht mit Alias-Namen arbeiten.....


    mysql> SELECT a.ort, a.id AS p_in_o, b.id AS p_in_n
       --> FROM a LEFT JOIN b
       -->      ON a.id = b.id
       --> WHERE b.id IS NULL
       --> ORDER BY p_in_o, p_in_n;
    +---------+--------+--------+
    | ort     | p_in_o | p_in_n |
    +---------+--------+--------+
    | passau  |      1 |   NULL |
    | münchen |      2 |   NULL |
    +---------+--------+--------+
    2 rows in set (0.00 sec)

Hier nun das fertige Statement:


SELECT a.ort, COUNT(*) AS anzahl
FROM person AS a LEFT JOIN rel_niederlassung_person AS b
ON a.id = b.id
WHERE b.id IS NULL
GROUP BY a.ort;

Somit dürfte auch klar sein, wie man einen "RIGHT JOIN" mit MySQL realisieren kann .... nämlich einfach durch Vertauschung der linken und rechten Tabelle .....

Online Suche im Handbuch