Introduktion til ydre sammenføjninger

ydre sammenføjninger

ydre sammenføjninger bruges til at matche rækker fra to tabeller. Selv om der ikke er nogen match rækker er inkluderet. Rækker fra en af tabellerne er altid inkluderet, for den anden, når der ikke er nogen kampe, er NULL-værdier inkluderet.

serien starter med artiklen Introduktion til Database slutter. Alle eksempler på denne lektion er baseret på Microsoft s .l Server Management Studio og Adventure .orks2012 database. Du kan komme i gang med at bruge disse gratis værktøjer ved hjælp af min Guide Kom godt i gang ved hjælp af s .l Server., I denne artikel vil vi dække ydre sammenføjninger.

typer af ydre sammenføjninger

Der er tre typer af ydre sammenføjninger:

• venstre ydre sammenføjning – alle rækker fra venstre tabel er inkluderet, uovertrufne rækker fra højre erstattes med NULL-værdier.
• højre ydre sammenføjning-alle rækker fra højre tabel er inkluderet, uovertruffen rækker fra venstre erstattes med NULL-værdier.
• fuld ydre sammenføjning-alle rækker fra begge tabeller er inkluderet, NULL-værdier fylder uovertruffen rækker.

lad os grave en dybere og udforske den venstre ydre sammenføjning.,

venstre ydre sammenføjning

tjek følgende datamodel. Dette er taget fra Adventure .orks2012-databasen. I denne model er der 1 Person til 0 eller 1 ansatte.

for at konstruere en liste over alle personens efternavn, men også vise JobTitle hvis personen er en medarbejder, har vi brug for en måde at slutte sig til de to tabeller og inkludere Personrækker i resultatet, selvom de ikke matcher medarbejder.

denne type join kaldes en venstre ydre join, da alle rækker til tabellen fra venstre side af JOIN-nøgleordet er inkluderet uanset kampen., Den grundlæggende syntaks for en venstre ydre join er:

SELECT columnlistFROM tableLEFT OUTER JOIN othertable ON join condition

SQL til den deltag i diagrammet ovenfor, 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 resultater fra forespørgslen

Bemærk, hvordan der er NULL-værdier, der er anført i anden række for NationalIDNumber og JobTitle. Dette skyldes, at der ikke er nogen medarbejdere, der matcher BusinessEntityID 293.

højre ydre sammenføjning

lad os tage et andet kig på diagrammet, men denne gang laver vi en højre ydre sammenføjning., Som du måske har gættet, er der ikke for meget forskel i S .l-sætningen mellem en venstre ydre sammenføjning og en højre ydre sammenføjning. Den grundlæggende syntaks for en højre ydre join er:

SELECT columnlistFROM tableRIGHT OUTER JOIN othertable ON join condition

Nedenfor er vores eksempel på forespørgsel skrevet som en højre ydre join:

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 vigtigste forskel er, at nu skal vi til at returnere alle poster fra Medarbejder-tabellen, som er tabellen til højre på join søgeord. Hvis der ikke findes en matchende Medarbejderpost, returneres NULL for BusinessEntityID og LastName.

Her er resultaterne fra forespørgslen.,

Jeg rullede gennem alle resultaterne og blev overrasket over ikke at se nogen null-værdier.

Ved du hvorfor?

svaret ligger i datamodellen. Der er en 0..1 til 1 forholdet mellem medarbejder og Person. Det betyder, at for hver medarbejder er der en person. I betragtning af dette, for højre join der vil ikke eksistere nogen ikke-matchende rækker. Med denne type forhold kunne du også have brugt en indre sammenføjning.

venstre versus højre ydre sammenføjning

Der er ingen forskel i funktionalitet mellem en venstre ydre sammenføjning og en højre ydre sammenføjning.,

erklæringen

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

Returnerer det samme resultat som

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

selvfølgelig er dette ikke ville være tilfældet, hvis jeg havde kun ændret deltage fra VENSTRE mod HØJRE, og ikke skiftede tabel navne.

Jeg bruger typisk venstre ydre sammenføjninger mere end jeg gør højre ydre sammenføjninger. Jeg tror, det skyldes, at når jeg tegner forhold, gør jeg det fra venstre mod højre. Jeg krydser også borde i mit hoved fra venstre mod højre.

dette passer godt sammen med S .l, da tabellen “venstre” er i fra-erklæringen.

Jeg er nysgerrig efter at vide, hvad du bruger., Jeg er virkelig nysgerrig efter at vide, om en ret sammenføjning synes mere intuitiv for dig, hvis du er indfødt på arabisk eller et andet “højre til venstre” sprog.

fuld ydre sammenføjning

en fuld ydre sammenføjning er kombinationen af resultater fra en venstre og højre ydre sammenføjning. Resultaterne, der returneres fra denne type sammenføjning, inkluderer alle rækker fra begge tabeller. Hvor kampe forekommer, er værdier relateret. Hvor matchet fra begge bord ikke, returneres NULL i stedet.,

