Introduktion till yttre kopplingar
yttre kopplingar
yttre kopplingar används för att matcha rader från två tabeller. Även om det inte finns några matchrader ingår. Rader från en av tabellerna ingår alltid, för den andra, när det inte finns några matcher, ingår NULL-värden.
serien börjar med artikelintroduktionen till databasanslutningar. Alla exempel i den här lektionen är baserad på Microsoft SQL Server Management Studio och AdventureWorks2012 databas. Du kan komma igång med dessa gratis verktyg med hjälp av min Guide komma igång med SQL Server., I den här artikeln kommer vi att täcka yttre kopplingar.
typer av yttre kopplingar
det finns tre typer av yttre kopplingar:
- vänster yttre koppling – alla rader från vänster tabell ingår, oöverträffade rader från höger ersätts med nollvärden.
- höger yttre koppling – alla rader från höger tabell ingår, oöverträffade rader från vänster ersätts med nollvärden.
- Full yttre koppling – alla rader från båda tabellerna ingår, nollvärden fyller oöverträffade rader.
låt oss gräva en djupare och utforska vänster yttre koppling.,
vänster yttre koppling
kolla in följande datamodell. Detta tas från AdventureWorks2012 databas. I denna modell finns 1 Person till 0 eller 1 anställda.
för att skapa en lista över alla personnamn, men också visa JobTitle om personen är anställd, behöver vi ett sätt att gå med i de två tabellerna och inkludera personrader i resultatet, även om de inte matchar anställd.
den här typen av koppling kallas en vänster yttre koppling, eftersom alla rader för tabellen från vänster sida av KOPPLINGSORDET ingår oavsett matchen., Den grundläggande syntaxen för en vänster yttre koppling är:
SELECT columnlistFROM tableLEFT OUTER JOIN othertable ON join condition
SQL för anslutningen i diagrammet ovan är:
SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
Här är de första resultaten från frågan
Lägg märke till hur det finns NULL-värden listade i den andra raden för NationalIDNumber och jobtitle. Detta beror på att det inte finns några anställda som matchar BusinessEntityID 293.
höger yttre koppling
Låt oss ta en annan titt på diagrammet, men den här gången gör vi en rätt yttre koppling., Som du kanske har gissat är det inte för stor skillnad i SQL-satsen mellan en vänster yttre koppling och en höger yttre koppling. Den grundläggande syntaxen för en höger yttre koppling är:
SELECT columnlistFROM tableRIGHT OUTER JOIN othertable ON join condition
nedan är vår provfråga skriven som en höger yttre koppling:
SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonRIGHT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
nyckelskillnaden är att nu kommer vi att returnera alla poster från Personaltabellen, vilket är tabellen till höger om kopplingsordet. Om en matchande Anställd inte hittas, returneras NULL om BusinessEntityID och Efternamn.
här är resultaten från frågan.,
jag rullade igenom alla resultat och blev förvånad över att inte se några nollvärden.
vet du varför?
svaret ligger i datamodellen. Det finns en 0..1 till 1 förhållande mellan anställd och Person. Det betyder att för varje anställd finns en Person. Med tanke på detta, för rätt koppling finns det inte några icke-matchande rader. Med denna typ av relation kan du också ha använt en inre koppling.
vänster kontra höger yttre kopplingar
det finns ingen skillnad i funktionalitet mellan en vänster yttre koppling och en höger yttre koppling.,
uttalandet
SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
returnerar samma resultat som
SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM HumanResources.EmployeeRIGHT OUTER JOIN person.Person ON person.BusinessEntityID = Employee.BusinessEntityID
självklart skulle detta inte vara fallet om jag bara hade ändrat kopplingen från vänster till höger och inte bytt tabellnamnen.
Jag använder vanligtvis vänster yttre kopplingar mer än jag gör rätt yttre kopplingar. Jag tror att det beror på att när jag ritar relationer gör jag det från vänster till höger. Jag korsar också bord i mitt huvud från vänster till höger.
detta passar bra med SQL som ”vänster” tabellen finns i från-satsen.
Jag är nyfiken på vad du använder., Jag är verkligen nyfiken på att veta om en rätt förenar verkar mer intuitivt för dig om du är infödd talare av arabiska eller något annat ”höger till vänster” språk.
fullständig yttre koppling
en fullständig yttre koppling är kombinationen av resultat från en vänster och höger yttre koppling. Resultaten som returneras från denna typ av koppling inkluderar alla rader från båda tabellerna. Om matchningar inträffar är värden relaterade. Där matchas från endera tabellen inte, då NULL returneras istället.,
den grundläggande syntaxen för en fullständig yttre koppling är:
SELECT columnlistFROM tableFULL OUTER JOIN othertable ON join condition
Låt oss ta en titt på en annan del av AdventureWork2012-databasen. Den här gången kommer vi att fokusera på relationerna mellan SalesOrderHeader och CurrencyRate tabeller.
modellen visas nedan:
Antag att vi vill veta Alla valutor som vi kan beställa i och vilka beställningar som placerades i dessa valutor?
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
här är en del av resultaten som visar var viss försäljning har matchat till en valuta och några som inte har gjort det., Anledningen till att det finns försäljning som inte matchar är att dessa är försäljning i USD.
längre ner i resultaten ser du valutor utan matchande försäljning. Detta återspeglar det faktum att ingen försäljning gjordes i dessa valutor.
notera: Jag blev förvånad över att se USD listad, se rad 42463, eftersom jag skulle tro att en majoritet av försäljningen skulle vara i den här valutan. Min tanke är att i stället för att vördnad valutakursen för dessa transaktioner, var SalesOrderHeader vale for CurrencyRateID inställd på null för alla USD-transaktioner., Jag tycker att detta är inkonsekvent, och det är inte så jag skulle göra det, men det är inte min databas…
Avancerat exempel
hittills har vi tittat på de tre typerna av yttre kopplingar men har inte utforskat några mer avancerade begrepp som att gå med i flera bord och använda mer än ett villkor i våra kopplingsklausuler.
vi täckte dessa begrepp när vi utforskade inre kopplingar, så det jag ska visa dig, borde inte vara för nytt, men jag tror att det fortfarande är meningsfullt att granska, eftersom det i vissa fall kan ge oväntade eller oavsiktliga resultat att blanda fulla kopplingar med inre kopplingar.,
låt oss vända vårt fokus till produktionsschemat och utforska produkter och kategorier. Låt oss producera en lista över alla produktkategorier och produktmodellerna som ingår i.
produkten har en till många relation med ProductModel och ProductSubcategory. Eftersom det ligger mellan dessa två tabeller, Det finns en implicit många till många relation mellan ProductModel och ProductSubcategory. På grund av detta är det en bra kandidat för yttre fogar eftersom det kan finnas produktmodeller utan tilldelade produkter och Produkterkategoriposter utan produkt.,
för att övervinna denna situation kommer vi att göra en yttre koppling till både produktmodellen och Produktkategoritabellen.
här är 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 finns flera objekt att notera:
- jag använde tabellalias för att göra SQL mer läsbar.
- Det finns mer än en fullständig outer join-klausul.,
- tabellen ProductCategory är också en del av en yttre koppling
ursprungligen när jag skrev SQL för den här frågan Hade jag en inre koppling mellan ProductSubcategory och ProductCategory, men jag såg inte NULL-värden för oöverträffade poster jag skulle förvänta mig.
När jag ändrade anslutningen till en fullständig yttre koppling såg jag de resultat jag förväntade mig. Anledningen till att detta inträffar är subtil.
Efter att ha kontrollerat data bekräftade jag att alla kategorier är tilldelade underkategorier., Med tanke på detta skulle du tro att en inre koppling skulle fungera; men anser att eftersom hela uttalandet exekveras och rader returneras, är ProductSubcategoryID-värdet NULL när en produkt misslyckas med att matcha en produktunderkategori.
Null-värden är per definition inte lika med varandra, så den inre anslutningen misslyckas. Med tanke på detta, när dessa värden sedan matchas till ProductCategory ingår de inte i resultatet om inte anslutningen till ProductCategory är en yttre koppling.,
faktum är att kopplingen inte behöver vara en fullständig yttre koppling, en vänster koppling fungerar lika 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
använder för yttre kopplingar
eftersom yttre kopplingar inte bara de matchande raderna utan även de som inte gör det, är de ett riktigt bra sätt att hitta saknade poster i tabeller. Detta är bra när du behöver göra diagnos på din databas för att avgöra om det finns dataintegritetsproblem.
anta till exempel att vi var oroade över att vi kan ha vissa Produktkategoriposter som inte matchar kategorier., Vi kan testa genom att köra följande 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 yttre kopplingen returnerar de oöverträffade radvärdena som NULL-värden. Where-klausulen filtrerar på icke-null-värdena, vilket gör att endast icke-matchande Underkategorinamn för oss att granska.
yttre kopplingar kan också användas för att ställa frågor som:
”vilka säljare har aldrig gjort en försäljning?”
” vilka produkter har inte tilldelats en produktmodell?”
” vilka avdelningar har inga tilldelade anställda?”
” lista alla försäljningsområden som inte tilldelats säljare.”