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


Auch wie LEFT JOIN, RIGHT JOIN und FULL JOIN sich einfügen?


4014
2017-09-01 22:36


Ursprung


Antworten:


Vorausgesetzt, Sie schließen sich an Spalten ohne Duplikate an, was ein sehr häufiger Fall ist:

  • Eine innere Verbindung von A und B gibt das Ergebnis von A intersect B, d. H. Den inneren Teil von a Venn-Diagramm Überschneidung.

  • Eine äußere Verbindung von A und B ergibt die Ergebnisse von A union B, d. H. Die äußeren Teile einer Venn-Diagramm-Vereinigung.

Beispiele

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

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

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

Innere Verbindung

Ein innerer Join, der eine der äquivalenten Abfragen verwendet, ergibt die Schnittmenge der zwei Tabellen, d. H. Die zwei 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

Linke äußere Verbindung

Ein linker äußerer Join gibt alle Zeilen in A sowie alle gemeinsamen Zeilen in B an.

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

Rechts äußerer Anschluss

Ein rechter äußerer Join gibt alle Zeilen in B sowie alle gemeinsamen Zeilen in A an.

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

Volle äußere Verbindung

Ein vollständiger äußerer Join gibt Ihnen die Vereinigung von A und B, dh alle Zeilen in A und alle Zeilen in B. Wenn etwas in A kein entsprechendes Datum in B hat, dann ist der B-Teil null und umgekehrt versa.

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

5420
2017-09-01 22:59



Sie können auch das folgende Schema für verschiedene Join-Typen berücksichtigen.

visual explanation of joins

Quelle: Visual-Repräsentation von SQL-Joins im Detail erklärt von C.L. Moffatt


2441
2018-05-16 23:03



ich empfehle Jeffs Blogartikel. Die beste Beschreibung, die ich je gesehen habe, plus eine Visualisierung, z. B .:

Innerer Join:

enter image description here

Vollständiger äußerer Join:

enter image description here


591
2017-08-30 11:52



Die Venn-Diagramme machen das nicht wirklich für mich.

Sie zeigen z. B. keinen Unterschied zwischen einem Kreuz-Join und einem Innen-Join oder zeigen allgemeiner eine Unterscheidung zwischen verschiedenen Arten von Join-Prädikaten oder bieten einen Rahmen für die Argumentation darüber, wie sie funktionieren werden.

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

  1. Stellen Sie sich eine Kreuzverbindung vor.
  2. Bewerten on Klausel gegen alle Zeilen von Schritt 1, wobei diejenigen beibehalten werden, bei denen das Prädikat auswertet true
  3. (Nur für äußere Joins) Fügen Sie alle äußeren Zeilen zurück, die in Schritt 2 verloren gegangen sind.

(Hinweis: In der Praxis kann der Abfrageoptimierer effizientere Möglichkeiten zum Ausführen der Abfrage finden als die rein logische Beschreibung oben, aber das Endergebnis muss identisch sein)

Ich beginne mit einer animierten Version eines volle äußere Verbindung. Weitere Erklärung folgt.

enter image description here


Erläuterung

Quellentabellen

enter link description here

Beginnen Sie zuerst mit einem CROSS JOIN (AKA kartesisches Produkt). Dies hat keine ON Klausel und gibt einfach jede Permutation von Zeilen aus den beiden Tabellen zurück.

SELECT A.Colour, B.Colour VON EINEM CROSS JOIN B

enter link description here

Innerer und äußerer Join haben ein "ON" -Klauselprädikat.

  • Innerer Beitritt Bewerten Sie die Bedingung in der "ON" -Klausel für alle Zeilen im Cross-Join-Ergebnis. Wenn true, gebe die verbundene Zeile zurück. Ansonsten verwerfen Sie es.
  • Linke äußere Verbindung. Genau wie innerer Join, dann für alle Zeilen in der linken Tabelle, die nichts passierten, geben Sie diese mit NULL-Werten für die richtigen Tabellenspalten aus.
  • Rechts außen verbinden Genau wie innerer Join, dann für alle Zeilen in der rechten Tabelle, die nichts passierten, geben Sie diese mit NULL-Werten für die linken Tabellenspalten aus.
  • Vollständige Äußere Verbindung. Wie Innerer Join, dann werden linke nicht übereinstimmende Zeilen wie in der linken äußeren Verknüpfung und rechte nicht übereinstimmende Zeilen gemäß der rechten äußeren Verknüpfung beibehalten.

Einige Beispiele

SELECT A.Colour, B.Colour VON EINEM INNEREN JOIN B AUF A.Colour = B.Colour

Das oben genannte ist der klassische Equi-Join.

Inner Join

Animierte Version

enter image description here

SELECT A.Colour, B.Colour VON EINEM INNEREN JOIN B AUF A.Farbe NICHT IN ('Grün', 'Blau')

Die innere Join-Bedingung muss nicht notwendigerweise eine Gleichheitsbedingung sein, und sie muss keine Spalten von beiden (oder sogar keiner) der Tabellen referenzieren. Bewertung A.Colour NOT IN ('Green','Blue') In jeder Zeile des Kreuzes wird der Join zurückgegeben.

inner 2

SELECT A.Colour, B.Colour VON EINEM INNEREN JOIN B ON 1 = 1

Die Join-Bedingung wird für alle Zeilen im Cross-Join-Ergebnis als wahr ausgewertet, so dass dies genau wie bei einem Cross-Join ist. Ich werde das Bild der 16 Zeilen nicht noch einmal wiederholen.

WÄHLEN SIE A.Farbe, B.Farbe VON EINEM LINKEN ÄUSSEREN BUCHEN AN A.Farbe = B.Farbe

Äußere Joins werden logisch wie innere Joins ausgewertet, außer dass eine Zeile aus der linken Tabelle (für einen linken Join) nicht mit irgendwelchen Zeilen aus der rechten Tabelle verknüpft wird, sondern im Ergebnis mit NULL Werte für die rechten Spalten.

LOJ

SELECT A.Colour, B.Colour VON LINKS OUTER JOIN B ON A.Colour = B.Farbe WHERE B.Colour IS NULL

Dies schränkt das vorherige Ergebnis einfach so ein, dass nur die Zeilen wo zurückgegeben werden B.Colour IS NULL. In diesem speziellen Fall sind dies die Zeilen, die beibehalten wurden, da sie keine Übereinstimmung in der rechten Tabelle hatten, und die Abfrage gibt die einzelne rote Zeile zurück, die in der Tabelle nicht übereinstimmt B. Dies wird als Anti-Semi-Join bezeichnet.

Es ist wichtig, eine Spalte für die zu wählen IS NULL Test, der entweder nicht nullfähig ist oder für den die Join-Bedingung dies gewährleistet NULL Werte werden ausgeschlossen, damit dieses Muster korrekt funktioniert und es vermieden wird, nur Zeilen zurück zu holen, die zufällig eine haben NULL Wert für diese Spalte zusätzlich zu den nicht übereinstimmenden Zeilen.

loj is null

WÄHLEN SIE A.Farbe, B.Farbe VON EINEM RECHTEN AUSSENBEREICH B AN A.Farbe = B.Farbe

Rechte äußere Verknüpfungen funktionieren ähnlich wie linke äußere Verknüpfungen, außer dass sie nicht übereinstimmende Zeilen von der rechten Tabelle beibehalten und Null die linken Spalten erweitern.

ROJ

WÄHLEN SIE A.Farbe, B.Farbe VON EINEM FULL OUTER JOIN B AN A.Colour = B.Colour

Vollständige äußere Joins kombinieren das Verhalten von linken und rechten Joins und behalten die nicht übereinstimmenden Zeilen sowohl der linken als auch der rechten Tabelle bei.

FOJ

WÄHLEN SIE A.Farbe, B.Farbe VON EINEM FULL OUTER JOIN B ON 1 = 0

Keine Zeilen in der Kreuzverbindung passen zu der 1=0 Prädikat. Alle Zeilen von beiden Seiten werden unter Verwendung normaler Outer-Join-Regeln mit NULL in den Spalten der Tabelle auf der anderen Seite beibehalten.

FOJ 2

WÄHLEN SIE COALESCE (A.Farbe, B.Farbe) ALS FARBE VON EINEM VOLLEN AUSSENBEREICH B ON 1 = 0

Mit einer kleinen Änderung der vorhergehenden Abfrage könnte man a UNION ALL der zwei Tabellen.

UNION ALL

SELECT A.Colour, B.Colour VON LINKS OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour = 'Grün'

Notiere dass der WHERE Klausel (falls vorhanden) wird nach dem Join logisch ausgeführt. Ein häufiger Fehler besteht darin, einen linken äußeren Join auszuführen und dann eine WHERE-Klausel mit einer Bedingung in die rechte Tabelle aufzunehmen, die die nicht übereinstimmenden Zeilen ausschließt. Das obige endet mit der äußeren Verbindung ...

LOJ

... Und dann läuft die "Where" -Klausel. NULL= 'Green' wird nicht als wahr ausgewertet, so dass die von der äußeren Verknüpfung beibehaltene Zeile (zusammen mit der blauen) verworfen wird, wodurch die Verknüpfung effektiv in eine innere umgewandelt wird.

LOJtoInner 

Wenn beabsichtigt wäre, nur Zeilen von B einzufügen, bei denen Farbe Grün ist und alle Zeilen von A unabhängig von der korrekten Syntax

SELECT A.Colour, B.Colour VON LINKS OUTER BINO ON A.Colour = B.Colour UND B.Colour = 'Green'

enter image description here

SQL-Geige

Siehe diese Beispiele live auf SQLFiddle.com laufen.


526
2017-12-13 11:58



Folgendes wurde aus dem Artikel entnommen:MySQL - LINKE JOIN und RECHTE JOIN, INNER JOIN und OUTER JOIN"von Graham Ellis in seinem Blog Horse's Mouth.

In einer Datenbank wie MySQL werden die Daten in eine Anzahl von Tabellen aufgeteilt, die dann verbunden werden (Joined) zusammen durch JOIN im SELECT Befehle zum Lesen von Datensätzen aus mehreren Tabellen. Lesen Sie dieses Beispiel, um zu sehen, wie es funktioniert.

Zuerst einige Beispieldaten:

people
    mysql> select * from people;
    +------------+--------------+------+
    | name       | phone        | pid  |
    +------------+--------------+------+
    | Mr Brown   | 01225 708225 |    1 |
    | Miss Smith | 01225 899360 |    2 |
    | Mr Pullen  | 01380 724040 |    3 |
    +------------+--------------+------+
    3 rows in set (0.00 sec)

property
    mysql> select * from property;
    +------+------+----------------------+
    | pid  | spid | selling              |
    +------+------+----------------------+
    |    1 |    1 | Old House Farm       |
    |    3 |    2 | The Willows          |
    |    3 |    3 | Tall Trees           |
    |    3 |    4 | The Melksham Florist |
    |    4 |    5 | Dun Roamin           |
    +------+------+----------------------+
    5 rows in set (0.00 sec)

REGELMÄSSIGE VERBINDUNG

Wenn wir einen regulären JOIN (mit keinem der Schlüsselwörter INNER, OUTER, LEFT oder RIGHT) ausführen, erhalten wir alle Datensätze, die in den beiden Tabellen in der entsprechenden Weise übereinstimmen, und Datensätze in beiden eingehenden Tabellen, die nicht übereinstimmen, werden nicht gemeldet :

mysql> select name, phone, selling 
from people join property 
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+-----------+--------------+----------------------+
4 rows in set (0.01 sec)

LINKE VERBINDUNG

Wenn wir einen LINKEN JOIN erstellen, erhalten wir alle Datensätze, die auf die gleiche Weise übereinstimmen, und ZUSÄTZLICH erhalten wir einen zusätzlichen Datensatz für jeden nicht übereinstimmenden Datensatz in der linken Tabelle des Joins - wodurch sichergestellt wird (in diesem Beispiel), dass jeder PERSON eine Erwähnung erhält :

   mysql> select name, phone, selling 
    from people left join property 
    on people.pid = property.pid; 
    +------------+--------------+----------------------+
    | name       | phone        | selling              |
    +------------+--------------+----------------------+
    | Mr Brown   | 01225 708225 | Old House Farm       |
    | Miss Smith | 01225 899360 | NULL <<-- unmatch    |
    | Mr Pullen  | 01380 724040 | The Willows          |
    | Mr Pullen  | 01380 724040 | Tall Trees           |
    | Mr Pullen  | 01380 724040 | The Melksham Florist |
    +------------+--------------+----------------------+
    5 rows in set (0.00 sec)

RECHT JOIN

Wenn wir einen RECHTEN JOIN machen, erhalten wir alle Datensätze, die übereinstimmen, und zusätzlich einen zusätzlichen Datensatz für jeden nicht übereinstimmenden Datensatz in der rechten Tabelle des Joins - in meinem Beispiel bedeutet dies, dass jede Eigenschaft eine Erwähnung erhält, selbst wenn wir dies nicht tun Verkäuferdetails haben:

mysql> select name, phone, selling 
from people right join property 
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
| NULL      | NULL         | Dun Roamin           |
+-----------+--------------+----------------------+
5 rows in set (0.00 sec)

Ein INNER JOIN führt einen vollständigen Join durch, genau wie das erste Beispiel, und das Wort OUTER kann in den letzten beiden Beispielen nach dem Wort LEFT oder RIGHT eingefügt werden - es ist für die ODBC-Kompatibilität vorgesehen und fügt keine zusätzlichen Funktionen hinzu.


291
2018-02-14 05:53



Innerer Beitritt

Rufen Sie nur die übereinstimmenden Zeilen ab, dh A intersect B.

Enter image description here

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

Linke äußere Verbindung

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

Enter image description here

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

Vollständige Äußere Verbindung

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

Enter image description here

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

Verweise


114
2018-01-27 12:16