den grundlæggende syntaks for en fuld ydre sammenføjning er:

SELECT columnlistFROM tableFULL OUTER JOIN othertable ON join condition

lad os se på en anden del af Adventure .ork2012-databasen. Denne gang vil vi fokusere på forholdet mellem SalesOrderHeader og CurrencyRate tabeller.

modellen er vist nedenfor:

Antag, at vi vil vide alle de valutaer, Vi kan placere ordrer i, og hvilke ordrer der blev placeret i disse valutaer?

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 af resultaterne, der viser, hvor nogle salg har match til en valuta og nogle, der ikke har., Årsagen til, at der er salg, der ikke stemmer overens, er, at dette er salg i USD.

længere nede i resultaterne ser du valutaer uden matchende salg. Dette afspejler, at der ikke blev solgt i disse valutaer.

Bemærk: Jeg var overrasket over at se USD opført, se række 42463, da jeg ville tro, at et flertal af salget ville være i denne valuta. Min tanke er, at snarere end ærbødighed valutakursen for denne transaktion, blev SalesOrderHeader vale for CurrencyRateID sat til nul for alle USD-transaktioner., Jeg tror, dette er inkonsekvent, og det er ikke den måde, jeg ville gøre det på, men det er ikke min database…

avanceret eksempel

indtil videre har vi set på de tre typer ydre sammenføjninger, men har ikke udforsket nogle mere avancerede koncepter som f.eks.

Vi dækkede disse begreber, da vi udforskede indre sammenføjninger, så det, jeg viser dig, burde ikke være for nyt, men jeg synes, det stadig giver mening at gennemgå, da det i nogle tilfælde kan blande fulde sammenføjninger med indre sammenføjninger give uventede eller utilsigtede resultater.,

lad os vende vores fokus til produktionsskemaet og udforske produkter og kategorier. Lad os producere en liste over alle produktkategorier og produktmodellerne indeholdt i.

produkt har en en til mange forhold til ProductModel og Productssubcategory. Da det ligger mellem disse to tabeller, er der en implicit mange til mange forhold mellem produktmodel og Produktsubkategori. På grund af dette er det en god kandidat til ydre sammenføjninger, da der kan være produktmodeller uden tildelte produkter og produktkategori poster uden produkt.,

for At overvinde denne situation, vil vi gøre en ydre join, at både ProductModel og ProductCategory bordet.

Her er S .l

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

Der er flere elementer, der skal bemærkes:

• jeg brugte tabelaliaser for at gøre S .l mere læsbar.
• Der er mere end en fuld ydre sammenføjningsklausul.,
• ProductCategory tabellen er også en del af en ydre join

da jeg Oprindeligt skrev SQL for denne forespørgsel, at jeg havde en indre join mellem ProductSubcategory og ProductCategory, men jeg var ikke at se NULL-værdier for uovertruffen registreringer, som jeg ville forvente.

Når jeg ændrede sammenføjningen til en fuld ydre sammenføjning, så jeg de resultater, jeg forventede. Årsagen til, at dette sker, er subtil.

efter kontrol af dataene bekræftede jeg, at alle kategorier er tildelt underkategorier., I betragtning af dette ville du tro, at en indre join ville fungere; men overvej at når hele sætningen udføres og rækker returneres, er ProductSubcategoryID-værdien NULL, når et produkt ikke svarer til en produktkategori.definition ikke er lig med hinanden, så den indre sammenføjning mislykkes. I betragtning af dette, når disse værdier derefter matches til produktkategori, er de ikke inkluderet i resultatet, medmindre sammenføjningen til Produktkategorien er en ydre sammenføjning.,

faktisk, slutte behøver ikke at være en full outer join, en left join fungerer lige så godt:

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

Anvendelse for Ydre Joins

Fordi det ydre joins ikke kun de matchende rækker, men også dem, der ikke, de er en rigtig god måde at finde de manglende poster i tabeller. Dette er fantastisk, når du skal gøre diagnose på din database for at afgøre, om der er dataintegritet spørgsmål.Antag for eksempel, at vi var bekymrede for, at vi muligvis har nogle Produktsubcategory-poster, der ikke stemmer overens med kategorier., Vi kunne teste ved at køre følgende s .l

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 ydre sammenføjning returnerer de umatchede rækkeværdier som nulværdier. Clausehere-klausulen filtrerer på ikke-null-værdierne og efterlader kun ikke-matchende Underkategorienavne, som vi kan gennemgå.

ydre sammenføjninger kan også bruges til at stille spørgsmål som:

“hvilke sælgere har aldrig foretaget et salg?”

” hvilke produkter er ikke tildelt en produktmodel?”

” hvilke afdelinger har ikke nogen tildelte medarbejdere?”

” Angiv alle salgsområder, der ikke er tildelt salgsfolk.”

---