Introduzione ai join esterni

Join esterni

I join esterni sono usati per abbinare le righe di due tabelle. Anche se non ci sono righe di corrispondenza sono inclusi. Le righe di una delle tabelle sono sempre incluse, per l’altra, quando non ci sono corrispondenze, i valori NULL sono inclusi.

La serie inizia con l’articolo Introduzione ai join del database. Tutti gli esempi di questa lezione sono basati su Microsoft SQL Server Management Studio e il database AdventureWorks2012. È possibile iniziare a utilizzare questi strumenti gratuiti utilizzando la mia guida Per iniziare a utilizzare SQL Server., In questo articolo ci accingiamo a coprire join esterni.

Tipi di Join esterni

Esistono tre tipi di join esterni:

• Left Outer Join – Tutte le righe della tabella di sinistra sono incluse, le righe non corrispondenti dalla destra vengono sostituite con valori NULL.
• Right Outer Join-Tutte le righe della tabella di destra sono incluse, le righe non corrispondenti da sinistra vengono sostituite con valori NULL.
• Full Outer Join-Tutte le righe di entrambe le tabelle sono incluse, i valori NULL riempiono righe non corrispondenti.

Cerchiamo di scavare un più profondo ed esplorare il join esterno sinistro.,

Left Outer Join

Controlla il seguente modello di dati. Questo è preso dal database AdventureWorks2012. In questo modello, c’è 1 persona a 0 o 1 dipendenti.

Per costruire un elenco di tutti i nomi di persona, ma anche mostrare JobTitle se la persona è un dipendente, abbiamo bisogno di un modo per unire le due tabelle e includere le righe di persona nel risultato, anche se non corrispondono a Employee.

Questo tipo di join è chiamato left outer join, poiché tutte le righe per la tabella dal lato sinistro della parola chiave JOIN sono incluse indipendentemente dalla corrispondenza., La sintassi di base per un left outer join è:

SELECT columnlistFROM tableLEFT OUTER JOIN othertable ON join condition

SQL per il join nel diagramma di cui sopra è:

SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID

Ecco i primi risultati della query

da Notare come non ci sono i valori NULL elencati in seconda fila per NationalIDNumber e JobTitle. Questo perché non ci sono dipendenti corrispondenti BusinessEntityID 293.

Right Outer Join

Diamo un’altra occhiata al diagramma, ma questa volta stiamo facendo un right outer join., Come avrai intuito, non c’è troppa differenza nell’istruzione SQL tra un join esterno sinistro e un join esterno destro. La sintassi di base per un join esterno destro è:

SELECT columnlistFROM tableRIGHT OUTER JOIN othertable ON join condition

di Seguito la nostra query di esempio scritto come un join esterno destro:

SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonRIGHT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID

La differenza principale è che ora stiamo andando a restituire tutti i record dalla tabella del Dipendente, che è la tabella per il diritto di parola chiave join. Se non viene trovato un record Dipendente corrispondente, verrà restituito NULL per BusinessEntityID e LastName.

Ecco i risultati della query.,

Ho fatto scorrere tutti i risultati e sono rimasto sorpreso di non vedere alcun valore nullo.

Sai perché?

La risposta sta nel modello di dati. C’è uno 0..1 a 1 rapporto tra dipendente e persona. Ciò significa che per ogni dipendente c’è una persona. Detto questo, per il join giusto non esisteranno righe non corrispondenti. Con questo tipo di relazione avresti potuto anche usare un join interno.

Left versus Right Outer Join

Non vi è alcuna differenza di funzionalità tra un join esterno sinistro e un join esterno destro.,

L’istruzione

SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID

Restituisce lo stesso risultato di

SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM HumanResources.EmployeeRIGHT OUTER JOIN person.Person ON person.BusinessEntityID = Employee.BusinessEntityID

Ovviamente questo non sarebbe il caso se avessi cambiato solo il join da SINISTRA a DESTRA e non cambiato i nomi delle tabelle.

Di solito uso i join esterni di sinistra più di quelli esterni di destra. Penso che questo sia perché quando disegno relazioni lo faccio da sinistra a destra. Inoltre, traverso i tavoli nella mia testa da sinistra a destra.

Questo si adatta bene a SQL poiché la tabella “sinistra” si trova nell’istruzione FROM.

Sono curioso di sapere cosa usi., Sono davvero curioso di sapere se un join a destra ti sembra più intuitivo se sei madrelingua arabo o qualche altra lingua “da destra a sinistra”.

Full Outer Join

Un full outer join è la combinazione dei risultati di un Full outer join sinistro e destro. I risultati restituiti da questo tipo di join includono tutte le righe di entrambe le tabelle. Dove si verificano corrispondenze, i valori sono correlati. Dove abbinati da entrambe le tabelle non lo fanno, vengono restituiti invece NULL.,

La sintassi di base per un join esterno completo è:

SELECT columnlistFROM tableFULL OUTER JOIN othertable ON join condition

Diamo un’occhiata a una porzione diversa del database AdventureWork2012. Questa volta, ci concentreremo sulle relazioni tra le tabelle SalesOrderHeader e CurrencyRate.

Il modello è mostrato di seguito:

Supponiamo di voler conoscere tutte le valute in cui possiamo effettuare ordini e quali ordini sono stati effettuati in quelle valute?

SELECT sales.SalesOrderHeader.AccountNumber, sales.SalesOrderHeader.OrderDate, sales.CurrencyRate.ToCurrencyCode, sales.CurrencyRate.AverageRateFROM sales.SalesOrderHeaderFULL OUTER JOIN sales.CurrencyRate ON sales.CurrencyRate.CurrencyRateID = sales.SalesOrderHeader.CurrencyRateID

