Was ist der Unterschied zwischen "INNER JOIN" und "OUTER JOIN"?

Question

Außerdem, wie passen LEFT OUTER JOIN, RIGHT OUTER JOIN und FULL OUTER JOIN zusammen?

Answer 1

Vorausgesetzt, Sie verbinden sich auf Spalten ohne Duplikate, was ein sehr häufiger Fall ist:

• Ein Inner Join von A und B ergibt das Ergebnis von A Schnittmenge B, d.h. den inneren Teil eines Venn-Diagramms Schnittmenge.

• Ein Outer Join von A und B ergibt das Ergebnis von A Vereinigung B, d.h. die äußeren Teile eines Venn-Diagramms Vereinigung.

Beispiele

Angenommen, Sie haben zwei Tabellen, jeweils mit einer Spalte, und Daten wie folgt:

A    B
-    -
1    3
2    4
3    5
4    6

Beachten Sie, dass (1,2) eindeutig für A sind, (3,4) gemeinsam sind, und (5,6) eindeutig für B sind.

Innerer Join

Ein innerer Join mit einer der äquivalenten Abfragen ergibt den Schnittpunkt der beiden Tabellen, d.h. die beiden Zeilen, die sie gemeinsam haben.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Linkes äußeres Join

Ein linkes äußeres Join wird alle Zeilen in A ergeben, plus gemeinsame Zeilen in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Rechtes äußeres Join

Ein rechtes äußeres Join wird alle Zeilen in B ergeben, plus gemeinsame Zeilen in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Voller äußerer Join

Ein voller äußerer Join wird Ihnen die Vereinigung von A und B ergeben, d.h. alle Zeilen in A und alle Zeilen in B. Wenn in A etwas kein entsprechendes Datum in B hat, dann ist der B-Teil null, und umgekehrt.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Answer 2

Die Venn-Diagramme überzeugen mich nicht wirklich.

Sie zeigen keine Unterscheidung zwischen einem Cross Join und einem Inner Join, zum Beispiel, oder zeigen allgemein keine Unterscheidung zwischen verschiedenen Arten von Join-Prädikaten oder bieten einen Rahmen für das Nachdenken darüber, wie sie funktionieren werden.

Es gibt keinen Ersatz für das Verständnis der logischen Verarbeitung und es ist relativ einfach zu erfassen.

1. Stellen Sie sich einen Cross Join vor.
2. Werten Sie die on Klausel gegen alle Zeilen aus Schritt 1 aus, wobei die Prädikate ausgewertet werden müssen, die auf true zutreffen
3. (Für äußere Joins) fügen Sie alle äußeren Zeilen wieder in Schritt 2 ein, die verloren gegangen sind.

(Anmerkung: In der Praxis kann der Abfrageoptimierer effizientere Möglichkeiten finden, die Abfrage auszuführen, als die rein logische Beschreibung oben, aber das Endresultat muss dasselbe sein)

ICh beginne mit einer animierten Version eines Full Outer Join. Weitere Erklärungen folgen.

---

Erklärung

Quelltabelle/n

Beginnen Sie zunächst mit einem CROSS JOIN (auch Kreuzprodukt genannt). Dies hat keine ON Klausel und gibt einfach jede Kombination von Zeilen aus den beiden Tabellen zurück.

SELECT A.Colour, B.Colour FROM A CROSS JOIN B

Inner und äußere Joins haben ein "ON" Klausel-Prädikat.

• Inner Join. Bewertung der Bedingung in der "ON" Klausel für alle Zeilen im Ergebnis des Cross Joins. Wenn wahr, wird die verbundene Zeile zurückgegeben. Andernfalls wird sie verworfen.
• Left Outer Join. Das Gleiche wie beim Inner Join, dann werden für alle Zeilen in der linken Tabelle, die nicht übereinstimmen, NULL-Werte für die rechten Tabellenspalten ausgegeben.
• Right Outer Join. Das Gleiche wie beim Inner Join, dann werden für alle Zeilen in der rechten Tabelle, die nicht übereinstimmen, NULL-Werte für die linken Tabellenspalten ausgegeben.
• Full Outer Join. Das Gleiche wie beim Inner Join, dann werden links nicht übereinstimmende Zeilen wie beim Left Outer Join und rechts nicht übereinstimmende Zeilen wie beim Right Outer Join erhalten.

Einige Beispiele

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour = B.Colour

Das obige ist der klassische Gleichheitsjoin.

Animierte Version

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN ('Green','Blue')

Die Inner Join-Bedingung muss nicht unbedingt eine Gleichheitsbedingung sein und sie muss nicht auf Spalten aus beiden (oder sogar einer) der Tabellen verweisen. Die Auswertung von A.Colour NOT IN ('Green','Blue') in jeder Zeile des Cross Joins ergibt.

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON 1 = 1

Die Join-Bedingung ergibt wahr für alle Zeilen im Ergebnis des Cross Joins, daher ist dies genau wie ein Cross Join. Ich werde das Bild der 16 Zeilen nicht wiederholen.

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour

