Главная » Microsoft SQL Server, Базы данных » Внешние объединения

0

В то время как внутренние объединения содержат только пересечение двух множеств данных, внешние объединения расширяют это множество за счет несоответствующих данных левого или правого набора данных (рис. 9.6).

решают существенную проблему для множества запросов, включая в результат данные, независимо от их соответствия. В приведенном выше примере “клиент- заказ” эта проблема проиллюстрирована достаточно хорошо. Если нам требуется перечислить всех клиентов вместе с заказами, выполненными ими за последнее время, то внутреннее объединение отсеет заказчиков, совершивших покупки раньше начальной даты диапазона. Этот тип ошибки довольно часто встречается в приложениях баз данных.

Рис. 9.6. Внешнее объединение включает не только соответствующие друг другу строки двух источников данных, но и несоответствующие строки вне пересечения множеств

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

При создании запросов в конструкторе вы можете в любой момент изменить тип запроса с внутреннего, принятого по умолчанию, на внешний. Это можно сделать с помощью контекстного меню, а также окна свойств объединения (рис. 9.7). Конструктор запросов отлично справляется с демонстрацией типа объединения, используя для этого специальные символы.

В коде SQL внешнее объединение объявляется с помощью ключевых слов left outer или right outer перед ключевым словом join (технически ключевое слово outer необязательное).

SELECT *

FROM Tablel

LEFT|RIGHT [OUTER] JOIN Table2 ON Tablel.column = Table2.column

Несмотря на то что некоторые слова в SQL являются необязательными (такие, как INNER или OUTER) или могут заменяться сокращениями (например, ргос для procedure), следование полному синтаксису повышает читаемость кода. В то же время многие разработчики опускают необязательный синтаксис.

Puc. 9.7. В окне свойств объединения отображаются столбцы, участвующие в нем. Здесь можно изменить условие объединения (=, >, < и т.п.) и добавить левую или правую часть внешнего объединения (все строки из таблицы Product, все строки из таблицы OrderDetails)

Совсем не сложно отличить внешнее правое объединение от левого. В коде понятия “левое” и “правое” относятся к таблице, все записи которой будут включены в результат, независимо от найденных соответствий. Эта таблица (иногда называемая управляющей) обычно перечисляется в запросе первой; по этой причине левое внешнее объединение используется чаще правого. Я полагаю, что основную путаницу в вопрос отличия правого внешнего объединения от левого вносит графическое представление. Все дело в том, что слова “правое” и “левое” относятся к порядку перечисления таблиц в коде SQL, а их расположение на экране в графическом представлении только вносит путаницу.

Чтобы приведенный ранее в качестве примера запрос “клиент-контакт” возвращал всех заказчиков независимо от наличия у них заказа, требуется лишь заменить внутреннее объединение левым внешним объединением.

SELECT ContactCode, OrderNumber FROM dbo.Contact

LEFT OUTER JOIN dbo.[Order]

ON [Order].ContactID = Contact.ContactID ORDER BY ContactCode

Данное левое внешнее объединение включит все строки из таблицы Contact и соответствующие им строки из таблицы [Order]. Сокращенный результат этого запроса выглядит так:

Contact.       [Order].

ContactCode        OrderNumber

106                      10

107                      NULL

108                      NULL

Поскольку контакты с номерами 107 и 108 не имеют соответствующих им строк в таблице [Order], в ее столбцах результирующего набора данных возвращаются пустые значения.

Язык Transact-SQL расширил синтаксис внешнего объединения ANSI SQL-89 за н0ВИНка           счет Добавления звездочки справа от знака равенства в условии предложения

2005     where. Несмотря на то что этот синтаксис работает в версии SQL Server 2000,

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

и необязательные внешние ключи

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

Еще одним примером использования необязательного внешнего ключа является столбец приоритетов или предупреждений. Однако в данном случае он должен указывать на допустимую строку в таблице приоритетов.

Учебная база данных OBXKites использует аналогичную схему приоритетов заказов, поэтому отчет по всем заказам с их приоритетами требует наличия внешнего объединения.

SELECT OrderNumber, OrderPriorityName FROM dbo. [Order]

LEFT OUTER JOIN dbo.OrderPriority ON [Order].OrderPrioritylD =

OrderPriority.OrderPrioritylD

Данное левое внешнее объединение извлекает все заказы и приоритеты, соответствующие им (если таковые имеются). Сценарий OBXKites_Populate. sql устанавливает двум заказам приоритет Rush (немедленно).

OrderNumber OrderPriorityName

1                  Rush

2                  NULL

3                  Rush

4                  NULL

5                  NULL

6                  NULL

7                  NULL

8                  NULL

9                  NULL

10               NULL

Возвратные отношения (называемые также рекурсивными и собственными) используют и необязательные внешние ключи. В учетной базе данных Family внешними ключами являются поля Mot he ID и Father ID, связанные с полем Ре г son ID матери и отца. Данный необязательный внешний ключ позволяет вводить информацию о людях, даже если данные об их родителях еще не введены в базу данных. В то же время, если поля MotherlD и FatherlD заполнены, они должны указывать на лицо, информация о котором занесена в базу данных.