Ecco una parte dei risultati che mostra dove alcune vendite hanno corrispondenza con una valuta e alcune che non hanno., Il motivo per cui ci sono vendite che non corrispondono è che queste sono vendite in USD.

Più in basso nei risultati si vedono valute senza vendite corrispondenti. Ciò riflette il fatto che non sono state effettuate vendite in tali valute.

Nota: sono rimasto sorpreso nel vedere l’USD elencato, vedi la riga 42463, dal momento che penserei che la maggior parte delle vendite sarebbe in questa valuta. Il mio pensiero è che piuttosto che riverire il tasso di valuta per queste transazioni, SalesOrderHeader vale per CurrencyRateID è stato impostato su null per tutte le transazioni in USD., Penso che questo sia incoerente, e non è il modo in cui lo farei, ma non è il mio database

Esempio avanzato

Finora abbiamo esaminato i tre tipi di join esterni ma non abbiamo esplorato alcuni concetti più avanzati come unire più tabelle e utilizzare più di una condizione nelle nostre clausole di join.

Abbiamo coperto questi concetti quando abbiamo esplorato i join interni, quindi quello che ti mostrerò non dovrebbe essere troppo nuovo, ma penso che abbia ancora senso rivedere, poiché in alcuni casi mescolare join completi con join interni può produrre risultati inaspettati o non voluti.,

Rivolgiamo la nostra attenzione allo schema di produzione ed esploriamo prodotti e categorie. Produciamo un elenco di tutte le categorie di prodotti e dei modelli di prodotto contenuti all’interno.

Il prodotto ha una relazione uno a molti con ProductModel e ProductSubcategory. Poiché si trova tra queste due tabelle, esiste una relazione implicita molti a molti tra ProductModel e ProductSubcategory. Per questo motivo, è un buon candidato per i join esterni in quanto potrebbero esserci modelli di prodotto senza prodotti assegnati e voci ProductSubcategory senza prodotto.,

Per superare questa situazione eseguiremo un join esterno alla tabella ProductModel e ProductCategory.

Ecco SQL

SELECT PC.Name AS Category, PSC.Name AS Subcategory, PM.Name AS Model, P.Name AS ProductFROM Production.Product AS PFULL OUTER JOIN Production.ProductModel AS PM ON PM.ProductModelID = P.ProductModelIDFULL OUTER JOIN Production.ProductSubcategory AS PSC ON PSC.ProductSubcategoryID = P.ProductSubcategoryIDINNER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryIDORDER BY PC.Name, PSC.Name

Ci sono diversi elementi da notare:

• Ho usato gli alias di tabella per rendere l’SQL più leggibile.
• Esiste più di una clausola di join esterno completo.,
• La tabella ProductCategory fa anche parte di un join esterno

Originariamente quando ho scritto l’SQL per questa query avevo un join interno tra ProductSubcategory e ProductCategory, ma non vedevo valori NULL per record non corrispondenti che mi sarei aspettato.

Una volta cambiato il join in un join esterno completo, ho visto i risultati che mi aspettavo. La ragione per cui ciò si verifica è sottile.

Dopo aver controllato i dati ho confermato che a tutte le categorie sono assegnate sottocategorie., Dato questo si potrebbe pensare che un join interno funzionerebbe; tuttavia, si consideri che quando l’intera istruzione viene eseguita e vengono restituite le righe, il valore ProductSubcategoryID è NULL ogni volta che un prodotto non riesce a corrispondere a una sottocategoria di prodotto.

I valori null, per definizione, non sono uguali tra loro, quindi il join interno fallisce. Detto questo, quando questi valori vengono poi abbinati a ProductCategory non vengono inclusi nel risultato a meno che il join to ProductCategory non sia un join esterno.,

In effetti, il join non deve essere un join esterno completo, un join sinistro funziona altrettanto bene:

SELECT PC.Name AS Category,PSC.Name AS Subcategory,PM.Name AS Model,P.Name AS ProductFROM Production.Product AS PFULL OUTER JOINProduction.ProductModel AS PMON PM.ProductModelID = P.ProductModelIDFULL OUTER JOINProduction.ProductSubcategory AS PSCON PSC.ProductSubcategoryID = P.ProductSubcategoryIDLEFT OUTER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryIDORDER BY PC.Name, PSC.Name

Usa per Join esterni

Perché outer unisce non solo le righe corrispondenti ma anche quelle che non lo fanno, sono un ottimo modo per trovare le voci mancanti nelle tabelle. Questo è grande quando è necessario fare la diagnosi sul database per determinare se ci sono problemi di integrità dei dati.

Ad esempio, supponiamo che fossimo preoccupati che potremmo avere alcune voci di ProductSubcategory che non corrispondono alle categorie., Potremmo testare eseguendo il seguente SQL

SELECT PSC.Name AS SubcategoryFROM Production.ProductCategory AS PSCLEFT OUTER JOIN Production.ProductSubcategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryIDWHERE PSC.ProductCategoryID is NULL

Il join esterno restituisce i valori di riga non corrispondenti come valori NULLI. La clausola where filtra i valori non null, lasciando solo i nomi delle sottocategorie non corrispondenti da esaminare.

I join esterni possono anche essere usati per porre domande come:

” Quali venditori non hanno mai fatto una vendita?”

” Quali prodotti non sono assegnati a un modello di prodotto?”

” Quali dipartimenti non hanno dipendenti assegnati?”

” Elenca tutti i territori di vendita non assegnati alle persone di vendita.”

---