Introduksjon til Ytre Sammenføyninger

Ytre Sammenføyninger

Ytre sammenføyninger er brukt til å matche rader fra to tabeller. Selv om det ikke samsvarer med rader er inkludert. Rader fra en av tabellene er alltid inkludert i prisen, for det andre, når det ikke er noen treff, NULL-verdier er inkludert.

serien starter med artikkelen Innføring i Databasen Blir. Alle eksemplene for denne leksjonen er basert på Microsoft SQL Server Management Studio og AdventureWorks2012 database. Du kan komme i gang med disse gratis verktøyene ved hjelp av min Veileder Komme i Gang ved Hjelp av SQL Server., I denne artikkelen skal vi dekke ytre sammenføyninger.

Typer Ytre Sammenføyninger

Det er tre typer av ytre sammenføyninger:

• Venstre Ytre Sammenføyning – Alle rader fra venstre bord er inkludert, enestående rader fra høyre er erstattet med NULL-verdier.
• Høyre Ytre Sammenføyning – Alle rader fra den høyre tabellen er inkludert, enestående rad fra venstre er erstattet med NULL-verdier.
• Full Outer Join – Alle rader fra begge tabellene er inkludert, NULL-verdier fylle uovertruffen rader.

La oss grave dypere og utforske venstre ytre sammenføyning.,

Venstre Ytre Sammenføyning

Sjekk ut følgende data modell. Dette er hentet fra AdventureWorks2012 database. I denne modellen er det 1 Person til 0 eller 1 Ansatte.

for Å lage en liste over alle Person LastNames, men også vise JobTitle hvis Personen er Ansatt, trenger vi en måte å bli de to tabellene og inkluderer Person rader i resultatet, selv om de ikke stemmer Ansatt.

Denne typen av delta er kalt en venstre ytre sammenføyning, som alle radene for bordet fra venstre side av DELTA søkeord er inkludert, uavhengig av kampen., Den grunnleggende syntaks for en venstre ytre sammenføyning er:

SELECT columnlistFROM tableLEFT OUTER JOIN othertable ON join condition

SQL for å bli med i diagrammet over er:

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

Her er de første resultatene fra søket

legg Merke til hvordan det er NULL-verdier som er oppført i den andre raden for NationalIDNumber og JobTitle. Dette er fordi det er ingen ansatte i matchende BusinessEntityID 293.

Høyre Ytre Sammenføyning

La oss ta en titt på diagrammet, men denne gangen gjør vi en høyre ytre sammenføyning., Som du kanskje har gjettet, det er ikke så mye forskjell i SQL-setningen mellom en venstre ytre sammenføyning og en høyre ytre sammenføyning. Den grunnleggende syntaks for en høyre ytre sammenføyning er:

SELECT columnlistFROM tableRIGHT OUTER JOIN othertable ON join condition

Nedenfor er vårt eksempel på spørring skrevet som en høyre ytre sammenføyning:

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

Den største forskjellen er at vi nå kommer til å returnere alle poster fra Ansatt-tabellen, som er den tabellen til høyre for bli med søkeord. Hvis en samsvarende Ansatt-posten er ikke funnet, så NULL vil bli returnert for BusinessEntityID og Etternavn.

Her er resultatene fra spørringen.,

jeg bladd gjennom alle resultatene, og ble overrasket over å ikke se noen null-verdier.

vil du vite hvorfor?

svaret ligger i datamodellen. Det er en 0..1-til-1-forhold mellom de Ansatte og Person. Dette betyr at for hver Ansatt, er det én Person. Gitt dette, for høyre delta det ikke finnes noen ikke-samsvarende rader. Med denne typen forhold du kunne også ha brukt en indre sammenføyning.

Venstre mot Høyre Ytre Sammenføyninger

Det er ingen forskjell i funksjonalitet mellom en venstre ytre sammenføyning og en høyre ytre sammenføyning.,

uttalelsen

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

gir samme resultat som

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

selvfølgelig dette ville ikke være tilfelle hvis jeg bare hadde endret delta fra VENSTRE til HØYRE og ikke slått tabellen navn.

jeg bruker vanligvis venstre ytre sammenføyninger mer enn det jeg gjør akkurat ytre sammenføyninger. Jeg tror dette er fordi når jeg trekker relasjoner jeg gjøre så mot venstre til høyre. Også, jeg traverse bord i mitt hode fra venstre til høyre.

Dette passer godt med SQL som «venstre» – bordet er i FRA erklæringen.

jeg er nysgjerrig på å vite hva du bruker., Jeg er veldig nysgjerrig på å vite om en høyre slutter seg til å virke mer intuitivt for deg hvis du er innfødt av arabisk eller noen andre «høyre til venstre» språk.

Full Outer Join

En full ytre sammenføyning er det en kombinasjon av resultater fra en høyre og en venstre ytre sammenføyning. Resultatene som returneres fra denne type delta inkluderer alle rader fra begge tabellene. Der samsvarer med skje, verdiene er i slekt. Hvor matchet enten fra bordet ikke, så NULL returneres i stedet.,

