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

domanda creata a Wed, Feb 13, 2019 12:00 AM

Inoltre, come si adattano LEFT JOIN, RIGHT JOIN e FULL JOIN?

    
4376
  1. delle risposte & commenti & i loro riferimenti sotto solo uno in realtà spiega come i diagrammi di Venn rappresentano gli operatori: l'area di intersezione del cerchio rappresenta l'insieme di righe in A JOIN B. L'area unica per ogni cerchio rappresenta l'insieme di righe che ottieni prendendo il suo le righe della tabella che non partecipano a A JOIN B e aggiungono le colonne univoche all'altra tabella sono tutte impostate su NULL. (E la maggior parte dà una vaga corrispondenza dei cerchi con A e B).
    2014-10-18 20: 24: 59Z
  2. Molte risposte sono già fornite ma non ho visto questo tutorial menzionato. Se conosci i diagrammi di Venn, questo è un ottimo tutorial: blog.codinghorror .com /a-visual-explanation-of-sql-joins Per me è abbastanza conciso per essere una lettura veloce ma afferra ancora l'intero concetto e funziona molto bene. Se non sai quali sono i diagrammi di Venn - impara loro. Prende 5-10 minuti per farlo e ti aiuterà ogni volta che avrai bisogno di visualizzare i lavori con i set e di gestire le operazioni sui set.
    2017-05-09 09: 04: 59Z
  3. @ DanteTheSmith No, che soffre degli stessi problemi dei diagrammi qui. Vedi il mio commento sopra la domanda & Di seguito è riportato il post di questo blog: "Jeff ripudia il suo blog di alcune pagine nei commenti". I diagrammi di Venn mostrano elementi in serie. Basta provare a identificare esattamente quali sono gli insiemi e quali sono gli elementi in questi diagrammi. Gli insiemi non sono le tabelle e gli elementi non sono le loro righe. Inoltre, è possibile unire due tavoli qualsiasi, quindi PK e amp; Le FK sono irrelvant. Tutti fasulli. Stai facendo proprio quello che migliaia di altri hanno fatto - ha una impressione vaga che (erroneamente) presumo abbia senso.
    2017-05-18 05: 57: 51Z
  4. Qualcuno potrebbe rispondere a questo in riferimento ai dati indicizzati di serie temporali - I join interni e esterni spesso non funzionano in un senso umano /di Venn quando si tratta di timestamp che sono diverso da pochi millisecondi.
    2017-09-05 09: 26: 18Z
  5. il titolo originale era più chiaro di quello che era stato chiesto a beng ("diversi tipi di join" potrebbero riferirsi a tipi di join fisici come hash e unire ad esempio) Il titolo originale era anche chiaramente efficace con i motori di ricerca dal numero di visualizzazioni. Ho eseguito il rollback della modifica non necessaria
    2019-02-13 08: 14: 08Z
24 risposte                              24                         

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

  • Un join interno di A e B dà il risultato di Un incrocio B, cioè la parte interna di un Diagramma di Venn intersezione.

  • Un join esterno di A e B fornisce i risultati di A union B, cioè le parti esterne di un unione di diagrammi di Venn.

Esempi

Supponiamo di avere due tabelle, ciascuna con una singola colonna e i 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, ovvero 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

Join esterno sinistro

Un join esterno sinistro darà 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

Right outer join

Un'unione esterna destra darà all righe in B, più eventuali 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

Join esterno completo

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, la parte B è nullo e viceversa.

 
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
    
5823
2017-09-28 19: 52: 04Z
  1. Sarebbe bene aumentare l'esempio aggiungendo un'altra riga nella tabella B con il valore 4. Questo mostrerà che i join interni non devono essere sullo stesso numero di righe.
    2009-08-30 12: 59: 48Z
  2. Una spiegazione eccellente, tuttavia questa affermazione: Un join esterno di A e B dà i risultati di A union B, cioè le parti esterne di un unione di diagrammi di venn . non è formulato in modo accurato. Un join esterno darà i risultati di Un incrocio B in aggiunta a uno dei seguenti: tutti A (join sinistro), tutti B (join destro) o tutti A e tutti B (join completo). Solo quest'ultimo scenario è in realtà un'unione B. Ancora, una spiegazione ben scritta.
    2011-05-03 19: 57: 42Z
  3. Ho ragione che FULL JOIN è un alias di FULL OUTER JOIN e LEFT JOIN è un alias di LEFT OUTER JOIN?
    2013-01-01 20: 29: 27Z
  4. sì ottima ed eccellente spiegazione. ma perché nella colonna b i valori non sono in ordine? vale a dire 6,5 non come 5,6?
    2013-03-04 08: 39: 36Z
  5. @ Ameer, grazie. Join non garantisce un ordine, è necessario aggiungere una clausola ORDER BY.
    2013-03-05 00: 28: 16Z

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

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

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

  1. Immagina un cross join.
  2. Valutare la clausola on su tutte le righe del passaggio 1, mantenendo quelle in cui il predicato restituisce true
  3. (Solo per i join esterni) ritorna in tutte le righe esterne perse nel passaggio 2.

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

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

 inserisci la descrizione dell'immagine qui


