Domanda Qual è la differenza tra "INNER JOIN" e "OUTER JOIN"?


Anche come LEFT JOIN, RIGHT JOIN e FULL JOIN adattarsi?


4012
2017-09-01 22:36


origine


risposte:


Supponendo che ti unisci a colonne senza duplicati, che è un caso molto comune:

  • Un'unione interna di A e B dà il risultato di Un incrocio B, cioè la parte interna di a diagramma di Venn  intersezione.

  • Un'unione esterna di A e B dà i risultati di un'unione B, cioè le parti esterne di un'unione di diagrammi di Venn.

Esempi

Supponiamo di avere due tabelle, ognuna con una singola colonna e dati come segue:

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

Nota che (1,2) sono unici per A, (3,4) sono comuni e (5,6) sono unici per B.

Join interno

Un join interno che utilizza una delle query equivalenti fornisce l'intersezione delle due tabelle, vale a dire le due righe che hanno in comune.

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

Giuntura esterna sinistra

Un join esterno sinistro fornirà tutte le righe in A, più eventuali righe comuni 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

Giuntura esterna destra

Un join esterno destro darà tutte le righe in B, più tutte le righe comuni 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

Full outer join

Un join esterno completo ti darà l'unione di A e B, cioè tutte le righe in A e tutte le righe in B. Se qualcosa in A non ha un dato corrispondente in B, allora la parte B è nullo, e viceversa 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

5417
2017-09-01 22:59



Inoltre è possibile considerare lo schema seguente per diversi tipi di join;

visual explanation of joins

Fonte: Visual-rappresentazione-of-SQL-join  spiegato in dettaglio da C.I. Moffatt


2439
2018-05-16 23:03



io raccomando Articolo del blog di Jeff . La migliore descrizione che abbia mai visto, oltre a una visualizzazione, ad esempio:

Join interno:

enter image description here

Full Outer Join:

enter image description here


591
2017-08-30 11:52



I diagrammi di Venn in realtà non lo fanno per me.

Ad esempio, non mostrano alcuna distinzione tra un cross join e un inner join, o più generalmente mostrano alcuna distinzione tra diversi tipi di predicato di join o forniscono un framework per il ragionamento su come opereranno.

Non c'è alcun sostituto per la comprensione dell'elaborazione logica ed è relativamente semplice da afferrare comunque.

  1. Immagina un cross join.
  2. Valutare il on clausola contro tutte le righe del passaggio 1, mantenendo quelle a cui il predicato valuta true
  3. (Solo per i join esterni), aggiungi nuovamente le righe esterne perse nel passaggio 2.

(NB: In pratica, Query Optimiser può trovare modi più efficienti di eseguire la query rispetto alla descrizione puramente logica sopra, ma il risultato finale deve essere lo stesso)

Inizierò con una versione animata di a full outer join . Segue un'ulteriore spiegazione.

enter image description here


Spiegazione

Tabelle di origine

enter link description here

Primo avvio con a CROSS JOIN (Prodotto cartesiano AKA). Questo non ha un ON clausola e restituisce semplicemente ogni permutazione delle righe dalle due tabelle.

SELEZIONA A.Colore, B.COLORE DA UN CROSS JOIN B

enter link description here

I join interni ed esterni hanno un predicato "ON".

  • Join interno.  Valutare la condizione nella clausola "ON" per tutte le righe nel risultato del cross join. Se true restituisci la riga unita. Altrimenti scartalo.
  • Left Outer Join.  Lo stesso di inner join quindi per tutte le righe nella tabella sinistra che non corrispondono a nessuna uscita con valori NULL per le colonne della tabella di destra.
  • Giusto outer join.  Lo stesso di inner join quindi per tutte le righe nella tabella di destra che non corrispondono a nessuna uscita con valori NULL per le colonne della tabella sinistra.
  • Full Outer Join.  Come l'inner join, quindi preservare le righe non corrispondenti a sinistra come nel join esterno sinistro e le righe non corrispondenti a destra come per il join esterno destro.

Qualche esempio

SELEZIONA A. Colore, B. Colore DA UN INTERNO GIUNTO B SU A. Colore = B. Colore

Quanto sopra è il classico equi join.

Inner Join

Versione animata

enter image description here

SELEZIONA A.Colore, B.COLORE DA UN INNER JOIN B ON A.Colour NOT IN ('Green', 'Blue')

La condizione di unione interna non deve necessariamente essere una condizione di uguaglianza e non è necessario che faccia riferimento alle colonne di entrambe (o anche entrambe) delle tabelle. Valutare A.Colour NOT IN ('Green','Blue') su ogni riga della croce ritorna il join.

