MySQL JOINs

Joins

Um Daten, die mit einander in Verbindung stehen, aus mehreren Tabellen aus einer Datenbank zu bekommen muss man diese Tabellen mit einander verknüpfen. Dies geschieht unter der Verwendung von JOINs.

Es gibt mehrere Arten von JOINs, die wichtigsten sind INNER JOIN, OUTER JOIN und CROSS JOIN. Der CROSS JOIN ist die Grundlage aller JOINs und sollte ihn kennen und wissen das er jeden Datensatz der einen Tabelle mit den Datensatz der anderen Tabelle verbindet (Kartesisches Produkt). Also das Produkt der Datensätze der beiden Tabellen als Ergebnis menge ausgibt. Da die Tabellen unter keinerlei Bedingung bzw. Einschränkung Verknüpft sind.

Der INNER JOIN (JOIN) zeigt nur die Datensätze der beiden Tabellen an in denen es Übereinstimmungen im angegebenen Datenfeld gibt (Schlüssel).

Der OUTER JOIN unterteilt sich noch in LEFT und RIGTH JOIN. Das bedeutet im Gegensatz zum INNER JOIN, der ja nur Übereinstimmungen anzeigt, das auch Daten der Linken bzw. der Rechten Tabelle ohne Übereinstimmungen angezeigt werden (NULL Werte im Feld).

Tabelle Buch
+------------+------------+----------+---------+
| buch_id | titel | preis | jahr |
+------------+------------+----------+--------+
| 1 | HTML | 10.50 | 2003 |
| 2 | XHTML | 12.50 | 2010 |
| 3 | CSS | 19.99 | 2001 |
| 4 | PHP 5.0 | 9.50 | 2008 |
| 5 | PHP 5.3 | 19.50 | 2009 |
| 6 | MYSQL | 14.30 | 2007 |
| 7 | XML | 12.50 | 2007 |
+------------+------------+----------+--------+

Tabelle Autor
+----------+---------+----------+
| autor_id | vname | name |
+----------+---------+----------+
| 1 | Heiko | Jensen |
| 2 | Sven | Meier |
| 3 | Torsten | Schulze |
| 5 | Heike | Schumann |
| 8 | Horst | Manitz |
+----------+---------+----------+

Unter Verwendung eines CROSS JOIN würde man hier 35 Datensätze erhalten die völlig sinnlos währen.

SELECT * FROM autor, buch;

Um heraus zu finden welcher Autor welches Buch geschrieben hat (autor_id = buch_id) kann man einen INNER JOIN verwenden.

SELECT vname, name, titel FROM autor JOIN buch ON autor_id = buch_id;

Links und rechts von JOIN gibt man die Tabellen an und nach ON werden die Spaltennamen der Tabellen angegeben die in Beziehung stehen. Somit erhalten wir nur noch 4 Datensätze, nämlich nur von den Autoren die auch ein Buch geschrieben haben.

Die Aussage links und rechts von JOIN kann man sich gleich mal merken. Genau damit haben der LEFT und der RIGTH JOINs was zu tun.

+---------+----------+---------+
| vname | name | titel |
+---------+----------+---------+
| Heiko | Jensen | HTML |
| Sven | Meier | XHTML |
| Torsten | Schulze | CSS |
| Heike | Schumann | PHP 5.3 |
+---------+----------+---------+

Der INNER JOIN (JOIN) kann natürlich auch mit der WHERE Klausel geschrieben werden.

SELECT * FROM autor, buch WHERE autor_id = buch_id;

Ergibt die selbe Ausgabe.

Wenn man die selbe Querie,

SELECT vname, name, titel FROM autor JOIN buch ON autor_id = buch_id;,

mit einen LEFT JOIN ausführt bekommt man einen Datensatz mehr.

SELECT vname, name, titel FROM autor LEFT JOIN buch ON autor_id = buch_id;

Das liegt daran weil sich der LEFT JOIN auf die Linke Tabelle (autor) bezieht. Das heißt so viel wie gib mir alle Daten aus Autor und alle übereinstimmenden und nicht übereinstimmenden Daten aus Buch. Dort wo keine Übereinstimmung vorhanden ist (also kein Datensatz existiert) wird NULL ausgegeben.

+---------+----------+---------+
| vname | name | titel |
+---------+----------+---------+
| Heiko | Jensen | HTML |
| Sven | Meier | XHTML |
| Torsten | Schulze | CSS |
| Heike | Schumann | PHP 5.3 |
| Horst | Manitz | NULL |
+---------+----------+---------+

Im klar Text, für den Autor Horst Meier gibt es keinen Datensatz in der Tabelle Buch, er hat also noch keins geschrieben oder es wurde einfach noch nicht in die Datenbank eingetragen. Somit könnte ich jetzt z.b. mit einen LEFT JOIN auf einfache weiße herausfinden welche Autoren noch kein Buch geschrieben haben. Oder mit einer anderen Beispiel Datenbank ‚Mitarbeiter’ welche Arbeiter noch nicht Ihre Stundenzettel abgegeben haben. Dazu erweitern wir die Query um eine WHERE Klausel, um nur die Datensätze mit NULL anzuzeigen.

SELECT vname, name, titel FROM autor LEFT JOIN buch ON autor_id = buch_id WHERE titel IS NULL;

+-------+--------+-------+
| vname | name | titel |
+-------+--------+-------+
| Horst | Manitz | NULL |
+-------+--------+-------+

Wenn Du bis jetzt alles verstanden hast weißt Du natürlich welche Daten angezeigt werden wenn bei der Überprüfung in der WHERE Klausel auf IS NOT NULL geprüft wird und welchem JOIN die Ausgabe entspricht.

SELECT vname, name, titel FROM autor left JOIN buch ON autor_id = buch_id WHERE titel IS NOT NULL;

Genau die 4 Datensätze von unserer INNER JOIN Abfrage, also alle übereinstimmenden Datensätze aus Tabelle Autor und Bücher bzw. alle Autoren die auch wirklich ein Buch geschrieben haben.

+---------+----------+---------+
| vname | name | titel |
+---------+----------+---------+
| Heiko | Jensen | HTML |
| Sven | Meier | XHTML |
| Torsten | Schulze | CSS |
| Heike | Schumann | PHP 5.3 |
+---------+----------+---------+

Beim RIGHT JOIN verhält sich das natürlich genau anders herum, da werden alle Datensätze aus der Rechten Tabelle und alle übereinstimmenden und nicht übereinstimmenden Datensätze aus der Linken Tabelle (Autor) ausgegeben. Damit erhalten wir genau 7 Datensätze weil wir ja genau 7 Bücher in unserer Tabelle Bücher haben. Es werden also alle Werte von vname und name in den Zeilen, die ein Buch betreffen von dem kein Autor bekannt ist, mit NULL angezeigt.

SELECT vname, name, titel FROM autor RIGHT JOIN buch ON autor_id = buch_id;

+---------+----------+---------+
| vname | name | titel |
+---------+----------+---------+
| Heiko | Jensen | HTML |
| Sven | Meier | XHTML |
| Torsten | Schulze | CSS |
| NULL | NULL | PHP 5.0 |
| Heike | Schumann | PHP 5.3 |
| NULL | NULL | MYSQL |
| NULL | NULL | XML |
+---------+----------+---------+

Kann man jetzt den RIGHT JOIN so umschreiben das dasselbe Ergebnis heraus kommt wie bei der Query mit dem LEFT JOIN, ohne LEFT zu benutzen? Ja man kann. Tausche einfach die Tabellen, so das jetzt Buch vor dem JOIN steht. Also links und Autor nach dem JOIN, also rechts.

SELECT vname, name, titel FROM buch RIGHT JOIN autor ON autor_id = buch_id;

+---------+----------+---------+
| vname | name | titel |
+---------+----------+---------+
| Heiko | Jensen | HTML |
| Sven | Meier | XHTML |
| Torsten | Schulze | CSS |
| Heike | Schumann | PHP 5.3 |
| Horst | Manitz | NULL |
+---------+----------+---------+

Das Ergebnis ist das selbe wie bei unseren LEFT JOIN.

Joins mit USING

Eine weitere Möglichkeit von JOINs ist die Benutzung von USING. Dabei müssen aber die Spaltennamen auf die sich die Tabellen beziehen den gleichen Namen haben. Ich habe dafür die beiden Tabellen verändert so das jetzt buch_id nur noch id und autor_id ebenfalls nur noch id heißt. Sie besitzen jetzt also den gleichen Namen und wir können USING benutzen. Dadurch wird die Query etwas übersichtlicher.