Spiegazione

Tabelle sorgenti

inserisci la descrizione del link qui

Inizia con un CROSS JOIN (prodotto cartesiano AKA). Questo non ha una clausola ON e restituisce semplicemente ogni combinazione di righe dalle due tabelle.

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

inserisci la descrizione del link qui

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

  • Join interno. valuta 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. Come per inner join, quindi per tutte le righe nella tabella sinistra che non corrispondono a nessuna uscita con valori NULL per le colonne della tabella corrette.
  • Right 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.
  • Join esterno completo. Come l'inner join, quindi conserva le righe non corrispondenti a sinistra come nel join esterno sinistro e le righe a destra non corrispondenti come per il join esterno destro.

Alcuni examples

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

Quanto sopra è il classico equi join.

Join interno

Versione animata ​​h2>

 inserisci la descrizione dell'immagine qui

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 da entrambe (o anche da entrambe) delle tabelle. La valutazione di A.Colour NOT IN ('Green','Blue') su ogni riga dei ritorni incrociati.

inner 2

SELEZIONA A.Colour, B.Colour FROM A 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 A SINISTRA ESTERNA 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 È NULL

Questo limita semplicemente il risultato precedente per restituire solo le righe in cui 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 il test IS NULL che non è annullabile o per la quale la condizione di join garantisce che tutti i NULL valori saranno esclusi affinché questo modello funzioni correttamente ed eviti di riportare solo le righe che capita di avere un valore NULL per quella colonna oltre alle righe non abbinate.

loj è null

SELEZIONA A.Colore, B.COLORE DA A DESTRA ESTERNO UNISCITI A B ON 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 null estendono le colonne a sinistra.

ROJ

SELEZIONA A.Colore, B.Colore da A FULL ESTERNO 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 nel join incrociato corrisponde al predicato 1=0. 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 FULL ESTERNO JOIN B ON 1 = 0

Con una modifica minore alla query precedente è possibile simulare un UNION ALL delle due tabelle.

UNION ALL

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

