Úvod do vnějších spojů
vnější spoje
vnější spoje se používají pro shodu řádků ze dvou tabulek. Dokonce i v případě, že není shoda řádky jsou zahrnuty. Řádky z jedné z tabulek jsou vždy zahrnuty, pro druhé, pokud nejsou žádné zápasy, jsou zahrnuty nulové hodnoty.
série začíná úvodem článku do databázových spojení. Všechny příklady této lekce jsou založeny na Microsoft SQL Server Management Studio A databázi AdventureWorks2012. Můžete začít používat tyto bezplatné nástroje pomocí mého průvodce Začínáme používat SQL Server., V tomto článku se budeme zabývat vnějšími spoji.
Druhy Vnější Spojení
k Dispozici jsou tři typy vnější spojení:
- Levé Vnější spojení – Všechny řádky z levé tabulky jsou zahrnuty, neporovnané řádky z pravé jsou nahrazeny hodnotami NULL.
- pravé vnější spojení – všechny řádky z pravé tabulky jsou zahrnuty, bezkonkurenční řádky zleva jsou nahrazeny nulovými hodnotami.
- plné vnější spojení – všechny řádky z obou tabulek jsou zahrnuty, nulové hodnoty vyplňují bezkonkurenční řádky.
pojďme kopat hlouběji a prozkoumat levé vnější spojení.,
levý vnější spoj
podívejte se na následující datový model. To je převzato z databáze AdventureWorks2012. V tomto modelu je 1 osoba 0 nebo 1 zaměstnanci.
vytvořit seznam všechny Osoby, Jména, ale také ukázat, JobTitle, pokud Osoba je Zaměstnanec, potřebujeme způsob spojování dvou tabulek a zahrnují Osobu řádků ve výsledku, i když se neshodují Zaměstnance.
Tento typ spojení se nazývá levý vnější spoj, protože všechny řádky tabulky z levé strany klíčového slova JOIN jsou zahrnuty bez ohledu na zápas., Základní syntaxe pro levé vnější spojení je:
SELECT columnlistFROM tableLEFT OUTER JOIN othertable ON join condition
SQL pro spojení v diagramu výše je:
SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
Zde jsou první výsledky z dotazu
Všimněte si, jak jsou hodnoty NULL, jsou uvedeny v druhém řádku pro NationalIDNumber a JobTitle. Je to proto, že neexistují zaměstnanci odpovídající BusinessEntityID 293.
pravý vnější spoj
podívejme se znovu na diagram, ale tentokrát děláme správné vnější spojení., Jak jste možná uhodli, v příkazu SQL není příliš velký rozdíl mezi levým vnějším spojením a pravým vnějším spojením. Základní syntaxe pro pravé vnější spojení je:
SELECT columnlistFROM tableRIGHT OUTER JOIN othertable ON join condition
Níže je náš ukázkový dotaz napsán jako pravé vnější spojení:
SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonRIGHT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
hlavní rozdíl je, že teď se budeme se vrátí všechny záznamy z tabulky Zaměstnanec, který je v tabulce na pravé straně připojit klíčové slovo. Pokud není nalezen odpovídající záznam zaměstnance, pak NULL bude vrácena pro BusinessEntityID a LastName.
zde jsou výsledky z dotazu.,
procházel jsem všechny výsledky a byl překvapen, že jsem neviděl žádné hodnoty null.
víte proč?
odpověď spočívá v datovém modelu. Je tu 0..1 až 1 vztah mezi zaměstnancem a osobou. To znamená, že pro každého zaměstnance je jedna osoba. Vzhledem k tomu, že pro správné připojení nebudou existovat žádné řádky, které by neodpovídaly. S tímto typem vztahu jste mohli také použít vnitřní spojení.
Vlevo oproti Pravé Vnější Spojení
není žádný rozdíl v funkčnosti mezi levé vnější spojení a pravé vnější spojení.,
prohlášení
SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
Vrátí stejný výsledek jako
SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM HumanResources.EmployeeRIGHT OUTER JOIN person.Person ON person.BusinessEntityID = Employee.BusinessEntityID
samozřejmě, že to nebude ten případ, když jsem měl jen změnil připojit zleva DOPRAVA a ne prohodil názvy tabulek.
obvykle používám levé vnější spoje více než pravé vnější spoje. Myslím, že je to proto, že když kreslím vztahy, dělám to zleva doprava. Taky, i procházet tabulky v mé hlavě zleva doprava.
toto dobře zapadá do SQL, protože tabulka“ vlevo “ je v příkazu FROM.
jsem zvědavý, co používáte., Jsem opravdu zvědavý, zda se vám zdá, že pravé spojení je intuitivnější, pokud jste rodilým mluvčím arabštiny nebo jiného jazyka „zprava doleva“.
plné vnější spojení
úplné vnější spojení je kombinací výsledků z levého a pravého vnějšího spojení. Výsledky vrácené z tohoto typu spojení zahrnují všechny řádky z obou tabulek. Tam, kde dochází k zápasům, jsou hodnoty příbuzné. Tam, kde uzavřeno z jedné tabulky nemají, pak NULL jsou vráceny místo.,
základní syntaxe pro úplné vnější spojení je:
SELECT columnlistFROM tableFULL OUTER JOIN othertable ON join condition
Pojďme se podívat na jinou část AdventureWork2012 databáze. Tentokrát se zaměříme na vztahy mezi prodejními a měnovými tabulkami.
model je uveden níže:
Předpokládejme, že chceme znát všechny měny můžeme umístit rozkazy a příkazy, které byly umístěny v těchto měnách?
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
zde je část výsledků ukazující, kde se některé prodeje shodovaly s měnou a některé, které ne., Důvodem jsou prodeje, které neodpovídají, je to, že se jedná o prodej v USD.
dále dolů ve výsledcích vidíte měny bez odpovídající prodeje. To odráží skutečnost, že v těchto měnách nebyly provedeny žádné prodeje.
Poznámka: byl jsem překvapen, vidět USD uvedeny, viz řádek 42463, protože myslím, že většina prodejů bude v této měně. Moje myšlenka je, že spíše než úcta kurz měny pro tyto transakce, SalesOrderHeader vale pro CurrencyRateID byl nastaven na null pro všechny transakce USD., Myslím, že to je v rozporu, a není způsob, jak bych to udělal, ale to není můj databázi…
Pokročilé Příklad
zatím jsme se podíval na tři typy vnější spojení, ale nebyly prozkoumány některé pokročilejší koncepty, jako jsou spojování více tabulky a používat více než jednu podmínku v naší klauzulí join.
Jsme zahrnuty tyto pojmy, když jsme zkoumali vnitřní spojení, takže to, co budu ukazovat vám, by neměla být příliš nové, ale myslím, že to pořád dává smysl, aby přezkoumala, protože v některých případech míchání plné spojení s vnitřní spojení může produkovat neočekávané nebo nezamýšlené výsledky.,
Zaměřme se na výrobní schéma a prozkoumejte produkty a kategorie. Pojďme vytvořit seznam všech kategorií produktů a modelů produktů obsažených v rámci.
produkt má jeden až mnoho vztah s ProductModel a ProductSubcategory. Vzhledem k tomu, že leží mezi těmito dvěma tabulkami, existuje implicitní mnoho pro mnoho vztahů mezi ProductModel a ProductSubcategory. Z tohoto důvodu je dobrým kandidátem na vnější spojení, protože mohou existovat modely produktů bez přiřazených produktů a Produktůubcategorie bez produktu.,
abychom tuto situaci překonali, uděláme vnější spojení s tabulkou ProductModel i ProductCategory.
Zde je 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
Existuje několik položek poznámka:
- používá tabulka aliasy, aby se SQL čitelnější.
- existuje více než jedna úplná klauzule o vnějším připojení.,
- ProductCategory tabulka je také součástí vnější spojení
Původně, když jsem napsal SQL pro tento dotaz jsem měl vnitřní spojení mezi ProductSubcategory a ProductCategory, ale nebyl jsem vidět hodnoty NULL za bezkonkurenční záznamů bych očekával.
jakmile jsem změnil spojení na úplné vnější spojení, viděl jsem výsledky, které jsem očekával. Důvod, proč k tomu dojde, je jemný.
po kontrole údajů jsem potvrdil, že všechny kategorie jsou přiřazeny podkategorie., Vzhledem k tomuto by si myslíte, že vnitřní spojení bude fungovat; nicméně, za to, že jako celé prohlášení je vykonán, a řádky, které jsou vráceny, ProductSubcategoryID hodnotu NULL vždy, když se výrobek nezdaří, aby odpovídala produktu podkategorie.
nulové hodnoty se podle definice navzájem nerovná, takže vnitřní spojení selže. Vzhledem k tomu, když jsou tyto hodnoty pak uzavřeno na ProductCategory nejsou zahrnuty do výsledku, pokud spojit do ProductCategory je vnější spojit.,
Ve skutečnosti, připojit nemusí být full outer join, left join funguje stejně dobře:
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
Využití pro Vnější Spojení
Protože vnější spojení není pouze odpovídající řádky, ale také ty, které ne, jsou opravdu dobrý způsob, jak najít chybějící položky v tabulkách. To je skvělé, když potřebujete provést diagnózu v databázi, abyste zjistili, zda existují problémy s integritou dat.
například jsme se obávali, že můžeme mít nějaké produkty, které neodpovídají kategoriím., Mohli bychom otestovat spuštěním následujícího 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
vnější spojení vrátí bezkonkurenční řádku hodnoty jako hodnoty NULL. Klauzule where filtruje hodnoty, které nejsou nulové, a ponechává nám pouze názvy podkategorií, které můžeme přezkoumat.
vnější spojení lze také použít k položení otázek, jako jsou:
„jaké prodejní osoby nikdy neprodaly?“
“ jaké produkty nejsou přiřazeny k modelu produktu?“
“ která oddělení nemají žádné přiřazené zaměstnance?“
“ uveďte všechna prodejní území, která nejsou přiřazena prodejním osobám.”