Vraag Wat is het verschil tussen "INNER JOIN" en "OUTER JOIN"?


Ook hoe LEFT JOIN, RIGHT JOIN en FULL JOIN passen in?


4014
2017-09-01 22:36


oorsprong


antwoorden:


Ervan uitgaande dat u deelneemt aan kolommen zonder duplicaten, wat een veel voorkomend geval is:

  • Een binnenverbinding van A en B geeft het resultaat van A kruising B, d.w.z. het binnenste deel van een Venn diagram kruispunt.

  • Een buitenverbinding van A en B geeft de resultaten van A-eenheid B, d.w.z. de buitenste delen van een Venn-diagramverbinding.

Voorbeelden

Stel dat u twee tabellen hebt met elk één kolom en de gegevens als volgt:

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

Merk op dat (1,2) uniek zijn voor A, (3,4) komen vaak voor, en (5,6) zijn uniek voor B.

Innerlijke join

Een inner join met een van de equivalente query's geeft de kruising van de twee tabellen, d.w.z. de twee rijen die ze gemeenschappelijk hebben.

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

Linkse outer join

Een linker outer join geeft alle rijen in A, plus eventuele gemeenschappelijke rijen 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

Rechter outer join

Een rechter outer join geeft alle rijen in B, plus eventuele gewone rijen 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

Volledige outer join

Een volledige outer join geeft u de unie van A en B, dus alle rijen in A en alle rijen in B. Als iets in A geen overeenkomend gegeven in B heeft, is het B-gedeelte null en vice versa 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

5419
2017-09-01 22:59



Ook kunt u het volgende schema voor verschillende jointypen beschouwen;

visual explanation of joins

Bron: Visual-vertegenwoordiging-of-SQL-joins in detail uitgelegd door CL Moffatt


2441
2018-05-16 23:03



ik raad aan Jeff's blogartikel. De beste beschrijving die ik ooit heb gezien, plus er is een visualisatie, bijvoorbeeld:

Inner Join:

enter image description here

Volledige Outer Join:

enter image description here


591
2017-08-30 11:52



De Venn-diagrammen doen het niet echt voor mij.

Ze laten bijvoorbeeld geen onderscheid zien tussen een cross join en een inner join, of meer in het algemeen laten ze een onderscheid zien tussen verschillende typen joinpredicaten of bieden ze een kader voor redenering over hoe ze zullen werken.

Er is geen vervanging voor het begrijpen van de logische verwerking en het is relatief eenvoudig om toch te begrijpen.

  1. Stel je een kruising voor.
  2. Evalueer de on clausule tegen alle rijen uit stap 1, waarbij de regels behouden blijven waar het predicaat evalueert true
  3. (Alleen voor outer joins) voeg terug in alle buitenrijen die u in stap 2 bent kwijtgeraakt.

(NB: in de praktijk kan de query-optimizer efficiëntere manieren vinden om de query uit te voeren dan de zuiver logische beschrijving hierboven, maar het eindresultaat moet hetzelfde zijn)

Ik begin met een geanimeerde versie van a volledige outer join. Verdere uitleg volgt.

enter image description here


Uitleg

Source Tables

enter link description here

Eerst beginnen met een CROSS JOIN (AKA Cartesian Product). Dit heeft geen ON clausule en geeft eenvoudig elke permutatie van rijen uit de twee tabellen.

SELECTEER A.Kleur, B.Kleur VAN EEN KRUIS JOIN B

enter link description here

Binnenste en buitenste joins hebben een predicaat "AAN" -clausule.

  • Inner Join. Evalueer de voorwaarde in de "AAN" -clausule voor alle rijen in het kruiselings join-resultaat. Als het waar is, gaat u terug naar de gekoppelde rij. Anders gooit u deze weg.
  • Left Outer Join. Hetzelfde als inner join en voor alle rijen in de linkertabel die niet overeenkwamen, worden deze uitgevoerd met NULL-waarden voor de rechtertabelkolommen.
  • Rechts Outer Join. Hetzelfde als inner join en voor elke rij in de rechtse tabel die niet overeenkwam, wordt deze uitgevoerd met NULL-waarden voor de linkse tabelkolommen.
  • Volledige Outer Join. Gelijk aan de inner join en bewaar de linker niet-overeenkomende rijen zoals in de linker outer join en de rechter niet-overeenkomende rijen vanaf de rechter outer join.