Si noti che la clausola WHERE (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 viene valutato su true, quindi la riga preservata dall'unione esterna finisce scartata (insieme a quella blu) convertendo efficacemente il join in uno interno.

LOJtoInner

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

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

inserisci la descrizione dell'immagine qui

SQL Fiddle

Guarda questi esempi esegui live su SQLFiddle.com .

    
623
2018-08-03 07:59:59Z
  1. Dirò che mentre questo non funziona per me quasi come i diagrammi di Venn, apprezzo che le persone variano e imparano in modo diverso e questo è molto ben presentato spiegazione diversa da quella che ho visto prima, quindi supporto @ypercube nell'assegnazione dei punti bonus. Anche un buon lavoro che spiega la differenza di mettere condizioni aggiuntive nella clausola JOIN vs la clausola WHERE. Complimenti a te, Martin Smith.
    2014-12-20 04: 48: 36Z
  2. @ OldPro I diagrammi di Venn sono giusti per quanto possibile, suppongo, ma sono silenziosi su come rappresentare un cross join, o per differenziare un tipo di predicato di join come come equi si unisce da un altro. Il modello mentale di valutare il predicato dell'aggregazione su ogni riga del risultato dell'aggiunta incrociata, quindi aggiungere di nuovo righe non corrispondenti se un join esterno e infine valutare il punto in cui funziona meglio per me.
    2014-12-20 11: 05: 32Z
  3. I diagrammi di Venn sono validi per rappresentare le unioni, le intersezioni e le differenze ma non i join. Hanno un valore educativo minore per join molto semplici, cioè join in cui la condizione di unione si trova su colonne univoche.
    2014-12-20 22: 28: 49Z
  4. @ Arth - No, ti sbagli. SQL Fiddle sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/5155 questo è qualcosa del Venn i diagrammi non possono illustrare.
    28/01/2016 15: 54: 10Z
  5. @ MartinSmith Wow, sono d'accordo, ho sbagliato completamente! Troppo abituato a lavorare con one-to-manys .. grazie per la correzione.
    28/01/2016 15: 57: 18Z

I join vengono utilizzati 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:

inserisci la descrizione dell'immagine qui

Join interno: - Inner join crea una nuova tabella dei risultati combinando i valori delle colonne di due tabelle ( Employee e Location ) in base al predicato join. La query confronta ogni riga di Impiegato con ogni riga di Posizione per trovare tutte le coppie di righe che soddisfano il predicato di join. Quando il predicato di join viene soddisfatto dall'accoppiamento di valori non NULL, i valori di colonna per ogni coppia di righe corrispondenti di Employee e Location vengono combinati in una riga 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: inserisci la descrizione dell'immagine qui  inserisci la descrizione dell'immagine qui

Join esterno: - 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 nei join esterni a sinistra e nei join esterni a destra, a seconda delle righe della 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 Employee e Location contiene sempre tutti i record della tabella "left" ( Employee ), anche se la condizione di join non trova alcun record corrispondente nella tabella "giusta" ( 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: inserisci la descrizione dell'immagine qui  inserisci la descrizione dell'immagine qui

Join esterno destro: - Un join esterno destro (o join destro) assomiglia molto a un join esterno sinistro, tranne che per il trattamento dei tavoli invertiti. Ogni riga della tabella "destra" ( Posizione ) verrà visualizzata nella tabella unita almeno una volta. Se non esiste alcuna riga corrispondente dalla tabella "left" ( Employee ), NULL apparirà in colonne da Employee per quei record che non hanno corrispondenza in Location . Ecco come appare 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:

inserisci la descrizione dell'immagine qui  inserisci la descrizione dell'immagine qui

Full Outer Joins: - Full Outer Join o Full Join è per conservare le informazioni non corrispondenti includendo righe non corrispondenti ai 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. inserisci la descrizione dell'immagine qui

Fonte immagine

Manuale di riferimento di MySQL 8.0 - Join Syntax

Operazioni di Join Oracle

    
142
2018-08-07 14: 38: 49Z

Join interno

Recupera solo le righe corrispondenti, ovvero A intersect B.

Inserisci la descrizione dell'immagine qui

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

Join esterno sinistro

Seleziona tutti i record dalla prima tabella e tutti i record nel secondo tabella che corrisponde alle chiavi unite.

Inserisci la descrizione dell'immagine qui

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

Join esterno completo

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

Inserisci la descrizione dell'immagine qui

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

Riferimenti

121
2014-07-21 15: 38: 32Z
  1. Qual è il nome dello strumento? Trovo che sia interessante in quanto mostra il numero di righe e diagrammi di Venn
    2014-01-27 12: 23: 03Z
  2. @ GrijeshChauhan Datamartist :)
    2014-01-27 12: 25: 31Z
  3. @ Trushar :( non è per il sistema Linux ..
    2014-01-27 12: 27: 42Z
  4. @ GrijeshChauhan Sì, ma puoi provare a eseguirlo utilizzando vino .
    2014-01-27 12: 30: 38Z
  5. Ohh! si .. ho usato SQLyog usando vino .. c'è anche PlayOnLinux
    2014-01-27 12: 32: 59Z

In semplicissimoe parole:

Un inner join recupera solo le righe corrispondenti.

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

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

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

  • Completo : tutte le righe in tutte le tabelle. Non importa se c'è una corrispondenza o meno

106
2014-07-21 15: 35: 37Z
  1. @ nomen Non che questa risposta la indirizzi, ma INNER JOIN è un'intersezione e FULL OUTER JOIN è la corrispondente UNION if left & i set /cerchi giusti contengono le righe di (rispettivamente) LEFT & DIRETTO. PS Questa risposta non è chiara sulle righe in input vs output. Confonde "nella tabella sinistra /destra" con "ha una parte sinistra /destra in sinistra /destra" e utilizza "riga corrispondente" rispetto a "tutto" per significare riga estesa per riga da un'altra tabella vs per null.
    2015-11-29 01: 17: 46Z

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 ci sono righe corrispondenti, le colonne per l'altro lato (a destra) mostreranno NULL.

    
100
2015-01-05 01: 14: 26Z

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 correlati da un "ID ordine".

Ordini

  • IDOrdine
  • CustomerName

OrderDetails

  • OrderDetailID
  • IDOrdine
  • ProductName
  • Quantità
  • Prezzo

La richiesta ​​p>  

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 cambi 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.

    
75
2018-09-10 08: 25: 51Z
  1. Apprezzo l'esempio semplice ma realistico. Ho cambiato una richiesta come SELECT c.id, c.status, cd.name, c.parent_id, cd.description, c.image FROM categories c, categories_description cd WHERE c.id = cd.categories_id AND c.status = 1 AND cd.language_id = 2 ORDER BY c.parent_id ASC a SELECT c.id, c.status, cd.name, c.parent_id, cd.description, c.image FROM categories c INNER JOIN categories_description cd ON c.id = cd.categories_id WHERE c.status = 1 AND cd.language_id = 2 ORDER BY c.parent_id ASC (MySQL) con successo. Non ero sicuro delle condizioni aggiuntive, si mischiano bene ...
    2013-01-05 11: 11: 24Z

In parole semplici:

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

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.
    }
  }