Äußere Joins werden logisch genauso wie Innere Joins ausgewertet, mit der Ausnahme, dass eine Zeile aus der linken Tabelle (bei einem Linken Join), die überhaupt nicht mit einer Zeile aus der rechten Tabelle übereinstimmt, im Ergebnis mit NULL Werten für die Spalten der rechten Tabelle erhalten bleibt.

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL

Dies beschränkt das vorherige Ergebnis einfach darauf, nur die Zeilen zurückzugeben, bei denen B.Colour IS NULL ist. In diesem Fall handelt es sich um die Zeilen, die erhalten wurden, da sie keine Übereinstimmung in der rechten Tabelle hatten, und die Abfrage gibt die einzelne rote Zeile zurück, die nicht mit der Tabelle B übereinstimmte. Dies wird als Anti-Semi-Join bezeichnet.

Es ist wichtig, eine Spalte für den IS NULL Test auszuwählen, die entweder nicht NULL-fähig ist oder für die die Join-Bedingung sicherstellt, dass alle NULL Werte ausgeschlossen werden, um zu vermeiden, einfache zeilen zurückzubringen, die zufällig einen NULL-Wert für diese Spalte haben in Zusatz zu den nicht übereinstimmenden Reihen.

SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour

Rechte äußere Joins wirken ähnlich wie linke äußere Joins, mit der Ausnahme, dass sie nicht übereinstimmende Zeilen aus der rechten Tabelle bewahren und die linken Spalten mit NULL erweitern.

SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON A.Colour = B.Colour

Vollständige äußere Joins kombinieren das Verhalten von linken und rechten Joins und bewahren die nicht übereinstimmenden Zeilen aus beiden linken und rechten Tabellen.

SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON 1 = 0

Keine Zeilen im Cross Join entsprechen dem 1=0 Prädikat. Alle Zeilen auf beiden Seiten werden unter Verwendung der normalen äußeren Join-Regeln beibehalten, wobei in den Spalten der Tabelle auf der anderen Seite NULL verwendet wird.

SELECT COALESCE(A.Colour, B.Colour) AS Colour FROM A FULL OUTER JOIN B ON 1 = 0

Mit einer geringfügigen Änderung an der vorherigen Abfrage könnte man eine UNION ALL der beiden Tabellen simulieren.

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour = 'Green'

Beachten Sie, dass die WHERE Klausel (falls vorhanden) logisch nach dem Join ausgeführt wird. Ein häufiger Fehler besteht darin, einen linken äußeren Join durchzuführen und dann eine WHERE-Klausel mit einer Bedingung auf der rechten Tabelle einzuschließen, die letztendlich die nicht übereinstimmenden Zeilen ausschließt. Das obige führt den äußeren Join durch...

...Und dann wird die "Where" Klausel ausgeführt. NULL= 'Green' ergibt nicht wahr, daher wird die durch den äußeren Join erhaltene Zeile verworfen (zusammen mit der blauen), wodurch der Join effektiv wieder in einen inneren umgewandelt wird.

Wenn die Absicht war, nur Zeilen aus B einzuschließen, bei denen die Farbe Grün ist und alle Zeilen aus A unabhängig davon, wäre die richtige Syntax:

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour AND B.Colour = 'Green'

SQL Fiddle

Sehen Sie sich diese Beispiele live bei SQLFiddle.com.

Answer 3

Joins werden verwendet, um die Daten aus zwei Tabellen zu kombinieren, wobei das Ergebnis eine neue, temporäre Tabelle ist. Joins werden basierend auf etwas namens Prädikat durchgeführt, das die Bedingung angibt, die verwendet werden soll, um einen Join durchzuführen. Der Unterschied zwischen einem inneren Join und einem äußeren Join besteht darin, dass ein innerer Join nur die Zeilen zurückgibt, die tatsächlich auf der Grundlage des Join-Prädikats übereinstimmen. Zum Beispiel - Betrachten wir die Tabellen Employee und Location:

Employee

EmpID

EmpName

13

Jason

8

Alex

3

Ram

17

Babu

25

Johnson

Location

EmpID

EmpLoc

13

San Jose

8

Los Angeles

3

Pune, India

17

Chennai, India

39

Bangalore, India

Inner Join:- Ein innerer Join erstellt eine neue Ergebnistabelle, indem die Spaltenwerte der beiden Tabellen (Employee und Location) basierend auf dem Join-Prädikat kombiniert werden. Die Abfrage vergleicht jede Zeile von Employee mit jeder Zeile von Location, um alle Paare von Zeilen zu finden, die das Join-Prädikat erfüllen. Wenn das Join-Prädikat durch Übereinstimmung von nicht-NULL-Werten erfüllt ist, werden die Spaltenwerte für jedes übereinstimmende Paar von Zeilen von Employee und Location zu einer Ergebniszeile kombiniert. So sieht der SQL-Code für einen inneren Join aus:

select  * from employee inner join location on employee.empID = location.empID
ODER
select  * from employee, location where employee.empID = location.empID

Das Ergebnis des Ausführens dieses SQL-Codes würde folgendermaßen aussehen:

Employee.EmpId

Employee.EmpName

Location.EmpId

Location.EmpLoc

13

Jason

13

San Jose

8

Alex

8

