Външни асоциации

Докато вътрешните съединения съдържат само пресичането на два набора от данни, външните съединения разширяват този набор от несъответстващите данни на левия или десния набор от данни (Фигура 9.6).

решават значителен проблем за много заявки, включително данни в резултата, независимо от тяхното съвпадение. Примерът за поръчка на клиент по-горе илюстрира този проблем доста добре. Ако трябва да изброим всички клиенти заедно с най-новите им поръчки, вътрешното обединяване ще филтрира клиенти, направили покупки преди началната дата на диапазона. Този тип грешка е доста често срещана в приложенията за бази данни.

Фигура: 9.6. Външно съединение включва не само съвпадащи редове от два източника на данни, но и несъвпадащи редове извън пресечната точка на множества

Някои от редовете в набора от резултати, образуван от външното съединение, ще изглеждат точно като редовете от вътрешното съединение - те ще съдържат колони от всеки източник. В същото време някои редове, които не са намерили съвпадение на противоположната страна на съединението, ще съдържат само колоните от външната таблица - колоните от противоположната страна ще съдържат празни стойности.

Когато създавате заявки в конструктора, можете да промените типа на заявката от вътрешен, приет по подразбиране, към външен по всяко време. Това може да се направи с помощта на контекстното меню, както и прозореца със свойства на обединението (фиг. 9.7). Query Builder върши чудесна работа, като демонстрира типа на обединението, като използва специални символи за това.

В SQL кода външното присъединяване се декларира с ключовите думи отляво външно или дясно външно преди ключовата дума join (технически външната ключова дума не е задължителна).

НАЛЯВО | НАДЯСНО [ВЪНШНО] ПРИСЪЕДИНЯВАНЕ Таблица2 ВКЛ. Таблица.колона = Таблица2.колона

Въпреки че някои думи в SQL не са задължителни (като INNER или OUTER) или могат да бъдат съкратени (като proc за процедура), спазването на пълния синтаксис увеличава четливостта на вашия код. В същото време много разработчици пропускат незадължителния синтаксис.