Right join : esattamente opposto di left join. Inserisci il nome del tavolo in SINISTRA SINISTRA sul lato destro in Giuntura a destra, ottieni lo stesso risultato di SINISTRA SINISTRA.

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

Esempio:

Esempio

Supponiamo ora per 2 tabelle

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 contienes emp_id come chiave esterna che collega employee.id quindi la sua tabella figlio.)

Join interni

Prendi i record di 2 tabelle SOLO SE La 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.

Join a sinistra :

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

 
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;

Join esterni :

 
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:

Diagramma

    
62
2015-02-19 05: 11: 56Z
  1. Il risultato non ha nulla da fare (fare di per sé) con chiavi primarie /uniche /candidate e amp; chiavi esterne. Il baviour può e deve essere descritto senza riferimento a loro. Viene calcolato un cross join, quindi le righe che non corrispondono alla condizione ON vengono filtrate; inoltre per le righe esterne le righe filtrate /non abbinate vengono estese da NULL (per SINISTRA /DESTRA /COMPLETA e incluse.
    2015-08-10 04: 27: 54Z

Utilizza INNER JOIN per restituire tutte le righe da entrambe le tabelle in cui è presente una corrispondenza. Ad esempio, nella tabella risultante tutte le righe e le colonne avranno valori.

In OUTER JOIN la tabella risultante potrebbe avere colonne vuote. L'unione esterna può essere LEFT o RIGHT.

LEFT OUTER JOIN restituisce tutte le righe dalla prima tabella, anche se non ci sono corrispondenze nella seconda tabella.

RIGHT OUTER JOIN restituisce tutte le righe dalla seconda tabella, anche se non ci sono corrispondenze nella prima tabella.

    
55
2015-06-09 03: 21: 07Z

 Questa è una buona spiegazione per i join

Questa è una buona spiegazione schematica per tutti i tipi di join

fonte: http://ssiddique.info/understanding-sql- unisce-in-facile-way.html

    
54
2016-10-18 18: 48: 59Z

INNER JOIN richiede che ci sia almeno una corrispondenza nel confronto tra le due tabelle. Ad esempio, tabella A e tabella B che implica A 8 B (A intersezione B).

LEFT OUTER JOIN e LEFT JOIN sono uguali. Fornisce tutti i record corrispondenti in entrambe le tabelle e tutte le possibilità della tabella sinistra.

Allo stesso modo, RIGHT OUTER JOIN e RIGHT JOIN sono uguali. Fornisce tutti i record corrispondenti in entrambe le tabelle e tutte le possibilità del tavolo giusto.

FULL JOIN è la combinazione di LEFT OUTER JOIN e RIGHT OUTER JOIN senza duplicazione.

    
51
2013-09-26 21: 25: 28Z

La risposta è nel significato di ciascuno, quindi nei risultati.

  

Nota:   Nel SQLite non c'è il RIGHT OUTER JOIN o il FULL OUTER JOIN.
  E anche nel MySQL non c'è il FULL OUTER JOIN.

La mia risposta è basata su Nota ​​strong>.

Quando hai due tabelle come queste:

 
--[table1]               --[table2]
id | name                id | name
---+-------              ---+-------
1  | a1                  1  | a2
2  | b1                  3  | b2

CROSS JOIN /OUTER JOIN:
Puoi avere tutti questi dati di tabelle con CROSS JOIN o solo con , in questo modo:

 
SELECT * FROM table1, table2
--[OR]
SELECT * FROM table1 CROSS JOIN table2

--[Results:]
id | name | id | name 
---+------+----+------
1  | a1   | 1  | a2
1  | a1   | 3  | b2
2  | b1   | 1  | a2
2  | b1   | 3  | b2

JOIN INTERNO:
Quando si desidera aggiungere un filtro ai risultati sopra riportati in base a una relazione come table1.id = table2.id, è possibile utilizzare INNER JOIN:

SELECT * FROM table1, table2 WHERE table1.id = table2.id
--[OR]
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

--[Results:]
id | name | id | name 
---+------+----+------
1  | a1   | 1  | a2

SINISTRA [OUTER] JOIN:
Quando vuoi avere tutte le righe di una delle tabelle nel risultato sopra riportato - con la stessa relazione- puoi usare LEFT JOIN:
(Per RIGHT JOIN cambia solo il luogo delle tabelle)

 
SELECT * FROM table1, table2 WHERE table1.id = table2.id 
UNION ALL
SELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)
--[OR]
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

--[Results:]
id | name | id   | name 
---+------+------+------
1  | a1   | 1    | a2
2  | b1   | Null | Null