SELECT vname, name, titel FROM autor JOIN buch USING(id);

+---------+----------+---------+
| vname | name | titel |
+---------+----------+---------+
| Heiko | Jensen | HTML |
| Sven | Meier | XHTML |
| Torsten | Schulze | CSS |
| Heike | Schumann | PHP 5.3 |
+---------+----------+---------+

Den selben Effekt erreicht man wenn man NATURAL JOIN benutzt, dann sucht MYSQL sich selbst die Spalten zur Verknüpfung. Ist in dem Fall, da ja die beiden Spalten denselben Name (id) haben und auch den selben Datentyp (INT) in Ordnung.

SELECT vname, name, titel FROM autor NATURAL JOIN buch;

+---------+----------+---------+
| vname | name | titel |
+---------+----------+---------+
| Heiko | Jensen | HTML |
| Sven | Meier | XHTML |
| Torsten | Schulze | CSS |
| Heike | Schumann | PHP 5.3 |
+---------+----------+---------+

Ändere ich jetzt die Namen wieder in buch_id und autor_id findet MYSQL keine Übereinstimmung und liefert als Ergebnis wieder einen CROSS JOIN.

Wird jetzt sogar noch die Spalte Titel in Tabelle Buch in 'name' umbenannt, werden keine Ergebnisse mehr angezeigt. Da MYSQL selbst die Spalten zur Verknüpfung wählt, wird jetzt die Spalte 'name' genommen. Und da kein Autor denselben Name hat wie ein Buch gibt es auch keine Resultate.

Ich habe jetzt ein Buch über Flash und einen Autor Namens Flash Gordon in die Datenbank eingefügt.

mysql> SELECT * from buch;

+---------+---------+-------+------+
| buch_id | name | preis | jahr |
+---------+---------+-------+------+
| 1 | HTML | 10.50 | 2003 |
| 2 | XHTML | 12.50 | 2010 |
| 3 | CSS | 19.99 | 2001 |
| 4 | PHP 5.0 | 9.50 | 2008 |
| 5 | PHP 5.3 | 19.50 | 2009 |
| 6 | MYSQL | 14.30 | 2007 |
| 7 | XML | 12.50 | 2007 |
| 8 | Flash | 12.00 | 2000 |
+---------+---------+-------+------+

8 rows in set (0.00 sec)

mysql> SELECT * from autor;

+----------+---------+----------+
| autor_id | vname | name |
+----------+---------+----------+
| 1 | Heiko | Jensen |
| 2 | Sven | Meier |
| 3 | Torsten | Schulze |
| 5 | Heike | Schumann |
| 8 | Horst | Manitz |
| 9 | Gordon | Flash |
+----------+---------+----------+

6 rows in set (0.00 sec)

Sie ahnen es bestimmt schon unser NATURAL JOIN findet jetzt eine Übereinstimmung in der Spalte name mit Flash und gibt uns diese Zeile zurück. Flash Gordon hat aber kein Buch über Flash geschrieben

mysql> SELECT a.vname AS Vorname, a.name AS Name, b.name AS Buch FROM autor a natural JOIN buch b;

+---------+-------+-------+
| Vorname | Name | Buch |
+---------+-------+-------+
| Gordon | Flash | Flash |
+---------+-------+-------+

1 row in set (0.00 sec)

In der Query habe ich Alias Namen für die Tabellen und Spalten benutzt.

Den NATURAL JOIN sollte man also nur verwenden wenn man genau über die Tabellen Definition und den Inhalt Bescheid weiß und man sicher stellen kann das diese auch später nicht verändert werden. Vor allem die Spalten Bezeichnungen auf die sich die Tabellen beziehen.

Es ist auch möglich JOINs auf dieselbe Tabelle zu setzen SELF JOIN. Hierbei ist aber darauf zu achten das auf jeden Fall Alias Namen vergeben werden müssen, für Tabelle und Spalten. Da ja sonst nicht mehr unterschieden werden kann um welche Spalten es sich handelt, den die Spalten kommen ja alle aus der selben Tabelle.