Los Angeles

3

Ram

3

Pune, India

17

Babu

17

Chennai, India

Outer Join:- Ein äußerer Join erfordert nicht, dass jeder Datensatz in den beiden verbundenen Tabellen einen übereinstimmenden Datensatz hat. Die verbundene Tabelle behält jeden Datensatz bei, auch wenn kein anderer übereinstimmender Datensatz vorhanden ist. Äußere Joins unterteilen sich weiter in linke äußere Joins und rechte äußere Joins, abhängig davon, welche Zeilen der Tabelle behalten werden (links oder rechts).

Linker äußerer Join:- Das Ergebnis eines linken äußeren Joins (oder einfach linken Joins) für die Tabellen Employee und Location enthält immer alle Datensätze der "linken" Tabelle (Employee), auch wenn die Join-Bedingung keinen übereinstimmenden Datensatz in der "rechten" Tabelle (Location) findet. So sieht der SQL-Code für einen linken äußeren Join aus, der die obigen Tabellen verwendet:

select  * from employee left outer join location on employee.empID = location.empID;
//Verwendung des äußeren Schlüsselworts ist optional

Das Ergebnis des Ausführens dieses SQL-Codes würde folgendermaßen aussehen:

Employee.EmpId

Employee.EmpName

Location.EmpId

Location.EmpLoc

13

Jason

13

San Jose

8

Alex

8

Los Angeles

3

Ram

3

Pune, India

17

Babu

17

Chennai, India

25

Johnson

NULL

NULL

Beachten Sie, dass obwohl Johnson keinen Eintrag in der Employee-Location-Tabelle hat, er dennoch in den Ergebnissen enthalten ist, aber die Standortfelder sind leer.

Rechter äußerer Join:- Ein rechter äußerer Join (oder rechter Join) ähnelt einem linken äußeren Join, allerdings mit umgekehrter Behandlung der Tabellen. Jede Zeile der "rechten" Tabelle (Location) erscheint mindestens einmal in der verbundenen Tabelle. Wenn keine übereinstimmende Zeile aus der "linken" Tabelle (Employee) vorhanden ist, wird NULL in den Spalten von Employee für die Datensätze erscheinen, die keine Übereinstimmung in Location haben. So sieht der SQL-Code dafür aus:

select * from employee right outer join location  on employee.empID = location.empID;
//Verwendung des äußeren Schlüsselworts ist optional

Anhand der obigen Tabellen können wir zeigen, wie das Ergebnis eines rechten äußeren Joins aussieht:

Employee.EmpId

Employee.EmpName

Location.EmpId

Location.EmpLoc

13

Jason

13

San Jose

8

Alex

8

Los Angeles

3

Ram

3

Pune, India

17

Babu

17

Chennai, India

NULL

NULL

39

Bangalore, India

Beachten Sie, dass obwohl keine Mitarbeiter in Bangalore aufgeführt sind, es dennoch in den Ergebnissen enthalten ist und die Mitarbeiterfelder leer sind.

Vollständige äußere Joins:- Ein voller äußerer Join oder Vollverbundenheit besteht darin, die nicht übereinstimmenden Informationen zu erhalten, indem nicht übereinstimmende Zeilen in den Ergebnissen eines Joins eingeschlossen werden. Es werden alle Zeilen aus beiden Tabellen eingeschlossen, unabhängig davon, ob die andere Tabelle einen übereinstimmenden Wert hat.

Employee.EmpId

Employee.EmpName

Location.EmpId

Location.EmpLoc

13

Jason

13

San Jose

8

Alex

8

Los Angeles

3

Ram

3

Pune, India

17

Babu

17

Chennai, India

25

Johnson

NULL

NULL

NULL

NULL

39

Bangalore, India

MySQL 8.0 Referenzhandbuch - Join-Syntax

Oracle Join-Operationen

Answer 4

Inner Join

Nur die übereinstimmenden Zeilen abrufen, das heißt A intersect B.

SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

---

Left Outer Join

Wählen Sie alle Datensätze aus der ersten Tabelle aus und alle Datensätze in der zweiten Tabelle, die den verbundenen Schlüsseln entsprechen.

SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

---

Full Outer Join

Wählen Sie alle Datensätze aus der zweiten Tabelle aus und alle Datensätze in der ersten Tabelle, die den verbundenen Schlüsseln entsprechen.

SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

---

Quellen

• Beispiele für innere und äußere Joins und den Join-Block in SQL

• SQL: JOINS

Answer 5

In einfachen Worten:

Ein inner join ruft nur die übereinstimmenden Zeilen ab.

Ein outer join ruft die übereinstimmenden Zeilen aus einer Tabelle und alle Zeilen aus einer anderen Tabelle ab.... das Ergebnis hängt davon ab, welchen Sie verwenden:

• Left: Übereinstimmende Zeilen in der rechten Tabelle und alle Zeilen in der linken Tabelle

• Right: Übereinstimmende Zeilen in der linken Tabelle und alle Zeilen in der rechten Tabelle oder

• Full: Alle Zeilen in allen Tabellen. Es spielt keine Rolle, ob es eine Übereinstimmung gibt oder nicht