Таблица 72 Построение внешнего объед1мия

1 Создать внутреннее объединение двух таблиц обычным способом

2 Каждую строку первой таблицы, которая не имеет связи ни с одной строкой второй таблицы, добавить в результаты запроса, присваивая всем столбцам второй таблицы значение null

3 Каждую строку второй таблицы, которая не имеет связи ни с одной строкой первой таблицы, добавить в результаты запроса, присваивая всем столбцам первой таблицы значение null

4 Результирующая таблица является внешним объединением двух таблиц

Левое и правое внешние объединения *

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

Левое внешнее объединение двух таблиц получается, если выполнить пункты 1 и 2 из числа перечисленных в табл 7 2, а пункт 3 пропустить Таким образом, левое внешнее объединение включает все несвязанные строки первой (левой) таблицы, дополняя их значениями null, но не включает несвязанные строки второй (правой) таблицы Вот левое внешнее объединение таблиц girls и boys

Вывести список девонек и мальчиков из одних и тех же городов и девочек, не имеющих пары

SELECT *

FROM GIRLS, BOYS WHERE GIRLS CITY *= BOYS CITY

GIRLS NAME GIRLS CITY BOYS NAME BOYS CITY

Mary Boston John Boston

Mary Boston Henry Boston

Susan Chicago Sam Chicago

Betty Chicago Sam Chicago

Anne Denver NOLL NOLL

Nancy NULL NULL NULL

Таблица результатов этого запроса содержит шесть строк все пары девочка/мальчик из одних и тех же городов и девочки без пары Мальчики, не имеющие пары в этой таблице отсутствуют

Правое внешнее объединение двух таблиц получается, если выполнить пункты 1 и 3 из числа перечисленных в табл 7 2, а пункт 2 пропустить Таким образом, правое внешнее объединение включает все несвязанные строки второй (правой) таблицы, дополняя их значениями null, но не включает несвязанные строки первой (левой) таблицы Вот правое внешнее объединение таблиц girls и boys

Вывести список девочек и мальчиков из одних и тех же городов и мальчиков не имеющих пары

SELECT *

FROM GIRLS, BOYS

WHERE GIRLS.CITY =* BOYS.CITY GIRLS.NAME GIRLS.CITY BOYS.NAME BOYS.CITY

Mary

Mary

Susan

Betty

NULL

NULL

Boston

Boston

Chicago

Chicago

NULL

NULL

John

Henry

James

George

Boston

Boston

Chicago

Chicago

Dallas

NULL

Этот запрос также дает таблицу результатов из шести строк: все пары девочка/мальчик из одних и тех же городов и мальчики без пары. На этот раз в таблицу не вошли девочки, не имеюшие пары. Таблица, у которой все строки включаются в объединение, называется главной, а таблица, недостаюшие элементы которой заполняются значениями null, называется вспомогательной.

На практике левое и правое внешние объединения более полезны, чем полное внешнее объединение, особенно если таблицы связаны через внешний и первичный ключи. В качестве иллюстрации обратимся снова к нашей учебной базе данных. Мы уже приводили пример левого внешнего объединения таблиц salesreps и offices. Столбец rep office таблицы salesreps является внешним ключом таблицы offices; он содержит номера офисов, в которых работают служащие, и допускает наличие значений null, если новому служащему еще не был назначен офис. В нашей базе данных такой служащий есть - это Том Снайдер. Любое объединение, которое формируется на основе данных из этих двух таблиц и в которое предполагается включить сведения о Томе Снайдере, обязано быть внешним, а таблица salesreps должна быть в нем главной.

Вывести список служащих и городов, где они работают.

SELECT NAME, CITY

FROM SALESREPS, OFFICES WHERE REP OFFICE *= OFFICE

NAME

CITY

Tom Snyder Mary Jones Sam Clark Bob Smith Paul Cruz Dan Roberts Bill Adams Sue Smith Larry Fitch

NULL New York New York Chicago Chicago Chicago Atlanta Los Angeles Los Angeles

Nancy Angelli Denver

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

Вывести список служащих и городов, где они работают.

SELECT ЫАМЕ, CITY

FROM SALESREPS, OFFICES WHERE OFFICE =* REP OFFICE

ЫАМЕ CITY

Tom Snyder MULL

Mary Jones Mew York

Sam Clark Mew York

Bob Smith Chicago

Paul Cruz Chicago

Dan Roberts Chicago

Bill Adams Atlanta

Sue Smith Los Angeles

Larry F-i tch Los Angeles

Nancy Angelli Denver

Важно to, что дочерняя таблица является главной в объединении.

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

Вывести список офисов и служащих, работающих в каждом из них.

SELECT CITY, МАМЕ

FROM OFFICES, SALESREPS WHERE OFFICE =* REP OFFICE

CITY МАМЕ

New York Mary Jones

New York Sam Clark

Chicago Bob Smith

Chicago Paul Cruz

Chicago Dan Roberts

Atlanta Bill Adams

Los Angeles Sue Smith Los Angeles Larry Fitch Denver Nancy Angelli

Dallas NULL

Цель запроса - гарантировать, что все строки таблицы offices будут представлены в результатах запроса, поэтому данная таблица является главной. Как видим, таблицы salesreps и offices поменялись ролями. Вследствие этого в результатах запроса отсутствует и строка для Тома Снайдера.

Система записи внешнего объединения *

Так как понятие внешнего объединения не вошло в стандарт SQL1 и не было реализовано в ранних СУБД фирмы IBM, поставщики тех СУБД, в которых это понятие поддерживалось, использовали различные системы записи в своих диалектах SQL. Система записи *=*, использованная в предыдущих параграфах, применяется