Een paar voorbeelden

SELECTEER A.Kleur, B.Kleur VANAF EEN BINNENVERBINDING B A A.Kleur = B.Kleur

Het bovenstaande is de klassieke equi-join.

Inner Join

Geanimeerde versie

enter image description here

SELECTEER A.Kleur, B.Kleur VAN EEN BINNENVERBINDING B A A.Kleur NIET IN ('Groen', 'Blauw')

De inner join-conditie hoeft niet noodzakelijkerwijs een voorwaarde voor gelijkheid te zijn en het hoeft niet te verwijzen naar kolommen van beide (of zelfs geen van beide) tabellen. Evalueren A.Colour NOT IN ('Green','Blue') op elke rij van de kruis join komt terug.

inner 2

SELECTEER A.Kleur, B.Kleur VANAF EEN BINNENVERBINDING BINNEN 1 = 1

De join-voorwaarde evalueert naar true voor alle rijen in het kruis join-resultaat, dus dit is net hetzelfde als een cross-join. Ik zal de afbeelding van de 16 rijen niet opnieuw herhalen.

SELECTEER A.Kleur, B.Kleur VAN EEN LINKSE BUITENSTE BINNENKOPEN A.Kleur = B.Kleur

Outer Joins worden logisch geëvalueerd op dezelfde manier als inner joins, behalve dat als een rij uit de linkertabel (voor een linkse join) niet wordt gekoppeld met rijen uit de rechtse tabel, deze wordt behouden in het resultaat met NULL waarden voor de kolommen aan de rechterkant.

LOJ

SELECTEER A.Kleur, B.Kleur VAN LINKERDE BUITENKANT B A A.Kleur = B.Kleur WAAR B.Kleur IS NULL

Dit beperkt eenvoudig het vorige resultaat om alleen de rijen waar te retourneren B.Colour IS NULL. In dit specifieke geval zijn dit de rijen die zijn behouden omdat ze geen overeenkomst hadden in de rechtse tabel en de query retourneert de enkele rode rij die niet overeenkomt in tabel B. Dit staat bekend als een anti-semi-join.

Het is belangrijk om een ​​kolom te selecteren voor de IS NULL test die ofwel niet nul is of waarvoor de join-voorwaarde zorgt dat die NULL waarden worden uitgesloten om dit patroon correct te laten werken en te voorkomen dat alleen rijen worden opgehaald die toevallig een NULL waarde voor die kolom naast de niet-overeenkomende rijen.

loj is null

SELECTEER A.Kleur, B.Kleur VANAF EEN JUISTE BUITENSTE B AANGAANDE A.Kleur = B.Kleur

Rechter outer joins werken op dezelfde manier als left outer joins, behalve dat ze niet-overeenkomende rijen van de rechtse tabel behouden en de linker kolomkolommen niet vergroten.

ROJ

SELECTEER A.Kleur, B.Kleur UIT EEN VOLLEDIGE BUITENSTE BINNENVERBINDING A.Kleur = B.Kleur

Volledige outer joins combineren het gedrag van links en rechts joins en bewaren de niet-overeenkomende rijen uit zowel de linker- als de rechtertabel.

FOJ

SELECTEER A.Kleur, B.Kleur UIT EEN VOLLEDIGE BUITENSTE BINNENKANT B AAN 1 = 0

Geen rijen in de kruising komen overeen met de 1=0 predikaat. Alle rijen van beide kanten worden bewaard met behulp van normale outer join-regels met NULL in de kolommen van de tabel aan de andere kant.