FULL OUTER JOIN:
Quando vuoi anche avere tutte le righe dell'altro tavolo nei risultati puoi usare FULL OUTER JOIN:

 
SELECT * FROM table1, table2 WHERE table1.id = table2.id
UNION ALL
SELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)
UNION ALL
SELECT Null, Null, * FROM table2 WHERE Not table2.id In (SELECT id FROM table1)
--[OR] (recommended for SQLite)
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
UNION ALL
SELECT * FROM table2 LEFT JOIN table1 ON table2.id = table1.id
WHERE table1.id IS NULL
--[OR]
SELECT * FROM table1 FULL OUTER JOIN table2 On table1.id = table2.id

--[Results:]
id   | name | id   | name 
-----+------+------+------
1    | a1   | 1    | a2
2    | b1   | Null | Null
Null | Null | 3    | b2

Bene, in base alle tue esigenze scegli ognuno di quelli che coprono le tue necessità;).

    
39
2017-09-12 08: 50: 33Z
  1. Puoi aggiungere alla tua nota, che non c'è neanche il full outer join in MySQL.
    2015-06-14 13: 34: 13Z

Join interno.

Un join sta combinando le righe di due tabelle. Un inner join tenta di abbinare le due tabelle in base ai criteri specificati nella query e restituisce solo le righe corrispondenti. Se una riga della prima tabella del join corrisponde a due righe nella seconda tabella, verranno restituite due righe nei risultati. Se c'è una riga nella prima tabella che non corrisponde a una riga nel secondo, non viene restituita; allo stesso modo, se c'è una riga nella seconda tabella che non corrisponde a una riga nella prima, non viene restituita.

Outer Join.

Un left join tenta di trovare la corrispondenza delle righe dalla prima tabella alle righe nella seconda tabella. Se non riesce a trovare una corrispondenza, restituirà le colonne dalla prima tabella e lascerà le colonne dalla seconda tabella vuota (null).

    
31
2016-02-02 11: 14: 00Z

Non vedo molti dettagli su prestazioni e ottimizzatore nelle altre risposte.

A volte è bene sapere che solo INNER JOIN è associativo, il che significa che l'ottimizzatore ha la maggior possibilità di giocarci. Può riordinare l'ordine di join per renderlo più veloce mantenendo lo stesso risultato. L'ottimizzatore può utilizzare la maggior parte delle modalità di join.

Generalmente è una buona pratica provare a utilizzare INNER JOIN invece del diverso tipo di join. (Naturalmente se è possibile considerare il set di risultati atteso.)

Ci sono un paio di buoni esempi e spiegazioni su questo strano comportamento associativo:

25
2017-05-23 12: 34: 48Z
  1. Non può essere una "buona pratica" usare un tipo di join su un altro. Il join che utilizzi determina i dati che desideri. Se ne usi uno diverso non sei corretto. Inoltre, in Oracle almeno questa risposta è completamente sbagliata. Sembra completamente sbagliato per tutto e non hai prove. Hai una prova?
    2014-12-26 08: 51: 00Z
  2. 1. Voglio dire provare da usare. Ho visto molte persone che utilizzano LEFT OUTER si uniscono ovunque senza una buona ragione. (Le colonne unite erano "non null".) In quei casi sarebbe decisamente meglio usare i join INNER. 2. Ho aggiunto un link che spiega il comportamento non associativo meglio di quanto potrei.
    2014-12-26 11: 01: 45Z
  3. Come so che INNER JOIN è più lento del LEFT JOIN nella maggior parte delle volte, E le persone possono usare LEFT JOIN invece di INNER JOIN aggiungendo un WHERE per rimuovere risultati NULL inaspettati;).
    2015-05-13 03: 01: 51Z
  4. Questi commenti mi hanno reso un po 'incerto. Perché pensi che il INNER sia più lento?
    2015-05-13 08: 04: 42Z

L'algoritmo preciso per INNER JOIN, LEFT/RIGHT OUTER JOIN è il seguente:

  1. Prendi ciascuna riga dalla prima tabella: a
  2. Considera tutte le righe della seconda tabella accanto: (a, b[i])
  3. Valuta la clausola ON ... per ogni coppia: ON( a, b[i] ) = true/false?
    • Quando la condizione viene valutata a true, restituisci la riga combinata (a, b[i]).
    • Quando raggiungi la fine della seconda tabella senza alcuna corrispondenza, e questo è un Outer Join, quindi restituisci una coppia (virtuale) usando Null per tutte le colonne di un'altra tabella: (a, Null) per UNIX outer join o (Null, b) per RIGHT join esterno. Questo per garantire che tutte le righe della prima tabella siano presenti nei risultati finali.

Nota: la condizione specificata nella clausola ON potrebbe essere qualsiasi cosa, non è necessario utilizzare Chiavi primarie (e non è necessario fare sempre riferimento alle Colonne da entrambi i tavoli)! Ad esempio:

 Inner Join vs. Left Outer Join


 inserisci la descrizione dell'immagine qui

