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.

Arten von Verknüpfungen

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.

INNER JOIN

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.

Äußere JOINs

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
Note: Null is returned for non-matching rows on right

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

RICHTIG BEITRETEN

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
Note: Null is returned for non-matching rows on left

„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.