PHP Lektionen – Lektion 3.6 – Arbeiten mit MySQL-Datenbank. Arten des JOIN-Operators.
In MySQL kann eine Auswahl mit JOIN auf verschiedene Arten durchgeführt werden. Wir werden versuchen, alle diese Arten von Abfragen zu betrachten. Hier ist eine Liste aller Abfragen mit Beteiligung von JOIN:
- INNER JOIN
- LEFT JOIN
- LEFT JOIN ohne Überschneidungen mit der rechten Tabelle
- RIGHT JOIN
- RIGHT JOIN ohne Überschneidungen mit der linken Tabelle
- FULL OUTER
- FULL OUTER, bei dem die linke oder rechte Tabelle leer ist
Und hier eine Illustration zu diesen JOIN-Arten:
Ich werde der Artikel Dateien unserer Website anhängen, unter denen sich auch join.php befindet, in denen ich alle Datensätze mit verschiedenen JOIN-Operatoren ausgeben werde.
INNER JOIN
Wir beginnen mit diesem Operator INNER JOIN, weil dieser Operator standardmäßig ausgeführt wird, wenn Sie in der Abfrage einfach JOIN schreiben. Dieser Operator wählt alle Datensätze aus zwei Tabellen aus, bei denen die Bedingung nach dem Operator ON erfüllt ist. Wir haben zwei Tabellen Files und Messages:
Tabelle Messages:
| mid | bodytext | fid |
| 1 | Test | NULL |
| 2 | Hi | 2 |
| 3 | Hello | NULL |
Tabelle Files:
| fid | path |
| 1 | /files/1.png |
| 2 | /files/2.png |
| 3 | /files/3.png |
Die Abfrage mit JOIN lautet wie folgt:
SELECT * FROM Messages INNER JOIN Files ON Messages.fid=Files.fid
Als Ergebnis werden folgende Datensätze ausgegeben:
| mid | bodytext | fid | path |
| 2 | Hi | 2 | /files/2.png |
Das heißt, dort, wo fid übereinstimmt, gibt MySQL diese Zeilen aus.
LEFT JOIN
Beim LEFT JOIN geben wir alle Datensätze aus, bei denen in der linken Tabelle (bei uns ist das Messages) alle Datensätze ausgegeben werden, einschließlich derjenigen, bei denen diese fid-Werte in der Tabelle Files vorhanden sind.
Tabelle Messages:
| mid | bodytext | fid |
| 1 | Test | 2 |
| 2 | Hi | NULL |
| 3 | Hello | 3 |
Tabelle Files:
| fid | path |
| 1 | /files/1.png |
| 2 | /files/2.png |
| 3 | /files/3.png |
Die Abfrage mit LEFT JOIN lautet wie folgt:
SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid
Als Ergebnis werden folgende Datensätze ausgegeben:
| mid | bodytext | fid | path |
| 1 | Test | 2 | /files/2.png |
| 2 | Hi | NULL | NULL |
| 3 | Hello | 3 | /files/3.png |
LEFT JOIN wird benötigt, wenn wir alle Nachrichten ausgeben wollen, und ob eine angehängte Datei vorhanden ist oder nicht, prüfen wir später in PHP.
LEFT JOIN ohne Überschneidungen mit der rechten Tabelle
LEFT JOIN gibt alle Datensätze aus der linken Tabelle aus, außer denen, bei denen fid in der rechten Tabelle übereinstimmt.
Tabelle Messages:
| mid | bodytext | fid |
| 1 | Test | 2 |
| 2 | Hi | NULL |
| 3 | Hello | 3 |
Tabelle Files:
| fid | path |
| 1 | /files/1.png |
| 2 | /files/2.png |
| 3 | /files/3.png |
Die Abfrage mit LEFT JOIN ohne Überschneidungen lautet wie folgt:
SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid WHERE Files.fid IS NULL
Als Ergebnis erhalten wir folgende Auswahl:
| mid | bodytext | fid | path |
| 2 | Hi | NULL | NULL |
LEFT JOIN ohne Überschneidungen wird benötigt, wenn wir alle Datensätze ohne angehängte Dateien ausgeben wollen.
RIGHT JOIN
RIGHT JOIN gibt alle Datensätze aus der rechten Tabelle aus; wenn Überschneidungen vorhanden sind, werden die Daten aus der linken Tabelle ausgegeben.
Tabelle Messages:
| mid | bodytext | fid |
| 1 | Test | 2 |
| 2 | Hi | NULL |
| 3 | Hello | 3 |
Tabelle Files:
| fid | path |
| 1 | /files/1.png |
| 2 | /files/2.png |
| 3 | /files/3.png |
Die Abfrage mit RIGHT JOIN lautet wie folgt:
SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid
Als Ergebnis erhalten wir folgende Auswahl:
| mid | bodytext | fid | path |
| NULL | NULL | 1 | /files/1.png |
| 1 | Test | 2 | /files/2.png |
| 3 | Hello | 3 | /files/3.png |
RIGHT JOIN wird benötigt, wenn wir alle angehängten Dateien ausgeben wollen, egal ob sie verwendet werden oder nicht, einfach alle Dateien.
RIGHT JOIN ohne Überschneidungen
RIGHT JOIN ohne Überschneidungen gibt alle Datensätze aus der rechten Tabelle aus, außer denen, die Überschneidungen mit der linken Tabelle haben.
Tabelle Messages:
| mid | bodytext | fid |
| 1 | Test | 2 |
| 2 | Hi | NULL |
| 3 | Hello | 3 |
Tabelle Files:
| fid | path |
| 1 | /files/1.png |
| 2 | /files/2.png |
| 3 | /files/3.png |
Die Abfrage mit RIGHT JOIN ohne Überschneidungen lautet wie folgt:
SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid WHERE Messages.fid IS NULL
So erhalten wir folgende Daten:
| mid | bodytext | fid | path |
| NULL | NULL | 1 | /files/1.png |
RIGHT JOIN wird benötigt, wenn wir alle angehängten Dateien ausgeben wollen, die an keine Nachrichten angehängt sind. Zum Beispiel, wenn wir Dateien ausgeben wollen, die nicht verwendet werden.
FULL OUTER JOIN
Obwohl es in der SQL-Sprache FULL OUTER JOIN gibt, existiert dieser Operator in MySQL nicht. Der Grund ist, dass ein solcher Operator eine enorme Last für den Server darstellt. Wir haben jetzt 3 Dateien und 3 Nachrichten, wodurch bei der Ausführung der Abfrage 4 Zeilen entstehen. Ich bin mir nicht sicher, ob es eine gute Idee ist, eine Abfrage zu schreiben, die insgesamt zwei Abfragen LEFT JOIN und RIGHT JOIN kombiniert. Aber es gibt dennoch die Möglichkeit, eine FULL OUTER JOIN-Abfrage zu emulieren.
Tabelle Messages:
| mid | bodytext | fid |
| 1 | Test | 2 |
| 2 | Hi | NULL |
| 3 | Hello | 3 |
Tabelle Files:
| fid | path |
| 1 | /files/1.png |
| 2 | /files/2.png |
| 3 | /files/3.png |
Die Emulation der FULL OUTER JOIN-Abfrage lautet wie folgt:
SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid UNION SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid
In dieser Abfrage verwenden wir den Operator UNION, um zwei Abfragen LEFT JOIN und RIGHT JOIN zu kombinieren.
Als Ergebnis erhalten wir folgende Datensätze:
| mid | bodytext | fid | path |
| 1 | Test | 2 | /files/2.png |
| 2 | Hi | NULL | NULL |
| 3 | Hello | 3 | /files/3.png |
| NULL | NULL | 1 | /files/1.png |
Und hier weiß ich schon nicht, wozu FULL OUTER JOIN benötigt wird. Aber da es in SQL existiert, wird es vermutlich irgendwann gebraucht.
FULL OUTER JOIN ohne Überschneidungen
Eine noch verrücktere Art von JOIN als der normale FULL OUTER JOIN ist der FULL OUTER JOIN ohne Überschneidungen. Ich kann nicht einmal vorschlagen, wo diese Art von JOIN verwendet werden könnte. Denn als Ergebnis erhalten wir Dateien, die nicht benutzt werden, und Nachrichten ohne Dateien. Und wie Sie sich wahrscheinlich schon gedacht haben, existiert auch dieser Operator nicht in MySQL. Er kann nur mit zwei Operatoren LEFT JOIN ohne Überschneidungen und RIGHT JOIN ohne Überschneidungen emuliert werden.
Emulation der FULL OUTER JOIN-Abfrage ohne Überschneidungen:
$sql = 'SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid WHERE Files.fid IS NULL UNION SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid WHERE Messages.fid IS NULL';
Als Ergebnis (die Ausgangstabellen sind dieselben wie im Beispiel mit FULL OUTER JOIN) erhalten wir:
| mid | bodytext | fid | path |
| 2 | Hi | NULL | NULL |
| NULL | NULL | 1 | /files/1.png |
Das wäre wohl alles, in den nächsten Lektionen werden wir noch komplexere Abfragen auf mehrere Tabellen gleichzeitig schreiben.