inner 2

SELEZIONA A.Colore, B.COLORE DA UN INNER JOIN B ON 1 = 1

La condizione di join viene valutata su true per tutte le righe nel risultato del cross join, quindi è uguale a un cross join. Non ripeterò più l'immagine delle 16 righe.

SELEZIONA A.Colore, B.COLORE DA UN SINISTRA ESTERNO UNISCITI A B ON A.Colore = B.Colore

I join esterni vengono valutati in modo logico allo stesso modo dei join interni, tranne che se una riga della tabella di sinistra (per un join di sinistra) non si unisce a nessuna riga della tabella di destra, viene conservata nel risultato con NULL valori per le colonne di destra.

LOJ

SELEZIONA A.Colore, B.COLORE DA A SINISTRA ESTERNA UNISCITI A B ON A.Colore = B.Colore WHERE B.Colour IS NULL

Ciò limita semplicemente il risultato precedente per restituire solo le righe dove B.Colour IS NULL. In questo caso particolare, queste saranno le righe che sono state conservate poiché non avevano corrispondenza nella tabella di destra e la query restituisce la singola riga rossa non corrispondente nella tabella B. Questo è noto come un anti semi join.

È importante selezionare una colonna per IS NULL test che non è annullabile o per il quale la condizione di join lo garantisce NULL i valori saranno esclusi perché questo modello funzioni correttamente ed eviti di riportare solo le righe che hanno un NULL valore per quella colonna in aggiunta alle righe non abbinate.

loj is null

SELEZIONA A.Colore, B.COLORE DA A DESTRA ESTERNO ENTRATA B SU A.Colore = B.Colore

I join esterni a destra agiscono in modo simile ai join esterni a sinistra, tranne per il fatto che conservano le righe non corrispondenti dalla tabella di destra e si estendono le colonne a sinistra.

ROJ

SELEZIONA A.Colore, B.COLORE DA UN INTERO COMPLETO UNISCITI A B ON A.Colore = B.Colore

I join esterni completi combinano il comportamento dei join sinistro e destro e preservano le righe non corrispondenti da entrambe le tabelle sinistra e destra.

FOJ

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

Nessuna riga nella join incrociata corrisponde a 1=0 predicato. Tutte le righe da entrambi i lati vengono mantenute utilizzando le normali regole di join esterno con NULL nelle colonne dalla tabella sull'altro lato.

FOJ 2

SELEZIONA COALESCE (A.Colour, B.Colour) AS Colore DA UN INTERO COMPLETO UNISCITI A B ON 1 = 0

Con una modifica minore alla query precedente si potrebbe simulare a UNION ALL dei due tavoli.

UNION ALL

SELEZIONA A.Colore, B.COLORE DA A SINISTRA ESTERNA UNISCITI A B ON A.Colore = B.Colore WHERE B.Colour = 'Verde'

Si noti che il WHERE la clausola (se presente) viene eseguita logicamente dopo il join. Un errore comune è eseguire un join esterno sinistro e quindi includere una clausola WHERE con una condizione sulla tabella destra che finisce per escludere le righe non corrispondenti. Quanto sopra finisce per eseguire il join esterno ...

LOJ

... E poi viene eseguita la clausola "Dove". NULL= 'Green' non valuta il valore vero, quindi la riga preservata dall'unione esterna finisce scartata (insieme a quella blu) convertendo efficacemente il join in uno interno.

LOJtoInner 

Se l'intenzione fosse quella di includere solo le righe da B dove Colore è verde e tutte le righe da A indipendentemente dalla sintassi corretta

SELEZIONA A.Colore, B.COLORE DA UN SINISTRA ESTERNO UNISCITI A B ON A.Colore = B.Colore E B.Colore = 'Verde'

enter image description here

SQL Fiddle

Vedi questi esempi eseguire live su SQLFiddle.com .


524
2017-12-13 11:58



Quanto segue è stato tratto dall'articolo " MySQL - LEFT JOIN e RIGHT JOIN, INNER JOIN e OUTER JOIN "di Graham Ellis sul suo blog Horse's Mouth.

In un database come MySQL, i dati sono divisi in un numero di tabelle che vengono poi collegate ( Joined) insieme da JOIN in SELECT comandi per leggere i record da più tabelle. Leggi questo esempio per vedere come funziona.

Innanzitutto, alcuni dati di esempio:

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)

REGOLARE ISCRIVITI