Nota: Join sinistro = Left Join esterno, Right Join = Right Join esterno.

    
22
2018-10-11 14: 16: 16Z

Dopo aver criticato il tanto amato diagramma di Venn con le sfumature rosse, ho pensato che fosse giusto pubblicare il mio tentativo.

Sebbene la risposta di @Martin Smith sia la migliore di questo gruppo da molto tempo, la sua unica mostra la colonna chiave di ogni tabella, mentre penso che idealmente dovrebbero essere mostrate anche le colonne non chiave.

Il meglio che potevo fare nella mezz'ora permessa, continuo a non pensare che dimostri in modo adeguato che i valori nulli sono presenti a causa dell'assenza di valori chiave in TableB o che OUTER JOIN è in realtà un sindacato piuttosto che un join:

 inserisci la descrizione dell'immagine qui

    
20
2016-01-22 15: 23: 20Z
  1. La domanda chiede Differenza tra join INNER e OUTER, tuttavia non necessariamente left outer join lol
    2016-01-22 19: 32: 48Z
  2. @ LearnByReading: la mia immagine a destra è un join esterno destro, sostituendo TableA a LEFT OUTER JOIN TableB b con TableB B RIGHT OUTER JOIN TableA a
    2019-04-29 07: 58: 14Z

 inserisci la descrizione dell'immagine qui

  •  INNER JOIN join più tipico per due o più tabelle. Restituisce la corrispondenza dei dati su entrambe le tabelle ON keykey e relazione forignkey.
  •  OUTER JOIN è uguale a INNER JOIN, ma include anche NULL dati su ResultSet.
    •  LEFT JOIN = INNER JOIN + dati non corrispondenti della tabella sinistra con corrispondenza Null sulla tabella destra.
    •  RIGHT JOIN = INNER JOIN + dati non corrispondenti della tabella destra con corrispondenza Null sulla tabella sinistra.
    •  FULL JOIN = INNER JOIN + dati senza corrispondenza su entrambe le tabelle destra e sinistra con Null corrispondenze.
  • Self join non è una parola chiave in SQL, quando una tabella fa riferimento a dati in sé noti come self join. Utilizzando INNER JOIN e OUTER JOIN possiamo scrivere query di self join.

Ad esempio:

 
SELECT * 
FROM   tablea a 
       INNER JOIN tableb b 
               ON a.primary_key = b.foreign_key 
       INNER JOIN tablec c 
               ON b.primary_key = c.foreign_key 
    
19
2019-03-21 22: 19: 48Z

Definizioni più semplici

Join interno: restituisce record abbinati da entrambe le tabelle.

Join esterno completo: restituisce i record corrispondenti e non corrispondenti di entrambe le tabelle con valori null per record non corrispondenti da Entrambe le tabelle .

Left Outer Join: restituisce i record corrispondenti e non corrispondenti solo dalla tabella su Lato sinistro .

Right Outer Join: restituisce i record corrispondenti e non corrispondenti solo dalla tabella su Lato destro .

In-breve

Corrispondenti + Sinistra Non corrispondenti + Giusti non corrispondenti = Join esterno completo

Corrispondenti + Sinistra Non corrispondenti = Join esterno sinistro

Corrispondenti + Giusti non corrispondenti = Giusti Join esterni

Corrispondenza = Join interno

    
17
2016-04-28 09: 10: 48Z
  1. Questo è geniale e spiega perché join non funziona come previsto per gli indici delle serie temporali. I timestamp a distanza di un secondo non hanno eguali.
    2017-09-05 09: 22: 46Z
  2. @ yeliabsalohcin Non si spiega "come previsto" qui o "funziona" nel commento alla domanda. È solo un equivoco personale inspiegabile che ti aspetti stranamente che gli altri abbiano. Se trattate le parole in modo scomodo mentre state leggendo - interpretando erroneamente la scrittura e /o accettando la scrittura poco chiara - come quando state scrivendo qui, potete aspettarvi di avere idee sbagliate. In realtà questa risposta come la maggior parte qui non è chiara & sbagliato. "Inner Join: restituisce i record corrispondenti da entrambe le tabelle" è sbagliato quando i set di colonne di input sono diversi. È cercando di dire qualcosa, ma non è . (Vedi la mia risposta.)
    2017-11-22 02: 52: 49Z

In termini semplici,

1. JOIN INTERNO O EQUI JOIN: restituisce il set di risultati che soddisfa solo la condizione in entrambe le tabelle.

2. OUTER JOIN: restituisce il set di risultati di tutti i valori di entrambe le tabelle, anche se c'è una corrispondenza di condizione o meno.

3. JOIN SINISTRO: restituisce il set di risultati di tutti i valori dalla tabella sinistra e solo le righe che corrispondono alla condizione nella tabella di destra.

4. RIGHT JOIN: restituisce il set di risultati di tutti i valori dalla tabella di destra e solo le righe che corrispondono alla condizione nella tabella di sinistra.