Den grunnleggende syntaks for en full ytre sammenføyning er:

SELECT columnlistFROM tableFULL OUTER JOIN othertable ON join condition

La oss ta en titt på en annen del av AdventureWork2012 database. Denne gangen er vi kommer til å fokusere på forholdet mellom SalesOrderHeader og CurrencyRate bord.

modellen er vist nedenfor:

la oss Anta at vi ønsker å vite alle valutaer kan vi plassere ordrer og bestillinger som ble plassert i de ulike valutaene?

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

Her er en del av de resultater som viser hvor noen salget har match til en valuta, og noen som ikke har., Grunnen til det er salg som ikke stemmer er at disse er salg i USD.

Ytterligere ned i resultatene du se valutaer med ingen matchende salg. Dette gjenspeiler det faktum at ikke noe salg ble gjort i disse valutaene.

Merk: jeg var overrasket over å se USD oppført, kan du se rad 42463, siden jeg tror et flertall av salg ville være i denne valutaen. Min tanke er at heller enn å ha ærbødighet for den valuta pris for disse transaksjonen, SalesOrderHeader vale for CurrencyRateID ble satt til null for alle USD transaksjoner., Jeg tror dette er inkonsekvent, og det er ikke slik jeg ville gjøre det, men det er ikke min database…

Avansert Eksempel på

Så langt vi har sett på tre typer av ytre sammenføyninger, men har ikke utforsket litt mer avanserte konsepter for eksempel bli flere bord og bruke mer enn én tilstand i våre bli med klausuler.

Vi dekket disse begrepene når vi utforsket indre sammenføyninger, så hva jeg skal vise deg, bør ikke være for nye, men jeg tror det fortsatt fornuftig å vurdere, da det i noen tilfeller blande full møter med indre sammenføyninger kan gi uventede eller utilsiktede resultater.,

La oss snu vårt fokus å produsere skjema og utforske produkter og kategorier. La oss lage en liste over alle kategorier og produktet modeller som finnes innenfor.

Produktet har en én-til-mange-relasjon med ProductModel og ProductSubcategory. Siden det ligger mellom disse to bord, det er en implisitt mange-til-mange-relasjon mellom ProductModel og ProductSubcategory. På grunn av dette, det er en god kandidat for ytre sammenføyninger som det er kan være produkt-modeller med ingen tilordnet produkter og ProductSubcategory oppføringer med noe annet produkt.,

for Å overvinne denne situasjonen vil vi gjøre en ytre bli med til både ProductModel og ProductCategory bordet.

Her er 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

Det er flere ting å merke seg:

• jeg brukte tabell aliaser for å lage SQL mer lesbar.
• Det er mer enn én full outer join-setningsdel.,
• ProductCategory tabellen er også en del av en ytre bli med

Opprinnelig da jeg skrev SQL for dette søket jeg hadde en indre sammenføyning mellom ProductSubcategory og ProductCategory, men jeg var ikke ser NULL-verdier for uovertruffen poster jeg ville forvente.

Når jeg skiftet bli med til en full ytre sammenføyning jeg så resultatene jeg forventet. Grunnen til at dette skjer er subtile.

Etter å ha sjekket data jeg bekreftet at alle kategoriene som er tilordnet underkategorier., Gitt dette, ville du tror en indre delta ville fungere, men anser det som hele setningen er utført og rader som returneres, den ProductSubcategoryID verdien er NULL når et produkt ikke klarer å matche et produkt underkategori.

Null-verdier, per definisjon, er ikke lik en annen, slik at den indre delta mislykkes. Gitt dette, når disse verdiene er så matchet til ProductCategory de ikke inkludert i resultatet, med mindre de bli med å ProductCategory er en ytre sammenføyning.,

faktisk, delta trenger ikke å være en full outer join, left join fungerer like bra:

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

Bruker for Ytre Sammenføyninger

Fordi ytre sammenføyninger ikke bare samsvarende rader, men også for dem som ikke har det, de er en veldig god måte å finne manglende registreringer i tabeller. Dette er flott når du trenger å gjøre diagnosen på databasen for å finne ut om det er data integritet problemer.

For eksempel anta at vi var bekymret for at vi kan ha noen ProductSubcategory bidrag som ikke passer til Kategorier., Vi kan teste ved å kjøre følgende 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

Den ytre sammenføyning returnerer den enestående rad verdier som NULL-verdier. Where-betingelsen filtre på ikke-null-verdier, slik at bare nonmatching Underkategori navn for oss å kontrollere.

Ytre sammenføyninger kan også brukes til å stille spørsmål som:

«Hva salgs-mennesker som aldri har gjort et salg?»

«Hvilke produkter som ikke er tilordnet et produkt modell?»

«avdelinger Som ikke har noen som er tilordnet ansatte?»

«Liste alle salg territorier som ikke er tilknyttet salget folk.”

---