Полные внешние объединения

Полное внешнее объединение возвращает все данные из обоих наборов независимо от их пересечения (рис. 9.8). Функционально такое объединение идентично слиянию с требованием уникальности правого и левого внешних объединений (о слияниях речь пойдет немного позже в этой главе).

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

Следующий пример иллюстрирует такую ситуацию и сравнивает полное внешнее объединение с правым и левым объединением. Таблица Two является подчиненной и имеет внешний ключ, ссылающийся на таблицу One. В ней не существует ограничений на внешний ключ, поэтому могут появляться некоторые несоответствия, которые легко выявить с помощью полного внешнего объединения:

CREATE TABLE dbo.One (

OnePK INT,

Thingl VARCHAR(15)

)

CREATE TABLE dbo.Two (

TwoPK INT,

OnePK INT,

Thing2 VARCHAR(15)

)

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

INSERT dbo.One(OnePK, Thingl)

VALUES (1, ‘Old Thing’)

INSERT dbo.One(OnePK, Thingl)

VALUES (2, ‘New Thing’)

INSERT dbo.One(OnePK, Thingl)

VALUES (3, ‘Red Thing’)

INSERT dbo.One(OnePK, Thingl)

VALUES (4, ‘Blue Thing’)

INSERT dbo.Two(TwoPK, OnePK, Thing2)

VALUES(1,0, ‘Plane’)

INSERT dbo.Two(TwoPK, OnePK, Thing2)

VALUES{2,2, ‘Train’)

INSERT dbo.Two(TwoPK, OnePK, Thing2)

VALUES(3,3, ‘Car’)

INSERT dbo.Two(TwoPK, OnePK, Thing2)

VALUES(4,NULL, ‘Cycle’)

Внутреннее объединение таблиц One и Two вернет только две строки, для которых было найдено соответствие:

SELECT Thingl, Thing2 FROM dbo.One JOIN dbo.Two

ON One.OnePK = Two.OnePK

Результат будет следующим:

Thingl            Thing2

New Thing     Train

Red Thing      Car

Левое внешнее объединение расширяет этот список за счет включения строк из таблицы One, не имеющих соответствий в таблице Two:

SELECT Thingl, Thing2 FROM dbo.One

LEFT OUTER JOIN dbo.Two ON One.OnePK = Two.OnePK

Теперь из таблицы One возвращаются все строки, но в то же время отсутствуют две строки из таблицы Two:

Thingl            Thing2

Old Thing    NULL

New Thing     Train

Red Thing         Car

Blue Thing   NULL

Полное внешнее объединение вернет все строки из обеих таблиц, независимо от того, найдено ли для них соответствие или нет:

SELECT Thingl, Thing2 FROM dbo.One

FULL OUTER JOIN dbo.Two ON One.OnePK = Two.OnePK

Теперь позиции Plane и Car перечислены наряду со всеми строками из таблицы One: Thingl               Thing2

NULL            Plane

New Thing     Train

Red Thing         Car

NULL            Cycle

Blue Thing   NULL

Old Thing    NULL

Как было показано в примере, полное внешнее объединение является прекрасным инструментом поиска всех данных, включая некорректные. Создавая запросы различий (о них мы поговорим позже в этой главе), построенные на внешних объединениях, можно выявлять и аннулировать некорректные данные.

Помещение во внешние объединения условий

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

В первом запросе левое внешнее объединение включает все строки из таблицы One, а затем объединяет их с теми строками из таблицы Two, для которых значения поля ОпеРК в обеих таблицах равно и значение поля Thinngl равно New Thing. В результате мы увидим все строки из таблицы One, но меньше строк из таблицы Two:

SELECT Thingl, Thing2 FROM dbo.One

LEFT OUTER JOIN dbo.Two ON One.OnePK = Two.OnePK AND One.Thingl = ‘New Thing1

Результат будет следующим:

Thingl           Thing2

Old Thing   NULL

New Thing Train

Red Thing  NULL

Blue Thing NULL

Второй запрос выполняет левое внешнее объединение, которое дает в результате четыре строки. После этого предложение WHERE применяет к этому результату ограничение, оставляя только одну строку — с товаром New Thing.

SELECT Thingl, Thing2 FROM dbo.One

LEFT OUTER JOIN dbo.Two ON One.OnePK = Two.OnePK WHERE One.Thingl = ‘New Thing’

Результат получится следующим:

Thingl           Thing2

New Thing   Train

Аналогия объединений

Когда я обучал студентов созданию запросов, то для объяснения разных типов объединений часто использовал следующую аналогию. Представьте себе церковь пилигримов в XVII веке, разделенную по половому признаку. Все мужчины в ней сидели по одну сторону, а женщины — по другую. Теперь представьте себе каждую сторону церкви как таблицу базы данных, а различные комбинации людей, уходящих из церкви, — как разные типы объединений.

Если все супружеские пары встанут со своих мест, возьмут друг друга под руку и выйдут из церкви, то это будет внутреннее объединение между мужчинами и женщинами. Результирующий набор данных покидающих церковь будет содержать только соответствующие друг другу пары.

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

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

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии