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



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

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