Inleiding tot buitenste Joins
buitenste Joins
buitenste joins worden gebruikt om rijen uit twee tabellen te matchen. Zelfs als er geen overeenkomende rijen zijn opgenomen. Rijen van een van de tabellen zijn altijd opgenomen, voor de andere, als er geen overeenkomsten zijn, NULL waarden zijn opgenomen.
De reeks begint met de artikel Inleiding tot Database Joins. Alle voorbeelden voor deze les zijn gebaseerd op Microsoft SQL Server Management Studio en de AdventureWorks2012 database. U kunt aan de slag met behulp van deze gratis tools met behulp van mijn gids aan de slag met behulp van SQL Server., In dit artikel gaan we outer joins behandelen.
typen buitenste Joins
Er zijn drie typen buitenste joins:
- linker buitenste Join-alle rijen uit de linker tabel zijn opgenomen, ongeëvenaarde rijen van rechts worden vervangen door nulwaarden.
- rechter buitenste Join – alle rijen uit de rechter tabel zijn opgenomen, ongeëvenaarde rijen van links worden vervangen door NULL-waarden.
- volledige buitenste Join-alle rijen van beide tabellen zijn inbegrepen, NULL-waarden vullen ongeëvenaarde rijen.
laten we dieper graven en de linker buitenste join verkennen.,
left Outer Join
Bekijk het volgende gegevensmodel. Dit is afkomstig uit de AdventureWorks2012 database. In dit model is er 1 persoon tot 0 of 1 werknemer.
om een lijst Te maken van alles Persoon LastNames, maar tonen ook Invoicestransamount als de Persoon een Werknemer is, moeten we een manier van deelnemen aan de twee tafels en zijn Persoon rijen in het resultaat, zelfs als ze niet overeenkomen Werknemer.
dit type join wordt een left outer join genoemd, omdat alle rijen voor de tabel aan de linkerkant van het trefwoord JOIN zijn opgenomen, ongeacht de overeenkomst., De basissyntaxis voor een linker buitenste join is:
SELECT columnlistFROM tableLEFT OUTER JOIN othertable ON join condition
De SQL voor de join in het bovenstaande diagram is:
SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
Hier zijn de eerste resultaten van de query
merk op hoe er zijn NULL-waarden in de tweede rij voor nationalidnumber en jobtitle. Dit komt omdat er geen werknemers zijn die overeenkomen met de bedrijfsidentiteit van 293.
rechter buitenste Join
laten we nog eens kijken naar het diagram, maar deze keer doen we een rechter buitenste join., Zoals je misschien al geraden hebt, is er niet al te veel verschil in de SQL statement tussen een linker buitenste join en een rechter buitenste join. De basissyntaxis voor een rechter buitenste join is:
SELECT columnlistFROM tableRIGHT OUTER JOIN othertable ON join condition
Hieronder is onze voorbeeldquery geschreven als een rechter buitenste 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
het belangrijkste verschil is dat we nu alle records uit de Employee table gaan retourneren, de tabel rechts van het trefwoord join. Als een bijpassende werknemer record niet wordt gevonden, dan NULL zal worden geretourneerd voor BusinessEntityID en LastName.
Hier zijn de resultaten van de query.,
ik bladerde door alle resultaten en was verbaasd geen null-waarden te zien.
weet u waarom?
het antwoord ligt in het datamodel. Er is een 0..1 op 1 relatie tussen werknemer en persoon. Dit betekent dat er voor elke medewerker één persoon is. Gegeven dit, voor de juiste join zullen er geen niet-overeenkomende rijen bestaan. Met dit soort relatie had je ook een inner join kunnen gebruiken.
Left versus Right Outer Joins
Er is geen verschil in functionaliteit tussen een left outer join en een right outer join.,
het statement
SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
geeft hetzelfde resultaat als
SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM HumanResources.EmployeeRIGHT OUTER JOIN person.Person ON person.BusinessEntityID = Employee.BusinessEntityID
Dit zou natuurlijk niet het geval zijn als ik alleen de join van links naar rechts had veranderd en de tabelnamen niet had verwisseld.
Ik gebruik meestal de linker buitenste joins meer dan de rechter buitenste joins. Ik denk dat dit komt omdat als Ik relaties teken ik dat van links naar rechts doe. Ook loop ik tafels door mijn hoofd van links naar rechts.
Dit past goed bij SQL aangezien de” left ” tabel in het FROM statement staat.
Ik ben benieuwd wat u gebruikt., Ik ben echt nieuwsgierig om te weten of een rechter joins lijken meer intuïtief voor u als u native speaker van het Arabisch of een andere “rechts naar links” taal.
volledige buitenste verbinding
een volledige buitenste verbinding is de combinatie van resultaten van een linker en rechter buitenste verbinding. De resultaten van dit type join bevatten alle rijen van beide tabellen. Waar overeenkomsten voorkomen, zijn waarden gerelateerd. Waar matched van een van beide tabel niet, dan NULL worden geretourneerd in plaats daarvan.,
de basissyntaxis voor een volledige outer join is:
SELECT columnlistFROM tableFULL OUTER JOIN othertable ON join condition
laten we eens kijken naar een ander deel van de AdventureWork2012 database. Deze keer richten we ons op de relaties tussen SalesOrderHeader en CurrencyRate tables.
het model wordt hieronder getoond:
stel dat we alle valuta ’s willen weten waarin we orders kunnen plaatsen en welke orders in die valuta’ s zijn geplaatst?
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
Hier is een deel van de resultaten die laten zien waar sommige verkopen overeenkomen met een valuta en andere niet., De reden dat er verkopen die niet overeenkomen is dat deze verkopen in USD.
verderop in de resultaten ziet u valuta ‘ s zonder overeenkomende verkopen. Dit weerspiegelt het feit dat er geen verkopen in deze valuta ‘ s hebben plaatsgevonden.
opmerking: Ik was verbaasd om USD vermeld te zien, zie rij 42463, omdat ik zou denken dat een meerderheid van de verkopen in deze valuta zou zijn. Mijn gedachte is dat in plaats van eerbied voor de wisselkoers voor deze transactie, de SalesOrderHeader vale voor CurrencyRateID werd ingesteld op null voor alle USD transacties., Ik denk dat dit inconsistent is, en is niet de manier waarop ik het zou doen, maar het is niet mijn database…
Geavanceerd voorbeeld
tot nu toe hebben we gekeken naar de drie soorten van buitenste joins, maar hebben niet onderzocht een aantal meer geavanceerde concepten zoals het samenvoegen van meerdere tabel en het gebruik van meer dan één voorwaarde in onze join clausules.
we bespraken deze concepten toen we inner joins verkenden, dus wat ik je zal laten zien, zou niet al te Nieuw moeten zijn, maar ik denk dat het nog steeds zinvol is om te herzien, omdat in sommige gevallen het mengen van volledige joins met inner joins onverwachte of onbedoelde resultaten kan opleveren.,
laten we onze focus richten op het productieschema en producten en categorieën verkennen. Laten we een lijst van alle productcategorieën en de productmodellen bevatten binnen.
Product heeft een één op vele relatie met ProductModel en Productsubcategorie. Aangezien het tussen deze twee tabellen ligt, bestaat er een impliciete relatie tussen Produktmodel en Produktsubcategorie. Vanwege dit, het is een goede kandidaat voor outer joins als er kan zijn productmodellen zonder toegewezen producten en Productsubcategorie inzendingen zonder product.,
om deze situatie te overwinnen zullen we een buitenste verbinding maken met zowel de productmodel als de productcategorie tabel.
Hier is de 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
Er zijn verschillende items op te merken:
- ik gebruikte tabelaliassen om de SQL leesbaarder te maken.
- Er is meer dan één volledige buitenste join-clausule.,
- de productcategorietabel maakt ook deel uit van een buitenste join
oorspronkelijk toen ik de SQL schreef voor deze query had ik een innerlijke join tussen Productsubcategorie en productcategorie, maar ik zag geen nulwaarden voor ongeëvenaarde records die ik zou verwachten.
zodra ik de join veranderde in een volledige buitenste join zag ik de resultaten die ik verwachtte. De reden dat dit gebeurt is subtiel.
na controle van de gegevens heb ik bevestigd dat aan alle categorieën subcategorieën zijn toegewezen., Gezien dit zou je denken dat een innerlijke join zou werken; echter, bedenk dat als de hele verklaring wordt uitgevoerd en rijen worden geretourneerd, de productsubcategoryid waarde is NULL wanneer een product niet overeenkomt met een product subcategorie.
Null-waarden zijn per definitie niet gelijk aan elkaar, dus de interne join faalt. Gegeven dit, wanneer deze waarden dan worden afgestemd op productcategorie zijn ze niet opgenomen in het resultaat, tenzij de verbinding met productcategorie een buitenste verbinding is.,
in feite hoeft de join geen volledige outer join te zijn, een Left join werkt net zo goed:
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
gebruikt voor Outer Joins
omdat outer joins niet alleen de overeenkomende rijen maar ook de rijen die dat niet doen, ze zijn echt een goede manier om ontbrekende items in tabellen te vinden. Dit is geweldig als je nodig hebt om de diagnose te doen op uw database om te bepalen of er gegevensintegriteit problemen.
stel bijvoorbeeld dat we bezorgd waren dat we bepaalde productsubcategorie items hebben die niet overeenkomen met Categorieën., We kunnen testen door de volgende 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
de buitenste join retourneert de ongeëvenaarde rijwaarden als nulwaarden. De where-clausule filtert op de niet-null-waarden, zodat we alleen subcategorie-namen kunnen bekijken.
externe joins kunnen ook worden gebruikt om vragen te stellen zoals:
“welke verkopers hebben nooit een verkoop gedaan?”
” welke producten zijn niet toegewezen aan een productmodel?”
” welke afdelingen hebben geen toegewezen werknemers?”
” som alle verkoopgebieden op die geen verkoopmedewerkers zijn toegewezen.”