Se eseguiamo un JOIN regolare (con nessuna delle parole chiave INNER, OUTER, LEFT o RIGHT), otteniamo tutti i record che corrispondono in modo appropriato nelle due tabelle e i record in entrambe le tabelle in entrata che non corrispondono non vengono segnalati :

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)

SINISTRA

Se facciamo un SINISTRA SINISTRO, otteniamo tutti i record che corrispondono allo stesso modo e IN AGGIUNTA otteniamo un record extra per ogni record non abbinato nella tabella sinistra del join - assicurando così (in questo esempio) che ogni PERSON riceve una menzione :

   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)

GIUSTO PARTITO

Se facciamo un GIUSTO PARTITO, otteniamo tutti i record che corrispondono e IN AGGIUNTA un record extra per ogni record ineguagliato nella tabella di destra del join - nel mio esempio, questo significa che ogni proprietà riceve una menzione anche se non lo facciamo avere i dettagli del venditore:

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)

Un INNER JOIN esegue un join completo, proprio come il primo esempio, e la parola OUTER può essere aggiunta dopo la parola LEFT o RIGHT negli ultimi due esempi: è prevista per la compatibilità ODBC e non aggiunge funzionalità extra.


291
2018-02-14 05:53



Join interno

Recupera solo le righe corrispondenti, cioè 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

Seleziona tutti i record dalla prima tabella e tutti i record nella seconda tabella che corrisponde alle chiavi unite.

Enter image description here

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

Full Outer Join

Seleziona tutti i record dalla seconda tabella e tutti i record nella prima tabella che corrisponde alle chiavi unite.

Enter image description here

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

Riferimenti


114
2018-01-27 12:16



Si unisce  sono usati per combinare i dati di due tabelle, con il risultato di essere una nuova tabella temporanea. I join vengono eseguiti in base a qualcosa chiamato predicato, che specifica la condizione da utilizzare per eseguire un join. La differenza tra un join interno e un join esterno è che un join interno restituirà solo le righe effettivamente corrispondenti in base al predicato di join. Consideriamo la tabella Employee e Location:

enter image description here

Join interno: - Inner join crea una nuova tabella dei risultati combinando i valori delle colonne di due tabelle ( Dipendente  e Posizione ) basato sul predicato di join. La query confronta ogni riga di Dipendente  con ogni riga di Posizione  per trovare tutte le coppie di righe che soddisfano il predicato di join. Quando il predicato di join è soddisfatto dall'accoppiamento di valori non NULL, valori di colonna per ogni coppia di righe con corrispondenza Dipendente  e Posizione  sono combinati in una fila di risultati. Ecco come apparirà l'SQL per un join interno:

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

Ora, ecco come apparirà il risultato dell'esecuzione di tale SQL: enter image description here enter image description here

Outer Join: - Un join esterno non richiede che ogni record nelle due tabelle unite abbia un record corrispondente. La tabella unita conserva ogni record, anche se non esiste nessun altro record corrispondente. I join esterni si suddividono ulteriormente in join esterni a sinistra e giunti esterni a destra, a seconda delle righe di tabella che vengono mantenute (a sinistra oa destra).

Join esterno sinistro: - Il risultato di un join esterno sinistro (o semplicemente left join) per le tabelle Dipendente  e Posizione  contiene sempre tutti i record della tabella "sinistra" ( Dipendente ), anche se la condizione di join non trova alcun record corrispondente nella tabella "right" ( Posizione ). Ecco come apparirebbe l'SQL per un join esterno sinistro, usando le tabelle sopra:

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

Ora, ecco come apparirà il risultato dell'esecuzione di questo SQL: enter image description here enter image description here

Right Outer Join: - Un join esterno destro (o join destro) assomiglia molto a un join esterno sinistro, tranne che per il trattamento dei tavoli invertiti. Ogni riga dalla tabella "giusta" ( Posizione ) apparirà nella tabella unita almeno una volta. Se nessuna riga corrispondente dalla tabella "sinistra" ( Dipendente ) esiste, NULL apparirà in colonne da Dipendente  per quei dischi che non hanno corrispondenza in Posizione . Questo è ciò che sembra l'SQL:

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

Usando le tabelle sopra, possiamo mostrare come sarebbe il set di risultati di un join esterno destro:

enter image description hereenter image description here

Full Outer Joins: - Full Outer Join o Full Join è per conservare le informazioni non corrispondenti includendo righe non corrispondenti nei risultati di un join, utilizzare un join esterno completo. Include tutte le righe di entrambe le tabelle, indipendentemente dal fatto che l'altra tabella abbia o meno un valore corrispondente. enter image description here

