MySQL JOINS-Tutorial: INNER, OUTER, LINKS, RECHTS, KREUZ
Was sind JOINS?
Joins helfen beim Abrufen von Daten aus zwei oder mehr Datenbanktabellen.
Die Tabellen sind über Primär- und Fremdschlüssel miteinander verknüpft.
Hinweis: JOIN ist das am meisten missverstandene Thema unter SQL-Kennern. Aus Gründen der Einfachheit und des besseren Verständnisses werden wir eine neue Datenbank zum Üben von Beispielen verwenden. Wie nachfolgend dargestellt
id | Vorname | Familienname, Nachname | movie_id |
---|---|---|---|
1 | Marcus | Smith | 1 |
2 | Ravi | Kumar | 2 |
3 | Susan | Davidson | 5 |
4 | Jenny | Adrianna | 8 |
5 | Lee | Pong | 10 |
id | Titel | Kategorie |
---|---|---|
1 | ASSASSIN'S CREED: EMBERS | Animation |
2 | Echter Stahl (2012) | Animation |
3 | Alvin und die Chipmunks | Animation |
4 | Die Abenteuer von Tintin | Animation |
5 | Sicher (2012) | Action |
6 | Sicheres Haus (2012) | Action |
7 | GIA | 18+ |
8 | Frist 2009 | 18+ |
9 | Das schmutzige Bild | 18+ |
10 | Marley und ich | Romantik |
Arten von Verknüpfungen
Kreuz JOIN
Cross JOIN ist eine einfachste Form von JOINs, die jede Zeile einer Datenbanktabelle allen Zeilen einer anderen zuordnet.
Mit anderen Worten: Es liefert uns Kombinationen jeder Zeile der ersten Tabelle mit allen Datensätzen in der zweiten Tabelle.
Angenommen, wir möchten alle Mitgliedsdatensätze mit allen Filmdatensätzen vergleichen, können wir das unten gezeigte Skript verwenden, um die gewünschten Ergebnisse zu erzielen.
SELECT * FROM `movies` CROSS JOIN `members`
Ausführen des obigen Skripts in MySQL Werkbank gibt uns die folgenden Ergebnisse.
id | title | id | first_name | last_name | movie_id | |
---|---|---|---|---|---|---|
1 | ASSASSIN'S CREED: EMBERS | Animations | 1 | Adam | Smith | 1 |
1 | ASSASSIN'S CREED: EMBERS | Animations | 2 | Ravi | Kumar | 2 |
1 | ASSASSIN'S CREED: EMBERS | Animations | 3 | Susan | Davidson | 5 |
1 | ASSASSIN'S CREED: EMBERS | Animations | 4 | Jenny | Adrianna | 8 |
1 | ASSASSIN'S CREED: EMBERS | Animations | 6 | Lee | Pong | 10 |
2 | Real Steel(2012) | Animations | 1 | Adam | Smith | 1 |
2 | Real Steel(2012) | Animations | 2 | Ravi | Kumar | 2 |
2 | Real Steel(2012) | Animations | 3 | Susan | Davidson | 5 |
2 | Real Steel(2012) | Animations | 4 | Jenny | Adrianna | 8 |
2 | Real Steel(2012) | Animations | 6 | Lee | Pong | 10 |
3 | Alvin and the Chipmunks | Animations | 1 | Adam | Smith | 1 |
3 | Alvin and the Chipmunks | Animations | 2 | Ravi | Kumar | 2 |
3 | Alvin and the Chipmunks | Animations | 3 | Susan | Davidson | 5 |
3 | Alvin and the Chipmunks | Animations | 4 | Jenny | Adrianna | 8 |
3 | Alvin and the Chipmunks | Animations | 6 | Lee | Pong | 10 |
4 | The Adventures of Tin Tin | Animations | 1 | Adam | Smith | 1 |
4 | The Adventures of Tin Tin | Animations | 2 | Ravi | Kumar | 2 |
4 | The Adventures of Tin Tin | Animations | 3 | Susan | Davidson | 5 |
4 | The Adventures of Tin Tin | Animations | 4 | Jenny | Adrianna | 8 |
4 | The Adventures of Tin Tin | Animations | 6 | Lee | Pong | 10 |
5 | Safe (2012) | Action | 1 | Adam | Smith | 1 |
5 | Safe (2012) | Action | 2 | Ravi | Kumar | 2 |
5 | Safe (2012) | Action | 3 | Susan | Davidson | 5 |
5 | Safe (2012) | Action | 4 | Jenny | Adrianna | 8 |
5 | Safe (2012) | Action | 6 | Lee | Pong | 10 |
6 | Safe House(2012) | Action | 1 | Adam | Smith | 1 |
6 | Safe House(2012) | Action | 2 | Ravi | Kumar | 2 |
6 | Safe House(2012) | Action | 3 | Susan | Davidson | 5 |
6 | Safe House(2012) | Action | 4 | Jenny | Adrianna | 8 |
6 | Safe House(2012) | Action | 6 | Lee | Pong | 10 |
7 | GIA | 18+ | 1 | Adam | Smith | 1 |
7 | GIA | 18+ | 2 | Ravi | Kumar | 2 |
7 | GIA | 18+ | 3 | Susan | Davidson | 5 |
7 | GIA | 18+ | 4 | Jenny | Adrianna | 8 |
7 | GIA | 18+ | 6 | Lee | Pong | 10 |
8 | Deadline(2009) | 18+ | 1 | Adam | Smith | 1 |
8 | Deadline(2009) | 18+ | 2 | Ravi | Kumar | 2 |
8 | Deadline(2009) | 18+ | 3 | Susan | Davidson | 5 |
8 | Deadline(2009) | 18+ | 4 | Jenny | Adrianna | 8 |
8 | Deadline(2009) | 18+ | 6 | Lee | Pong | 10 |
9 | The Dirty Picture | 18+ | 1 | Adam | Smith | 1 |
9 | The Dirty Picture | 18+ | 2 | Ravi | Kumar | 2 |
9 | The Dirty Picture | 18+ | 3 | Susan | Davidson | 5 |
9 | The Dirty Picture | 18+ | 4 | Jenny | Adrianna | 8 |
9 | The Dirty Picture | 18+ | 6 | Lee | Pong | 10 |
10 | Marley and me | Romance | 1 | Adam | Smith | 1 |
10 | Marley and me | Romance | 2 | Ravi | Kumar | 2 |
10 | Marley and me | Romance | 3 | Susan | Davidson | 5 |
10 | Marley and me | Romance | 4 | Jenny | Adrianna | 8 |
10 | Marley and me | Romance | 6 | Lee | Pong | 10 |
INNER JOIN
Der innere JOIN wird verwendet, um Zeilen aus beiden Tabellen zurückzugeben, die die angegebene Bedingung erfüllen.
Angenommen, Sie möchten eine Liste der Mitglieder erhalten, die Filme ausgeliehen haben, zusammen mit den Titeln der von ihnen ausgeliehenen Filme. Sie können dafür einfach einen INNER JOIN verwenden, der Zeilen aus beiden Tabellen zurückgibt, die bestimmte Bedingungen erfüllen.
SELECT members.`first_name` , members.`last_name` , movies.`title` FROM members ,movies WHERE movies.`id` = members.`movie_id`
Führen Sie das obige Skript aus
first_name | last_name | title |
---|---|---|
Adam | Smith | ASSASSIN'S CREED: EMBERS |
Ravi | Kumar | Real Steel(2012) |
Susan | Davidson | Safe (2012) |
Jenny | Adrianna | Deadline(2009) |
Lee | Pong | Marley and me |
Beachten Sie, dass das obige Ergebnisskript auch wie folgt geschrieben werden kann, um die gleichen Ergebnisse zu erzielen.
SELECT A.`first_name` , A.`last_name` , B.`title` FROM `members`AS A INNER JOIN `movies` AS B ON B.`id` = A.`movie_id`
Äußere JOINs
MySQL Äußere JOINs geben alle übereinstimmenden Datensätze aus beiden Tabellen zurück.
Es kann Datensätze erkennen, die in der verknüpften Tabelle nicht übereinstimmen. Es kehrt zurück NULL Werte für Datensätze der verbundenen Tabelle, wenn keine Übereinstimmung gefunden wird.
Klingt verwirrend? Schauen wir uns ein Beispiel an –
LEFT JOIN
Angenommen, Sie möchten die Titel aller Filme zusammen mit den Namen der Mitglieder erhalten, die sie ausgeliehen haben. Es ist klar, dass einige Filme von niemandem ausgeliehen wurden. Wir können einfach verwenden LEFT JOIN zwecks.
Der LEFT JOIN gibt alle Zeilen der Tabelle links zurück, auch wenn in der Tabelle rechts keine passenden Zeilen gefunden wurden. Wenn in der Tabelle rechts keine Übereinstimmungen gefunden wurden, wird NULL zurückgegeben.
SELECT A.`title` , B.`first_name` , B.`last_name` FROM `movies` AS A LEFT JOIN `members` AS B ON B.`movie_id` = A.`id`
Ausführen des obigen Skripts in MySQL Workbench gibt. Sie können sehen, dass im zurückgegebenen Ergebnis, das unten aufgeführt ist, bei Filmen, die nicht ausgeliehen sind, die Felder für Mitgliedsnamen NULL-Werte haben. Das bedeutet, dass für diesen bestimmten Film kein passendes Mitglied in der Mitgliedertabelle gefunden wurde.
title | first_name | last_name |
---|---|---|
ASSASSIN'S CREED: EMBERS | Adam | Smith |
Real Steel(2012) | Ravi | Kumar |
Safe (2012) | Susan | Davidson |
Deadline(2009) | Jenny | Adrianna |
Marley and me | Lee | Pong |
Alvin and the Chipmunks | NULL | NULL |
The Adventures of Tin Tin | NULL | NULL |
Safe House(2012) | NULL | NULL |
GIA | NULL | NULL |
The Dirty Picture | NULL | NULL |
RICHTIG BEITRETEN
RIGHT JOIN ist offensichtlich das Gegenteil von LEFT JOIN. Der RIGHT JOIN gibt alle Spalten der rechten Tabelle zurück, auch wenn in der linken Tabelle keine passenden Zeilen gefunden wurden. Wenn in der Tabelle links keine Übereinstimmungen gefunden wurden, wird NULL zurückgegeben.
Nehmen wir in unserem Beispiel an, dass Sie Namen von Mitgliedern und von ihnen ausgeliehene Filme benötigen. Jetzt haben wir ein neues Mitglied, das noch keinen Film ausgeliehen hat
SELECT A.`first_name` , A.`last_name`, B.`title` FROM `members` AS A RIGHT JOIN `movies` AS B ON B.`id` = A.`movie_id`
Ausführen des obigen Skripts in MySQL Workbench liefert die folgenden Ergebnisse.
first_name | last_name | title |
---|---|---|
Adam | Smith | ASSASSIN'S CREED: EMBERS |
Ravi | Kumar | Real Steel(2012) |
Susan | Davidson | Safe (2012) |
Jenny | Adrianna | Deadline(2009) |
Lee | Pong | Marley and me |
NULL | NULL | Alvin and the Chipmunks |
NULL | NULL | The Adventures of Tin Tin |
NULL | NULL | Safe House(2012) |
NULL | NULL | GIA |
NULL | NULL | The Dirty Picture |
„ON“- und „USING“-Klauseln
In den obigen JOIN-Abfragebeispielen haben wir die ON-Klausel verwendet, um die Datensätze zwischen Tabellen abzugleichen.
Für den gleichen Zweck kann auch die USING-Klausel verwendet werden. Der Unterschied zu VERWENDUNG ist es muss identische Namen für übereinstimmende Spalten in beiden Tabellen haben.
Bisher haben wir in der Tabelle „Filme“ den Primärschlüssel mit dem Namen „id“ verwendet. Wir haben in der Tabelle „members“ mit dem Namen „movie_id“ darauf verwiesen.
Benennen wir das Feld „movies“ in der Tabelle „id“ um, sodass es den Namen „movie_id“ erhält. Wir tun dies, um identische übereinstimmende Feldnamen zu haben.
ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;
Als nächstes verwenden wir USING mit dem obigen LEFT JOIN-Beispiel.
SELECT A.`title` , B.`first_name` , B.`last_name` FROM `movies` AS A LEFT JOIN `members` AS B USING ( `movie_id` )
Abgesehen von der Verwendung ON und USING mit JOINs Sie können viele andere verwenden MySQL Klauseln wie GRUPPE NACH, WO und sogar Funktionen wie SUM, AVG, usw.
Warum sollten wir Joins verwenden?
Jetzt fragen Sie sich vielleicht, warum wir JOINs verwenden, wenn wir die gleiche Aufgabe auch beim Ausführen von Abfragen erledigen können. Insbesondere wenn Sie Erfahrung in der Datenbankprogrammierung haben, wissen Sie, dass wir Abfragen einzeln ausführen und die Ausgabe jeder Abfrage in aufeinanderfolgenden Abfragen verwenden können. Natürlich ist das möglich. Aber mit JOINs können Sie die Arbeit erledigen, indem Sie nur eine einzige Abfrage mit beliebigen Suchparametern verwenden. Andererseits MySQL kann eine bessere Leistung erzielen mit JOINs, da es die Indizierung nutzen kann. Durch die einfache Verwendung einer einzelnen JOIN-Abfrage anstelle der Ausführung mehrerer Abfragen wird der Serveraufwand reduziert. Die Verwendung mehrerer Abfragen führt stattdessen zu mehr Datenübertragungen zwischen ihnen MySQL und Anwendungen (Software). Darüber hinaus sind auch auf der Anwendungsseite weitere Datenmanipulationen erforderlich.
Es ist klar, dass wir mehr erreichen können MySQL und Anwendungsleistungen durch die Verwendung von JOINs.
Zusammenfassung
- Mit JOINS können wir Daten aus mehr als einer Tabelle in einem einzigen Ergebnissatz kombinieren.
- JOINS bieten im Vergleich zu Unterabfragen eine bessere Leistung
- INNER JOINS geben nur Zeilen zurück, die die angegebenen Kriterien erfüllen.
- OUTER JOINS können auch Zeilen zurückgeben, in denen keine Übereinstimmungen gefunden wurden. Die nicht übereinstimmenden Zeilen werden mit dem Schlüsselwort NULL zurückgegeben.
- Zu den wichtigsten JOIN-Typen gehören Inner, Left Outer, Right Outer, Cross JOINS usw.
- Die in JOIN-Operationen häufig verwendete Klausel ist „ON“. Die „USING“-Klausel erfordert, dass übereinstimmende Spalten denselben Namen haben.
- JOINS können auch in anderen Klauseln wie GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS usw. verwendet werden.