Puc. 9.7. Прозорецът на свойствата за присъединяване показва колоните, участващи в него. Тук можете да промените условието за присъединяване (=,>, count Добавяне на звездичка отдясно на знака за равенство в условието на клаузата

2005 г. където. Въпреки че този синтаксис работи в SQL Server 2000,

вече не се поддържа в SQL Server 2005. Вътрешните съединения на ANSI SQL-89 продължават да работят, но външните съединения изискват синтаксис на ANSI SQL-92.

и незадължителни външни ключове

често се използва, когато втората таблица има ограничения за външен ключ, засягащи първата таблица и позволява нулеви стойности в колоната за външен ключ. Наличието на този незадължителен външен ключ предполага, че ако вторичният ред се отнася до основния ред, тогава първичният трябва да съществува. В същото време е допустимо вторичният ред да се въздържа от достъп до основната таблица.

Друг пример за използване на незадължителен външен ключ е в колона с приоритет или предупреждение. В този случай обаче той трябва да сочи към валиден ред в таблицата с приоритети.

Примерната база данни OBXKites използва подобна схема за приоритет на поръчките, така че отчитането на всички поръчки с техните приоритети изисква външно присъединяване.

ИЗБЕРЕТЕ OrderNumber, OrderPriorityName ОТ dbo. [Поръчка]

ЛЯВО ВЪНШНО ПРИСЪЕДИНЯВАНЕ dbo.OrderPriority ON [Поръчка] .OrderPrioritylD =

Това ляво външно присъединяване извлича всички поръчки и приоритети, които им съответстват (ако има такива). OBXKites_Populate скрипт. sql задава две поръчки с приоритет Rush (незабавно).

Връщащите се връзки (наричани още рекурсивни и естествени) използват незадължителни външни ключове. В базата данни за семейно счетоводство външните ключове са полетата Mot he ID и Father ID, свързани с полето за идентификация на майка и баща на Regson. Този незадължителен външен ключ позволява да се въвежда информация за хората, дори ако информацията на техния родител все още не е въведена в базата данни. В същото време, ако полетата MotherlD и FatherlD са попълнени, те трябва да посочат лицето, чиято информация е въведена в базата данни.

Пълни външни съединения

Пълното външно съединение връща всички данни от двата набора, независимо от тяхното пресичане (Фигура 9.8). Функционално подобно сливане е идентично на сливане, изискващо уникалността на дясното и лявото външно съединение (за сливанията ще говорим малко по-късно в тази глава).

В реалния живот референтната цялост намалява необходимостта от пълни външни обединения, тъй като всеки ред от вторичната таблица трябва да съвпада в първичната таблица (в зависимост от това дали се изисква външен ключ), така че лявото външно съединение често е достатъчно. Пълните външни съединения са особено полезни за почистване на неограничени данни, тъй като те филтрират невалидни стойности.

Следващият пример илюстрира тази ситуация и сравнява пълно външно съединение с дясно и ляво съединение. Таблица две е подчинена и има външен ключ, който се отнася към таблица Първа. Той няма ограничения за външен ключ, така че може да има някои несъответствия, които могат лесно да бъдат идентифицирани с помощта на пълно външно съединение:

СЪЗДАЙТЕ ТАБЛИЦА dbo.One (

СЪЗДАЙТЕ ТАБЛИЦА dbo.Two (

Данните в този пример съдържат низове, които нарушават референтната цялост. Външният ключ (OnePK) за редовете Plane и Cycle в таблица Two няма съответствие в таблица One. Следната партида от инструкции вмъква осем реда в таблиците:

INSERT dbo.One (OnePK, Thingl)

СТОЙНОСТИ (1, „Old Thing“)

INSERT dbo.One (OnePK, Thingl)

ЦЕННОСТИ (2, „Ново нещо“)

INSERT dbo.One (OnePK, Thingl)

СТОЙНОСТИ (3, „Червено нещо“)

INSERT dbo.One (OnePK, Thingl)

ЦЕННОСТИ (4, „Синьо нещо“)

INSERT dbo.Two (TwoPK, OnePK, Thing2)

Вътрешно съединение на таблици Първа и Втора ще върне само два реда, които са били съчетани:

ИЗБЕРЕТЕ Thingl, Thing2 ОТ dbo.One JOIN dbo.Two

ON One.OnePK = Two.OnePK

Резултатът ще бъде както следва:

Ново нещо влак

Лявото външно съединение разширява този списък, за да включи редове от таблица Първа, които нямат съвпадение в таблица Втора:

ИЗБЕРЕТЕ Thingl, Thing2 ОТ dbo.One

ЛЯВО ВЪНШНО ПРИСЪЕДИНЯВАНЕ dbo.Two ON One.OnePK = Two.OnePK

Сега всички редове се връщат от таблица One, но в същото време два реда липсват от таблица Two:

Ново нещо влак

Blue Thing NULL

Пълното външно съединение ще върне всички редове от двете таблици, независимо дали съвпадат или не:

ИЗБЕРЕТЕ Thingl, Thing2 ОТ dbo.One

ПЪЛНО ВЪНШНО ПРИСЪЕДИНЯВАНЕ dbo.Two ON One.OnePK = Two.OnePK

Позициите на равнина и кола вече са изброени заедно с всички редове от таблица Първо: Thingl Thing2

Ново нещо влак

Blue Thing NULL

Както е показано в примера, пълното външно съединение е отличен инструмент за намиране на всички данни, включително невалидни данни. Чрез създаването на разни заявки (ще говорим за тях по-късно в тази глава) въз основа на външни съединения, можете да идентифицирате и отхвърлите невалидни данни.

Поставянето във външно състояние се присъединява

Що се отнася до вътрешните съединения, условието има същия ефект, когато е поставено в клауза JOIN или WHERE, но това твърдение не се отнася за външни съединения. Когато условието е в клауза JOIN, SQL Server включва всички редове от външната таблица в резултата и след това използва условието за включване на редове от вторичната таблица. Когато ограничението е поставено в клауза WHERE, съединението се извършва и след това условието се прилага към комбинираните редове. Следващите два примера демонстрират ефекта от поставянето на условие в различни оферти.

В първата заявка лявото външно съединение включва всички редове от таблица Първо и след това ги обединява с тези редове от таблица Втора, за които стойността на полето OpeRK и в двете таблици е равна и стойността на полето Thinngl е New Thing. В резултат на това ще видим всички редове от таблица Първа, но по-малко редове от таблица Втора:

ИЗБЕРЕТЕ Thingl, Thing2 ОТ dbo.One

ЛЯВО ВЪНШНО ПРИСЪЕДИНЯВАНЕ dbo.Two ON One.OnePK = Two.OnePK AND One.Thingl = 'New Thing 1

Резултатът ще бъде както следва:

Ново нещо влак

Blue Thing NULL

Втората заявка изпълнява ляво външно съединение, което води до четири реда. След това клаузата WHERE прилага ограничението към този резултат, оставяйки само един ред - с продукта New Thing.

ИЗБЕРЕТЕ Thingl, Thing2 ОТ dbo.One

ЛЯВО ВЪНШНО ПРИСЪЕДИНЯВАНЕ dbo.Two ON One.OnePK = Two.OnePK WHERE One.Thingl = ‘New Thing’

Резултатът ще изглежда така:

Ново нещо влак

Когато преподавах на учениците как да създават заявки, често използвах следната аналогия, за да обясня различните видове обединения. Представете си поклонническа църква от 17 век, разделена по пол. Всички мъже в него седяха от едната страна, а жените от другата. Сега си представете всяка страна на църквата като таблица с база данни и различни комбинации от хора, напускащи църквата, като различни видове асоциации.

Ако всички семейни двойки станат от местата си, хванат се под ръка и напуснат църквата, тогава това ще бъде вътрешен съюз между мъжете и жените. Полученият набор от данни за напускане на църквата ще съдържа само съвпадащи двойки.

Ако всички мъже се изправят и всеки съпруг вземе ръката на жена си, а всички останали излязат поединично или на групи, тогава това ще бъде пример за ляво външно обединение. Структурата на напускащите църквата ще се състои както от семейни двойки, така и от неженени мъже.

По същия начин, ако всички жени се изправят и излязат от църквата със съпрузите си (разбира се, който ги има), това би било пример за дясно обединение. В този случай всички неженени жени ще напуснат църквата една по една.

В случай на пълно външно обединение, всички ще напуснат църквата, докато семейните двойки ще излязат на ръка.

Източник: Nielsen, Paul. Microsoft SQL Server 2005. Библията на потребителя.: Per. от английски - М .: LLC „I.D. Уилямс ”, 2008. - 1232 с.: аз ще. - Паралелно. синигер Английски.