FOJ 2

SELECT COALESCE (A.Colour, B.Colour) ALS Kleur UIT EEN VOLLEDIGE BUITENSTE BINNEN 1 = 0

Met een kleine wijziging in de voorgaande query kon men een a simuleren UNION ALL van de twee tabellen.

UNION ALL

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B A A.Kleur = B.Kleur WHERE B.Colour = 'Groen'

Merk op dat de WHERE clausule (indien aanwezig) wordt logisch uitgevoerd na de join. Een veelvoorkomende fout is het uitvoeren van een linkse outer join en vervolgens een WHERE-component met een voorwaarde in de rechtse tabel die eindigt met uitsluiting van de rijen zonder aanpassing. Het bovenstaande eindigt met het uitvoeren van de outer join ...

LOJ

... En dan loopt de "Where" -clausule. NULL= 'Green' wordt niet geëvalueerd zodat de rij die door de outer join wordt bewaard, uiteindelijk wordt weggegooid (samen met de blauwe), waardoor de join effectief wordt omgezet in een innerlijke join.

LOJtoInner 

Als het de bedoeling was om alleen rijen van B op te nemen waar Kleur groen is en alle rijen vanaf A, ongeacht de juiste syntaxis

SELECTEER A.Kleur, B.Kleur VANUIT EEN LINKSE BUITENSTE BINNENVERBINDING A.Kleur = B.Kleur EN B.Kleur = 'Groen'

enter image description here

SQL Fiddle

Zie deze voorbeelden live op SQLFiddle.com.


526
2017-12-13 11:58



Het volgende is afkomstig uit het artikel "MySQL - LEFT JOIN en RIGHT JOIN, INNER JOIN en OUTER JOIN"door Graham Ellis op zijn blog Horse's Mouth.

In een database zoals MySQL worden de gegevens verdeeld in een aantal tabellen die vervolgens worden verbonden (Joined) samen door JOIN in SELECT opdrachten om records uit meerdere tabellen te lezen. Lees dit voorbeeld om te zien hoe het werkt.

Eerst een aantal voorbeeldgegevens:

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)

REGELMATIGE JOIN

Als we een regelmatige JOIN doen (met geen van de trefwoorden INNER, OUTER, LEFT of RIGHT), dan krijgen we alle records die op de juiste manier overeenkomen in de twee tabellen en records in beide inkomende tabellen die niet overeenkomen, worden niet gerapporteerd :

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)

LINKS DOE MEE

Als we een LINKEROPDRACHT doen, krijgen we alle records die op dezelfde manier overeenkomen en NEGEEN krijgen we een extra record voor elk ongeëvenaard record in de linkerstabel van de join - waardoor (in dit voorbeeld) wordt verzekerd dat elke PERSOON een vermelding krijgt :

   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)

RECHTS JOIN

Als we een RECHTE JOIN DOEN, krijgen we alle records die overeenkomen en bovendien een extra record voor elk ongeëvenaard record in de rechtse tabel van de join - in mijn voorbeeld betekent dit dat elke eigenschap een vermelding krijgt, zelfs als we dat niet doen hebben verkoper details:

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)

Een INNER JOIN maakt een volledige join, net als het eerste voorbeeld, en het woord OUTER kan na het woord LEFT of RIGHT in de laatste twee voorbeelden worden toegevoegd - het is bedoeld voor ODBC-compatibiliteit en voegt geen extra mogelijkheden toe.


291
2018-02-14 05:53



Inner Join

Haal alleen de overeenkomende rijen op, dat wil zeggen A intersect B.

Enter image description here

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

Left Outer Join

Selecteer alle records uit de eerste tabel en alle records in de tweede tabel die overeenkomt met de samengevoegde toetsen.

Enter image description here

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

Volledige Outer Join

Selecteer alle records uit de tweede tabel en alle records in de eerste tabel die overeenkomt met de samengevoegde toetsen.

Enter image description here

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