5. FULL JOIN: Join completo e Full outer Join sono uguali.

    
6
2016-10-13 09: 04: 54Z

1. Join interno: anche chiamato come join. Restituisce le righe presenti sia nella tabella sinistra, sia nella tabella destra solo se c'è una corrispondenza . In caso contrario, restituisce zero record.

Esempio:

 
SELECT
  e1.emp_name,
  e2.emp_salary    
FROM emp1 e1
INNER JOIN emp2 e2
  ON e1.emp_id = e2.emp_id

 output1

2. Join esterno completo: anche chiamato Full Join. Restituisce tutte le righe presenti sia nella tabella sinistra sia nella tabella destra.

Esempio:

 
SELECT
  e1.emp_name,
  e2.emp_salary    
FROM emp1 e1
FULL OUTER JOIN emp2 e2
  ON e1.emp_id = e2.emp_id

 output2

3. Join esterno sinistro: o semplicemente chiamato Join sinistro. Restituisce tutte le righe presenti nella tabella di sinistra e le righe corrispondenti dalla tabella di destra (se presente).

4. Right Outer Join: Chiamato anche come Right Join. Restituisce le righe corrispondenti dalla tabella sinistra (se presente) e tutte le righe presenti nella tabella di destra.

 unisce

Vantaggi dei join

  1. Esegue più velocemente.
4
2017-07-28 04: 17: 49Z
  1. Questo è corretto solo quando le tabelle hanno lo stesso set di colonne. (Confonde l'unione interna con l'intersezione e l'unione completa con l'unione). Inoltre, la "corrispondenza" non è definita. Leggi i miei altri commenti.
    2017-07-24 13: 02: 37Z
  • Join interno - Un inner join utilizzando una delle query equivalenti fornisce l'intersezione delle due tabelle , ovvero le due righe che hanno in comune.

  • Join esterno sinistro - Un join esterno sinistro fornirà tutte le righe in A, più eventuali righe comuni in B.

  • Join esterno completo - Un join esterno completo ti darà l'unione di A e B, ovvero Tutte le righe in A e tutte le righe in B Se qualcosa in A non ha un dato corrispondente in B, allora la porzione B è nullo, e viceversa

3
2016-06-23 13: 55: 57Z
  1. Questo è sia sbagliato che non chiaro. Unirsi è non un incrocio a meno che le tabelle non abbiano le stesse colonne. I join esterni non hanno righe da A o B a meno che non abbiano le stesse colonne, nel qual caso non sono aggiunti valori nulli. Stai cercando di dire qualcosa, ma non lo stai dicendo. Non stai spiegando in modo corretto o chiaro.
    2017-02-03 11: 15: 30Z
  2. @ philipxy: Disaccordo sulla tua dichiarazione Join is not an intersection unless the tables have the same columns No. Puoi unire qualsiasi colonna che vuoi e se il valore corrisponde, si uniranno insieme.
    2017-04-11 09: 23: 49Z
  3. Questo commento non è chiaro come risposta. (Suppongo che tu stia pensando qualcosa del genere, l'insieme di valori di subrow per le colonne comuni del risultato è l'intersezione degli insiemi di valori di subrow per le colonne comuni di ciascuno degli input, ma non è quello che hai scritto. non sono chiari.)
    2017-04-11 14: 24: 05Z

left join on (alias left outer join on) restituisce inner join on righe union all righe della tabella sinistra senza corrispondenza estese per null.

right join (on alias right outer join on) restituisce inner join on righe union all righe della tabella di destra non corrispondenti estese per null.

full join on (alias full outer join on) restituisce inner join on righe union all righe della tabella sinistra senza corrispondenza estese da null union all righe della tabella di destra senza corrispondenza estese per null.

(SQL Standard 2006 SQL /Foundation 7.7 Regole di sintassi 1, Regole generali 1 b, 3 c & d, 5 b.)

Quindi non outer join fino a quando non saprai che cosa implica il inner join sottostante.


Scopri quali righe inner join restituisce .

Leggi i miei commenti in merito ai molti confusi e amp; risposte scadenti.

