Wprowadzenie do złączeń zewnętrznych
złączeń zewnętrznych
złączeń zewnętrznych używa się do dopasowania wierszy z dwóch tabel. Nawet jeśli nie ma dopasowanych wierszy są uwzględniane. Wiersze z jednej z tabel są zawsze uwzględniane, dla drugiej, gdy nie ma dopasowań, są uwzględniane wartości NULL.
seria rozpoczyna się od wprowadzenia artykułu do bazy danych. Wszystkie przykłady tej lekcji są oparte na Microsoft SQL Server Management Studio i Bazie Danych AdventureWorks2012. Możesz zacząć korzystać z tych bezpłatnych narzędzi za pomocą mojego przewodnika Pierwsze kroki za pomocą SQL Server., W tym artykule omówimy zewnętrzne połączenia.
typy złączeń zewnętrznych
istnieją trzy typy złączeń zewnętrznych:
- Left Outer Join – wszystkie wiersze z lewej tabeli są uwzględniane, niezrównane wiersze z prawej są zastępowane wartościami NULL.
- Right Outer Join – wszystkie wiersze z prawej tabeli są uwzględniane, niezrównane wiersze z lewej są zastępowane wartościami NULL.
- Full Outer Join – wszystkie wiersze z obu tabel są uwzględnione, wartości NULL wypełniają niezrównane wiersze.
Wykopmy głębiej i zbadajmy lewy zewnętrzny join.,
Left Outer Join
sprawdź poniższy model danych. Pochodzi z bazy danych AdventureWorks2012. W tym modelu jest od 1 osoby do 0 LUB 1 pracownika.
aby utworzyć listę wszystkich ostatnich nazw osób, a jednocześnie pokazać tytuł pracy jeśli dana osoba jest pracownikiem, potrzebujemy sposobu łączenia dwóch tabel i dołączania wierszy osób w wyniku, nawet jeśli nie pasują one do pracownika.
ten typ połączenia nazywa się połączeniem lewym zewnętrznym, ponieważ wszystkie wiersze tabeli z lewej strony słowa kluczowego JOIN są uwzględniane niezależnie od dopasowania., Podstawowa składnia lewego zewnętrznego połączenia to:
SELECT columnlistFROM tableLEFT OUTER JOIN othertable ON join condition
SQL dla połączenia na powyższym diagramie to:
SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
oto pierwsze wyniki zapytania
zauważ, że w drugim wierszu są wartości NULL dla nationalidnumber i jobtitle. Dzieje się tak dlatego, że nie ma pracowników pasujących do Biznesentitiid 293.
Right Outer Join
przyjrzyjmy się jeszcze raz schematowi, ale tym razem robimy right outer join., Jak można się domyślić, nie ma zbyt dużej różnicy w instrukcji SQL między lewym zewnętrznym złączem a prawym zewnętrznym złączem. Podstawowa składnia prawego zewnętrznego połączenia to:
SELECT columnlistFROM tableRIGHT OUTER JOIN othertable ON join condition
Poniżej znajduje się nasze przykładowe zapytanie napisane jako PRAWE ZEWNĘTRZNE połączenie:
SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonRIGHT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
kluczową różnicą jest to, że teraz będziemy zwracać wszystkie rekordy z tabeli pracowników, która jest tabelą po prawej stronie słowa kluczowego join. Jeśli pasujący rekord pracownika nie zostanie znaleziony, to dla Business Entityid i LastName zostanie zwrócone NULL.
oto wyniki zapytania.,
przewijałem wszystkie wyniki i byłem zaskoczony, że nie widziałem żadnych wartości null.
wiesz dlaczego?
odpowiedź leży w modelu danych. Jest 0..Stosunek 1 do 1 między pracownikiem a osobą. Oznacza to, że na każdego pracownika przypada jedna osoba. Biorąc to pod uwagę, dla właściwego połączenia nie będzie żadnych niepasujących wierszy. Z tego typu relacji można było również użyć wewnętrznego join.
lewe i prawe Złącza zewnętrzne
nie ma różnicy w funkcjonalności między lewym złączem zewnętrznym a prawym złączem zewnętrznym.,
Instrukcja
SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
zwraca ten sam wynik co
SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM HumanResources.EmployeeRIGHT OUTER JOIN person.Person ON person.BusinessEntityID = Employee.BusinessEntityID
oczywiście nie byłoby tak, gdybym zmienił tylko join z lewej Na prawą i nie zamienił nazw tabel.
zazwyczaj używam lewych złączy zewnętrznych więcej niż prawych złączy zewnętrznych. Myślę, że to dlatego, że kiedy rysuję związki robię to od lewej do prawej. Ponadto przemierzam stoły w mojej głowie od lewej do prawej.
to dobrze pasuje do SQL, ponieważ „lewa” tabela znajduje się w instrukcji FROM.
ciekawi mnie, czego używasz., Jestem naprawdę ciekaw, czy prawa strona wydaje ci się bardziej intuicyjna, jeśli jesteś native speakerem języka arabskiego lub innego języka „od prawej do lewej”.
pełne zewnętrzne połączenie
pełne zewnętrzne połączenie jest kombinacją wyników z lewego i prawego zewnętrznego połączenia. Wyniki zwracane z tego typu połączenia zawierają wszystkie wiersze z obu tabel. Tam, gdzie występują dopasowania, wartości są powiązane. Gdzie dopasowane z każdej tabeli nie, wtedy zwracane są NULL.,
podstawowa składnia pełnego połączenia zewnętrznego to:
SELECT columnlistFROM tableFULL OUTER JOIN othertable ON join condition
przyjrzyjmy się innej części bazy danych AdventureWork2012. Tym razem skupimy się na relacjach między SalesOrderHeader i CurrencyRate tabele.
model jest pokazany poniżej:
Załóżmy, że chcemy znać Wszystkie waluty, w których możemy składać zamówienia i które zamówienia zostały złożone w tych walutach?
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
oto część wyników pokazujących, gdzie niektóre sprzedaży mają dopasowanie do waluty, a niektóre, które nie., Powodem, dla którego są sprzedaży, które nie pasują, jest to, że są to sprzedaż w USD.
Dalej w dół w wynikach widać waluty bez dopasowania sprzedaży. Odzwierciedla to fakt, że nie dokonano sprzedaży w tych walutach.
Uwaga: byłem zaskoczony, widząc USD na liście, patrz wiersz 42463, ponieważ myślę, że większość sprzedaży będzie w tej walucie. Moim zdaniem zamiast szanować kurs waluty dla tych transakcji, SalesOrderHeader vale dla CurrencyRateID został ustawiony na null dla wszystkich transakcji USD., Myślę, że jest to niespójne i nie jest to sposób, w jaki bym to zrobił, ale nie jest to moja baza danych…
zaawansowany przykład
do tej pory przyjrzeliśmy się trzem rodzajom zewnętrznych połączeń, ale nie zbadaliśmy bardziej zaawansowanych pojęć, takich jak łączenie wielu tabel i używanie więcej niż jednego warunku w naszych klauzulach przyłączenia.
omówiliśmy te koncepcje, gdy badaliśmy połączenia wewnętrzne, więc to, co wam pokażę, nie powinno być zbyt nowe, ale myślę, że nadal ma sens, ponieważ w niektórych przypadkach mieszanie pełnych połączeń z połączeniami wewnętrznymi może przynieść nieoczekiwane lub niezamierzone rezultaty.,
skupmy się na schemacie produkcji i zbadajmy produkty i kategorie. Stwórzmy listę wszystkich kategorii produktów i modeli produktów zawartych w nich.
produkt ma związek jeden do wielu z ProductModel i ProductSubcategory. Ponieważ leży między tymi dwoma tabelami, istnieje ukryta relacja wiele do wielu między ProductModel i ProductSubcategory. Z tego powodu jest to dobry kandydat do łączenia zewnętrznego, ponieważ mogą istnieć modele produktów bez przypisanych produktów i wpisy ProductSubcategory bez produktu.,
aby przezwyciężyć tę sytuację, wykonamy zewnętrzne połączenie zarówno z tabelą ProductModel, jak i ProductCategory.
oto 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
jest kilka elementów do zapamiętania:
- użyłem aliasów tabel, aby uczynić SQL bardziej czytelnym.
- istnieje więcej niż jedna pełna klauzula złączenia zewnętrznego.,
- tabela ProductCategory jest również częścią zewnętrznego połączenia
pierwotnie, gdy pisałem SQL dla tego zapytania, miałem wewnętrzne połączenie między ProductSubcategory i ProductCategory, ale nie widziałem wartości NULL dla niezrównanych rekordów, których oczekiwałbym.
kiedy zmieniłem join na full outer join zobaczyłem oczekiwane efekty. Powód tego jest subtelny.
po sprawdzeniu danych potwierdziłem, że wszystkie kategorie mają przypisane podkategorie., Biorąc to pod uwagę, można by pomyśleć, że połączenie wewnętrzne będzie działać; jednak należy wziąć pod uwagę, że ponieważ cała instrukcja jest wykonywana i zwracane są wiersze, wartość ProductSubcategoryID jest NULL, gdy produkt nie pasuje do podkategorii produktu.
wartości Null z definicji nie są sobie równe, więc połączenie wewnętrzne nie powiedzie się. Biorąc to pod uwagę, gdy wartości te są dopasowane do ProductCategory, nie są one uwzględniane w wyniku, chyba że łączenie do ProductCategory jest łączeniem zewnętrznym.,
w rzeczywistości połączenie nie musi być pełnym zewnętrznym połączeniem, lewe połączenie działa równie dobrze:
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
wykorzystuje zewnętrzne połączenia
ponieważ zewnętrzne połączenia nie tylko pasują do wierszy, ale także te, które nie, są naprawdę dobrym sposobem na znalezienie brakujących wpisów w tabelach. Jest to świetne rozwiązanie, gdy trzeba przeprowadzić diagnozę w bazie danych, aby ustalić, czy występują problemy z integralnością danych.
na przykład załóżmy, że obawialiśmy się, że możemy mieć pewne wpisy ProductSubcategory, które nie pasują do kategorii., Możemy przetestować uruchamiając następujący 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
zewnętrzne połączenie zwraca niezrównane wartości wiersza jako wartości NULL. Klauzula where filtruje wartości inne niż null, pozostawiając nam do przejrzenia tylko niepasujące nazwy podkategorii.
można również zadawać pytania takie jak:
„Jakie osoby sprzedające nigdy nie dokonały sprzedaży?”
„Jakie produkty nie są przypisane do modelu produktu?”
„które działy nie mają przydzielonych pracowników?”
„Lista wszystkich terytoriów sprzedaży nie przypisanych sprzedawców.”