Referenties


114
2018-01-27 12:16



Doet mee worden gebruikt om de gegevens uit twee tabellen te combineren, met als resultaat een nieuwe, tijdelijke tabel. Joins worden uitgevoerd op basis van iets dat een predicaat wordt genoemd en dat de voorwaarde specificeert die moet worden gebruikt om een ​​join uit te voeren. Het verschil tussen een inner join en een outer join is dat een inner join alleen de rijen retourneert die daadwerkelijk overeenkomen op basis van het join-predikaat. Laten we de tabel Werknemer en locatie overwegen:

enter image description here

Inner Join: - Inner join maakt een nieuwe resultatentabel door kolomwaarden van twee tabellen te combineren (Werknemer en Plaats) op basis van het join-predikaat. De query vergelijkt elke rij met Werknemer bij elke rij van Plaats om alle paren rijen te vinden die voldoen aan het join-predikaat. Als aan het joinpredikaat is voldaan door niet-NULL-waarden te matchen, worden kolomwaarden voor elk gekoppeld paar rijen van Werknemer en Plaats worden gecombineerd in een resultaatrij. Hier is wat de SQL voor een innerlijke join eruit zal zien:

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

Nu, hier is wat het resultaat van het uitvoeren van die SQL eruit zou zien: enter image description here enter image description here

Outer Join: - Een outer join vereist niet dat elke record in de twee gekoppelde tabellen een overeenkomende record heeft. De gekoppelde tabel behoudt elke record, zelfs als er geen ander overeenkomend record bestaat. Externe joins worden verder onderverdeeld in linker outer joins en rechter outer joins, afhankelijk van de rijen van de tabel (links of rechts).

Left Outer Join: - Het resultaat van een linkse outer join (of gewoon links join) voor tabellen Werknemer en Plaats bevat altijd alle records van de "linker" tabel (Werknemer), zelfs als de join-voorwaarde geen overeenkomstige record vindt in de "juiste" tabel (Plaats). Hier is wat de SQL voor een linker outer join eruit zou zien, met behulp van de bovenstaande tabellen:

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

Nu, hier is wat het resultaat van het uitvoeren van deze SQL eruit zou zien: enter image description here enter image description here

Right Outer Join: - Een rechter outer join (of rechts join) lijkt veel op een linker outer join, behalve wanneer de behandeling van de tabellen is omgekeerd. Elke rij van de "juiste" tafel (Plaats) verschijnt ten minste één keer in de samengevoegde tabel. Als er geen overeenkomende rij uit de "linker" tabel is (Werknemer) bestaat, NULL verschijnt in kolommen van Werknemer voor die records die geen match hebben Plaats. Dit is hoe de SQL eruit ziet:

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

Met behulp van de bovenstaande tabellen kunnen we laten zien hoe de resultatenset van een rechter outer join eruit zou zien:

enter image description hereenter image description here

Volledige Outer Joins: - Volledige externe join of volledige join is om de niet-overeenkomende informatie te behouden door niet-overeenkomende rijen op te nemen in de resultaten van een join, een volledige outer join te gebruiken. Het bevat alle rijen uit beide tabellen, ongeacht of de andere tabel al dan niet een overeenkomende waarde heeft. enter image description here

Afbeeldingsbron

MySQL 8.0 Naslaggids - Join syntaxis


108
2017-12-18 06:54



In eenvoudige woorden:

Een innerlijke join haal alleen de overeenkomende rijen op.

Overwegende dat een outer join haal de overeenkomende rijen op uit de ene tabel en alle rijen in de andere tabel ... het resultaat hangt af van welke u gebruikt:

  • Links: Overeenkomende rijen in de rechtse tabel en alle rijen in de linkerstabel

  • Rechts: Overeenkomende rijen in de linker tabel en alle rijen in de rechtse tabel of

  • vol: Alle rijen in alle tabellen. Het maakt niet uit of er een match is of niet


101
2018-01-12 11:07