MySQL Verbinden: Innen, Außen, 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 eines der am häufigsten missverstandenen Themen unter SQL-Lernenden. Um die Sache zu vereinfachen und das Verständnis zu erleichtern, verwenden wir für die Beispielübung eine neue Datenbank. Wie unten dargestellt.

Alle folgenden Beispiele verwenden diese beiden Tabellen. movie_id Spalte in Mitglieder zeigt auf die id Spalte in Filme — die Beziehung, auf die jeder JOIN zutrifft.

Mitglieder

Filme

Warum sollten wir JOINS verwenden?

Bevor wir uns mit den einzelnen JOIN-Typen befassen, ist es hilfreich zu wissen, warum ein JOIN gegenüber der Ausführung mehrerer Abfragen bevorzugt wird.

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.

Arten von Joins

MySQL Es werden verschiedene JOIN-Typen unterstützt, die jeweils unterschiedliche Fragen zu denselben zwei Tabellen beantworten. Die folgende Tabelle vergleicht diese Typen; anschließend wird jeder Typ anhand einer Abfrage und ihrer Ausgabe demonstriert.

CROSS 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

Ein CROSS JOIN liefert alle möglichen Paarungen, was selten gewünscht ist. Ein INNER JOIN beschränkt das Ergebnis auf die tatsächlich zusammenhängenden Paare.

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. Dazu können Sie einfach einen INNER JOIN verwenden, der Zeilen aus beiden Tabellen zurückgibt, die die gegebenen 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

Ein INNER JOIN verwirft stillschweigend Zeilen ohne Partner. Wenn diese nicht übereinstimmenden Zeilen wichtig sind, ist ein OUTER JOIN die richtige Wahl.

MySQL Outer 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 Die Workbench liefert die Ergebnisse. Wie Sie im unten aufgeführten Ergebnis sehen können, enthalten die Felder für die Mitgliedsnamen bei Filmen, die nicht ausgeliehen sind, den Wert NULL. Das bedeutet, dass in der Mitgliedertabelle kein passendes Mitglied für diesen Film 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

Bisher hat jede Abfrage Zeilen mit einer ON-Klausel gefunden. MySQL bietet eine kürzere Alternative, wenn die übereinstimmenden Spalten den gleichen Namen haben.

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 GRUPPIERE NACH, WO und sogar Funktionen wie SUM, AVG, usw.

Häufig gestellte Fragen