Poi leggi i miei commenti qui i molti confusi e amp; risposte scadenti.

    
3
2018-07-23 05: 53: 22Z
  1. Ho davvero letto i tuoi numerosi commenti. Quando si dice "un diagramma di Venn, se correttamente interpretato, può rappresentare l'unione interna vs esterna" intendi se correttamente interpretato dall'osservatore o dal diagramma di Venn stesso? Se è il secondo, per favore disegnalo:)
    2019-04-24 16: 08: 07Z
  2. Non sono sicuro di cosa stai cercando di dire. Sto parlando dell'interpretazione standard di un diagramma di Venn come insiemi di elementi. (Perché alcuni usi dei diagrammi non riescono nemmeno a gestirlo.) "Correttamente" per un'applicazione include dire cosa sono gli insiemi e /o gli elementi. Vedi il commento in cima a questa pagina con 50 upvotes rispetto a un diagramma di Venn per i join interni vs esterni. Modificherò alcuni dei miei commenti in questa domanda. Non voglio un diagramma di Venn in questo post.
    2019-04-24 16: 31: 27Z
  3. Non voglio nemmeno diagrammi di Venn!
    2019-04-25 15: 52: 29Z
  4. Devo ammettere che, nonostante il mio rapido fraseggio nei commenti, perché SQL coinvolge borse e amp; null e la cultura SQL non hanno una terminologia comune per nominare & distinguere tra nozioni rilevanti, non è banale nemmeno spiegare chiaramente come gli elementi di un diagramma di Venn siano 1: 1 con "righe" di output, per non parlare di input di "righe". O quali sono i join interni o esterni, per non parlare della loro differenza. "valore" può includere o meno NULL, "riga" può essere una lista di valori rispetto a uno slot in un valore di tabella o variabile & "=" potrebbe essere SQL "=" rispetto all'uguaglianza.
    2019-04-25 17: 44: 05Z
  5. Simile alla nostra discussione sul prodotto cartesiano-prodotto-vs-relazionale, sospetto che sia il caso che i diagrammi di Venn abbiano molto senso per la gente che capisce già il differenze tra i tipi di join!
    2019-04-29 07: 51: 15Z

La differenza tra inner join e outer join è la seguente:

  1. Inner join è un join che combina le tabelle in base alle tuple corrispondenti, mentre outer join è un join che combina la tabella in base a tuple corrispondenti e non abbinate.
  2. L'unione interna unisce la riga con corrispondenza di due tabelle in cui vengono omesse le righe non corrispondenti, mentre l'unione esterna unisce le righe di due tabelle e le righe non corrispondenti si riempiono di valore null.
  3. Il join interno è come un'operazione di intersezione, mentre il join esterno è come un'operazione di unione.
  4. Il join interno è di due tipi, mentre quello esterno è di tre tipi.
  5. Il join interno è più lento, mentre il join esterno è più veloce di inner join.
1
2017-10-17 12: 25: 56Z

Considera di seguito 2 tabelle:

EMP

 
empid   name    dept_id salary
1       Rob     1       100
2       Mark    1       300
3       John    2       100
4       Mary    2       300
5       Bill    3       700
6       Jose    6       400

Dipartimento

 
deptid  name
1       IT
2       Accounts
3       Security
4       HR
5       R&D

Join interno:

Generalmente scritto come solo UNISCI in query SQL. Restituisce solo i record corrispondenti tra le tabelle.

Scopri tutti i dipendenti e i loro nomi di dipartimento:

 
Select a.empid, a.name, b.name as dept_name
FROM emp a
JOIN department b
ON a.dept_id = b.deptid
;

empid   name    dept_name
1       Rob     IT
2       Mark    IT
3       John    Accounts
4       Mary    Accounts
5       Bill    Security

Come vedi sopra, Jose non viene stampato da EMP nell'output poiché è dept_id 6 non trova una corrispondenza nella tabella Department. Allo stesso modo, le righe HR e R&D non vengono stampate dalla tabella Dipartimento in quanto non hanno trovato una corrispondenza nella tabella Emp.

Quindi, INNER JOIN o semplicemente JOIN, restituisce solo le righe corrispondenti.

SINISTRA:

Ciò restituisce tutti i record dalla tabella LEFT e solo i record corrispondenti dalla tabella RIGHT.

 
Select a.empid, a.name, b.name as dept_name
FROM emp a
LEFT JOIN department b
ON a.dept_id = b.deptid
;

empid   name    dept_name
1       Rob     IT
2       Mark    IT
3       John    Accounts
4       Mary    Accounts
5       Bill    Security
6       Jose    

Quindi, se osservi l'output sopra riportato, tutti i record della tabella LEFT (Emp) vengono stampati con i record corrispondenti della tabella RIGHT.

Le righe HR e R&D non vengono stampate dalla tabella Dipartimento in quanto non hanno trovato una corrispondenza nella tabella Emp su dept_id.

Quindi, LEFT JOIN restituisce TUTTE le righe dalla tabella Left e solo le righe corrispondenti dalla tabella RIGHT.

Puoi anche controllare DEMO qui .

    
0
2018-11-25 15: 04: 40Z
  1. Niente in questo descrive chiaramente cosa fa uno dei due join. (Né si rivolge a una "differenza" tra di loro, oltre a dire che sono diversi.) Questo non aggiunge nulla alle molte risposte (molte molto in voga) di una domanda di 10 anni - è "utile"?
    2018-11-27 01: 19: 10Z
fonte posta Qui