Fonte dell'immagine

MySQL 8.0 Manuale di riferimento - Partecipa alla sintassi


108
2017-12-18 06:54



In parole semplici:

Un join interno  recupera solo le righe corrispondenti.

Mentre un join esterno  recuperare le righe corrispondenti da una tabella e tutte le righe in un'altra tabella .... il risultato dipende da quale si sta utilizzando:

  • Sinistra : Righe corrispondenti nella tabella di destra e tutte le righe nella tabella sinistra

  • Destra : Righe corrispondenti nella tabella di sinistra e tutte le righe nella tabella di destra o

  • Pieno : Tutte le righe in tutte le tabelle. Non importa se c'è una partita o no


101
2018-01-12 11:07



Un join interno mostra solo le righe se c'è un record corrispondente sull'altro lato (a destra) del join.

Un join esterno (a sinistra) mostra le righe per ogni record sul lato sinistro, anche se non vi sono righe corrispondenti sull'altro lato (a destra) del join. Se non c'è una riga corrispondente, le colonne per l'altro lato (a destra) mostrerebbero NULL.


92
2017-09-01 22:38



I join interni richiedono che un record con un ID correlato esista nella tabella unita.

I join esterni restituiranno i record per il lato sinistro anche se non esiste nulla per il lato destro.

Ad esempio, hai una tabella Orders e OrderDetails. Sono collegati da un "OrderID".

Ordini

  • ID ordine
  • Nome del cliente

OrderDetails

  • OrderDetailID
  • ID ordine
  • Nome del prodotto
  • Quantità
  • Prezzo

La richiesta

SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

restituirà solo Ordini che hanno anche qualcosa nella tabella OrderDetails.

Se lo si cambia in OUTER LEFT JOIN

SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

quindi restituirà i record dalla tabella Ordini anche se non hanno record OrderDetails.

Puoi usare questo per trovare Ordini che non hanno OrderDetails che indicano un possibile ordine orfano aggiungendo una clausola where come WHERE OrderDetails.OrderID IS NULL.


67
2017-09-01 22:47



In parole semplici:

Join interno  -> Prendi SOLO i record comuni dalle tabelle padre e figlio DOVE la chiave primaria della tabella padre corrisponde a Chiave esterna nella tabella Bambino.

Unisciti a sinistra  ->

codice pseudo

1.Take All records from left Table
2.for(each record in right table,) {
    if(Records from left & right table matching on primary & foreign key){
       use their values as it is as result of join at the right side for 2nd table.
    } else {
       put value NULL values in that particular record as result of join at the right side for 2nd table.
    }
  }

Iscriviti a destra  : Esattamente opposto di unire a sinistra. Inserisci il nome del tavolo in SINISTRA SINISTRA sul lato destro in Giuntura a destra, ottieni lo stesso risultato di SINISTRA.

Join esterno  : Mostra tutti i record in entrambe le tabelle No matter what. Se i record nella tabella di sinistra non corrispondono alla tabella di destra basata sulla chiave primaria, Forieign, utilizzare il valore NULL come risultato di join.

Esempio :

Example

Supponiamo ora per 2 tavoli

1.employees , 2.phone_numbers_employees

employees : id , name 

phone_numbers_employees : id , phone_num , emp_id   

Qui, la tabella dei dipendenti è la tabella principale, phone_numbers_employees è la tabella figlio (che contiene emp_id come chiave esterna che si connette employee.id quindi la sua tabella figlio.)

Inner join  

Prendi i record di 2 tavoli SOLO IF Tabella chiave primaria dei dipendenti (il suo id) corrisponde a Chiave esterna della tabella figlio phone_numbers_employees (emp_id) .

Quindi la query sarebbe:

SELECT e.id , e.name , p.phone_num FROM employees AS e INNER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Qui prendi solo le righe corrispondenti sulla chiave primaria = chiave esterna come spiegato sopra. Qui le righe non corrispondenti sulla chiave primaria = la chiave esterna vengono saltate come risultato del join.

La sinistra si unisce  :

L'unione sinistra conserva tutte le righe della tabella sinistra, indipendentemente dal fatto che ci sia una riga che corrisponde alla tabella corretta.

SELECT e.id , e.name , p.phone_num FROM employees AS e LEFT JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Outer join  :

SELECT e.id , e.name , p.phone_num FROM employees AS e OUTER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Diagramaticamente sembra:

Diagram


58
2018-02-19 04:50