Introducere în Outer Joes
Outer Joes
outer Joes sunt utilizate pentru a se potrivi rânduri din două tabele. Chiar dacă nu există rânduri de potrivire sunt incluse. Rândurile de la unul dintre tabele sunt întotdeauna incluse, pentru celălalt, când nu există potriviri, sunt incluse valori nule.
seria începe cu articolul Introduction to Database Joins. Toate exemplele pentru această lecție se bazează pe Microsoft SQL Server Management Studio și baza de date AdventureWorks2012. Puteți începe să utilizați aceste instrumente gratuite folosind ghidul meu Noțiuni de bază folosind SQL Server., În acest articol vom acoperi exterior se alătură.
tipuri de îmbinări exterioare
există trei tipuri de îmbinări exterioare:
- îmbinare exterioară stângă – toate rândurile din tabelul din stânga sunt incluse, rândurile de neegalat din dreapta sunt înlocuite cu valori nule.
- right Outer Join-toate rândurile din tabelul din dreapta sunt incluse, rândurile de neegalat din stânga sunt înlocuite cu valori nule.
- full Outer Join-toate rândurile din ambele tabele sunt incluse, valorile nule umple rânduri de neegalat.
să săpăm mai adânc și să explorăm îmbinarea exterioară din stânga.,
Left Outer Join
consultați următorul model de date. Aceasta este preluată din Baza de date AdventureWorks2012. În acest model, există 1 persoană la 0 sau 1 angajați.
Pentru a construi o listă de toate Persoană LastNames, dar arată, de asemenea, Ibm dacă Persoana este un Angajat, avem nevoie de o modalitate de a se alătura cele două tabele și includ Persoană rânduri în rezultat, chiar dacă acestea nu se potrivesc Angajat.
Acest tip de join se numește un outer join stânga, ca toate rândurile pentru tabelul din partea stângă a cuvântului cheie JOIN sunt incluse indiferent de meci., În sintaxa de bază pentru o dreapta outer join este:
SELECT columnlistFROM tableLEFT OUTER JOIN othertable ON join condition
SQL pentru alăturați-vă în diagrama de mai sus este:
SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
Aici sunt primele rezultate de interogare
Observați cum există valori NULL enumerate în al doilea rând pentru NationalIDNumber și Ibm. Acest lucru se datorează faptului că nu există angajați care se potrivesc BusinessEntityID 293.
dreapta exterior se alăture
Să aruncăm o privire la diagrama, dar de data aceasta facem o dreapta exterior se alăture., După cum probabil ați ghicit, nu există prea multă diferență în instrucțiunea SQL între o îmbinare exterioară stângă și o îmbinare exterioară dreaptă. În sintaxa de bază pentru un outer join este:
SELECT columnlistFROM tableRIGHT OUTER JOIN othertable ON join condition
mai Jos este eșantionul nostru de interogare scris ca un drept exterior alătura:
SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonRIGHT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
diferența esențială este că acum suntem de gând să se întoarcă toate înregistrările din tabelul Salariat, care este tabelul din dreapta se alăture cuvinte cheie. Dacă nu se găsește o înregistrare a angajaților potrivită, atunci null va fi returnat pentru BusinessEntityID și LastName.
iată rezultatele din interogare.,
am derulat prin toate rezultatele și am fost surprins să nu văd nicio valoare nulă.
știți de ce?răspunsul se află în modelul de date. Există un 0..1 la 1 relația dintre angajat și persoană. Aceasta înseamnă că pentru fiecare angajat există o singură persoană. Având în vedere acest lucru, pentru alăturarea corectă nu vor exista rânduri care nu se potrivesc. Cu acest tip de relație ai fi putut folosi și o îmbinare interioară.
stânga versus dreapta se alătură exterioare
nu există nici o diferență în funcționalitate între un join exterior stânga și un join exterior dreapta.,
declarația
SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
Returnează același rezultat ca
SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM HumanResources.EmployeeRIGHT OUTER JOIN person.Person ON person.BusinessEntityID = Employee.BusinessEntityID
desigur, acest lucru nu ar fi cazul dacă am avut doar de schimbat se alăture de la STÂNGA la DREAPTA și nu a schimbat numele de masă.
de obicei folosesc îmbinări exterioare stânga mai mult decât fac îmbinări exterioare drepte. Cred că acest lucru se datorează faptului că atunci când desenez relații, fac acest lucru de la stânga la dreapta. De asemenea, traversez mese în capul meu de la stânga la dreapta.
Acest lucru se potrivește bine cu SQL, deoarece tabelul „stânga” se află în instrucțiunea FROM.
sunt curios să știu ce folosiți., Sunt foarte curios sa stiu daca un drept se alatura par mai intuitive pentru tine daca esti vorbitor nativ de araba sau o alta limba „de la dreapta la stanga”.o îmbinare exterioară completă este combinația de rezultate dintr-o îmbinare exterioară stângă și dreaptă. Rezultatele returnate de la acest tip de join includ toate rândurile din ambele tabele. În cazul în care apar meciuri, valorile sunt legate. În cazul în care potrivite din oricare tabel nu, atunci NULL sunt returnate în schimb.,
sintaxa de bază pentru un full outer join este:
SELECT columnlistFROM tableFULL OUTER JOIN othertable ON join condition
Să aruncăm o privire la o altă parte a AdventureWork2012 baza de date. De data aceasta, ne vom concentra pe relațiile dintre tabelele SalesOrderHeader și CurrencyRate.
modelul este prezentat mai jos:
să Presupunem că vrem să știm toate monedele putem plasa comenzi și în ce ordine au fost plasate în acele 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
aici este o parte din rezultatele care arată în cazul în care unele vânzări au meci la o monedă și unele care nu au., Motivul pentru care există vânzări care nu se potrivesc este că acestea sunt vânzări în USD.
mai jos în rezultatele pe care le vedeți valute fără vânzări potrivite. Aceasta reflectă faptul că nu s-au efectuat vânzări în aceste monede.
Notă: am fost surprins să văd USD enumerate, vezi rând 42463, deoarece cred că o majoritate de vânzări va fi în această valută. Gândul meu este că, mai degrabă decât să respect cursul valutar pentru aceste tranzacții, SalesOrderHeader vale pentru CurrencyRateID a fost setat la nul pentru toate tranzacțiile în USD., Cred că acest lucru este în contradicție, și nu așa cum ar face-o, dar nu e baza mea de date…
Advanced Exemplu
până acum ne-am uitat la cele trei tipuri de exterior se alătură dar nu l-am explorat unele concepte mai avansate, cum ar fi aderarea la masă multiple și folosind mai mult de o condiție nostru se alăture clauze.
Ne-am acoperit aceste concepte atunci când am explorat interior se alătură, astfel încât ceea ce voi fi vă arată, n-ar fi prea nou, dar cred că încă mai are sens pentru a revizui, deoarece în unele cazuri amestecarea completă se alătură cu interior se pot produce neașteptate sau nedorite rezultate.,să ne îndreptăm atenția spre schema de producție și să explorăm produsele și categoriile. Să producem o listă a tuturor categoriilor de produse și a modelelor de produse conținute în acestea.
produsul are o relație de unu la mulți cu ProductModel și ProductSubcategory. Deoarece se află între aceste două tabele, există o relație implicită între multe și multe între ProductModel și ProductSubcategory. Din acest motiv, este un bun candidat pentru exterior se alătură ca nu poate fi modele de produse fără produse alocate și intrările ProductSubcategory cu nici un produs.,
Pentru a depăși această situație vom face un exterior alătura atât ProductModel și ProductCategory masă.
aici este 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
există mai multe elemente de remarcat:
- am folosit alias-uri de masă pentru a face SQL mai ușor de citit.
- există mai mult de o clauză completă de îmbinare exterioară.,
- ProductCategory masă este, de asemenea, parte dintr-un exterior se alăture
Inițial când am scris SQL pentru interogarea am avut un inner join între ProductSubcategory și ProductCategory, dar nu mă văd cu valori NULL pentru recorduri de neegalat mi-ar aștepta.odată ce am schimbat join-ul într-un join complet exterior, am văzut rezultatele pe care le așteptam. Motivul pentru care se întâmplă acest lucru este subtil.
după verificarea datelor am confirmat că toate categoriile sunt atribuite subcategorii., Dat fiind acest lucru ar trebui să cred un inner join ar lucra; cu toate acestea, consideră că, întrucât întreaga declarație este executat si randurile sunt returnate, ProductSubcategoryID valoare este NULĂ ori de câte ori un produs nu reușește să se potrivi un produs subcategorie.
Valorile nule, prin definiție, nu sunt egale între ele, astfel încât îmbinarea interioară eșuează. Având în vedere acest lucru, atunci când aceste valori sunt apoi potrivite cu ProductCategory, acestea nu sunt incluse în rezultat decât dacă join to ProductCategory este un join exterior.,
de fapt, se alăture nu trebuie să fie un full outer join, left join funcționează la fel de bine:
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
Foloseste pentru Exterior se Alătură
Pentru exterior se alătură nu numai rânduri de potrivire, dar, de asemenea, cei care nu, ele sunt o modalitate foarte bună de a găsi lipsă intrări în tabele. Acest lucru este excelent atunci când trebuie să faceți diagnostic în baza de date pentru a determina dacă există probleme de integritate a datelor.
de exemplu, să presupunem că am fost preocupat de faptul că am putea avea unele intrări ProductSubcategory care nu se potrivesc cu categorii., Am putea testa prin rularea următoarele 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
outer join returnează valorile rând de neegalat ca valori nule. Clauza where filtrează valorile non-nule, lăsând doar nume de subcategorii care nu se potrivesc pentru a fi revizuite.îmbinările exterioare pot fi, de asemenea, folosite pentru a pune întrebări precum:
„Ce vânzări nu au făcut niciodată o vânzare?”
„ce produse nu sunt atribuite unui model de produs?”
„ce departamente nu au angajați desemnați?”
” listați toate teritoriile de vânzări care nu sunt alocate oamenilor de vânzări.”