Verbindet werden verwendet, um die Daten von zwei Tabellen zu kombinieren, mit dem Ergebnis einer neuen, temporären Tabelle. Joins werden basierend auf einem so genannten Prädikat ausgeführt, das die Bedingung angibt, die zum Ausführen eines Joins verwendet werden soll. Der Unterschied zwischen einem inneren Join und einem äußeren Join besteht darin, dass ein innerer Join nur die Zeilen zurückgibt, die basierend auf dem Join-Prädikat tatsächlich übereinstimmen. Betrachten wir Mitarbeiter- und Standorttabelle:

enter image description here

Innerer Anschluss: - Innerer Join erstellt eine neue Ergebnistabelle, indem Spaltenwerte zweier Tabellen kombiniert werden (Mitarbeiter und Ort) basierend auf dem Join-Prädikat. Die Abfrage vergleicht jede Zeile von Mitarbeiter mit jeder Reihe von Ort um alle Paare von Zeilen zu finden, die das Join-Prädikat erfüllen. Wenn das Join-Prädikat erfüllt ist, indem Nicht-NULL-Werte abgeglichen werden, werden die Spaltenwerte für jedes übereinstimmende Paar von Zeilen von Mitarbeiter und Ort werden zu einer Ergebniszeile zusammengefasst. So sieht das SQL für einen inneren Join aus:

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

Nun, hier ist, was das Ergebnis der Ausführung von SQL aussehen würde: enter image description here enter image description here

Äußere Verbindung: - Ein äußerer Join erfordert nicht, dass jeder Datensatz in den beiden verbundenen Tabellen einen übereinstimmenden Datensatz aufweist. Die verbundene Tabelle behält jeden Datensatz bei, selbst wenn kein anderer übereinstimmender Datensatz existiert. Outer-Joins gliedern sich weiter in linke Outer-Joins und rechte Outer-Joins, abhängig davon, welche Tabellenzeilen erhalten bleiben (links oder rechts).

Linke äußere Verbindung:- Das Ergebnis einer linken äußeren Verknüpfung (oder einfach linken Verknüpfung) für Tabellen Mitarbeiter und Ort enthält immer alle Datensätze der "linken" Tabelle (Mitarbeiter), auch wenn die Join-Bedingung keinen passenden Eintrag in der "richtigen" Tabelle findet (Ort). Hier sehen Sie, wie die SQL für einen linken äußeren Join aussieht, indem Sie die obigen Tabellen verwenden:

select  * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional

Nun, hier ist, was das Ergebnis der Ausführung dieses SQL aussehen würde: enter image description here enter image description here

Rechts äußerer Anschluss: - Eine rechte äußere Verbindung (oder rechte Verbindung) ähnelt stark einer linken äußeren Verbindung, außer bei umgekehrter Behandlung der Tabellen. Jede Zeile von der "richtigen" Tabelle (Ort) erscheint mindestens einmal in der verbundenen Tabelle. Wenn keine passende Zeile aus der "linken" Tabelle (Mitarbeiter) existiert, NULL erscheint in Spalten von Mitarbeiter für die Datensätze, die keine Übereinstimmung haben Ort. So sieht das SQL aus:

select * from employee right outer join location  on employee.empID = location.empID;
//Use of outer keyword is optional

Mit den obigen Tabellen können wir zeigen, wie die Ergebnismenge eines rechten äußeren Joins aussehen würde:

enter image description hereenter image description here

Volle äußere Verbindungen: - Vollständiger äußerer Join oder vollständiger Join besteht darin, die nicht übereinstimmenden Informationen beizubehalten, indem nicht übereinstimmende Zeilen in die Ergebnisse eines Joins aufgenommen werden. Verwenden Sie einen vollständigen äußeren Join. Sie enthält alle Zeilen aus beiden Tabellen, unabhängig davon, ob die andere Tabelle einen übereinstimmenden Wert aufweist oder nicht. enter image description here

Bildquelle

MySQL 8.0 Referenzhandbuch - Join-Syntax


108
2017-12-18 06:54



In einfachen Worten:

Ein innere Verbindung Nur die übereinstimmenden Zeilen abrufen.

Während a äußere Verbindung Abrufen der übereinstimmenden Zeilen aus einer Tabelle und aller Zeilen in einer anderen Tabelle .... Das Ergebnis hängt davon ab, welche Sie verwenden:

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

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

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


101
2018-01-12 11:07



Ein innerer Join zeigt nur Zeilen, wenn auf der anderen (rechten) Seite des Joins ein übereinstimmender Datensatz vorhanden ist.

Ein (linker) äußerer Join zeigt Zeilen für jeden Datensatz auf der linken Seite, auch wenn auf der anderen (rechten) Seite des Joins keine übereinstimmenden Zeilen vorhanden sind. Wenn es keine übereinstimmende Zeile gibt, würden die Spalten für die andere (rechte) Seite NULL anzeigen.


92
2017-09-01 22:38