1.3.32. Внешнее объединение таблиц

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

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

SELECT NAME, REP_OFFICE FROM SALESREPS

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

SELECT NAME, CITY FROM SALESREPS, OFFICES WHERE REP_OFFICE = OFFICE

На первый взгляд, эти два запроса должны давать одинаковое количество строк, но это не так. Почему? Потому что может быть сотрудник, который в настоящий момент ещё не получил назначение ни в один офис и его строка имеет значение NULL в столбце REP_OFFICE (это связанный столбец для данного объединения). Значение NULL не совпадает ни с одним идентификатором офиса в таблице OFFICES, поэтому строка для такого служащего в таблице SALESREPS остаётся без пары. В результате она "исчезает" из объединения. Таким образом, стандартное SQL-объединение может привести к потере информации, если объединяемые таблицы содержат несвязанные строки.

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

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

SELECT NAME, CITY FROM SALESREPS, OFFICES WHERE REP_OFFICE *= OFFICE

Такие результаты запроса получаются с помощью другой операции объединения, называемой внешним объединением таблиц (в предложении WHERE оно обозначается символами *=). Внешнее объединение является расширением стандартного объединения, описанного выше, и иногда называемого внутренним объединением таблиц. В стандарте SQL1 дано определение только внутреннего объединения; понятие внешнего объединения в нём отсутствует. Ранние СУБД фирмы также поддерживают только внутреннее объединение. Однако внешнее объединение является понятной и полезной частью реляционной модели базы данных и реализовано во многих СУБД, включая SQL Server, Oracle, Sybase и Informix. Как показано ниже, внешнее объединение – это наиболее естественный способ для выражения запросов определённого типа.

Чтобы понять смысл внешнего объединения, будет полезно рассмотреть две простые таблицы, изображённые на рисунке.

Рисунок 1.3.32.1 – Внешнее объединение

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

SELECT * FROM GIRLS, BOYS WHERE GIRLS.CITY = BOYS.CITY

Внутреннее объединение даёт четыре строки в таблице результатов запроса. Обратите внимание, что две девочки (Анна (Anne) и Нэнси (Nancy)) и два мальчика (Джеймс (James) и Джордж (George)) не представлены в таблице результатов запроса. Эти строки не имеют пары в другой таблице и поэтому отсутствуют в таблице результатов внутреннего объединения.

Две несвязанные строки (Анна и Джеймс) имеют действительные значения в столбцах CITY, НО ОНИ не совпадают ни с одним городом в противоположной таблице. Две другие несвязанные строки (Нэнси и Джордж) имеют в столбцах CITY значение NULL, а по правилам SQL значение NULL не равно никакому другому значению (даже другому значению NULL).

Предположим, что вы хотите вывести список пар девочка/мальчик, живущих в одних и тех же городах, и включить в него девочек и мальчиков без пары. Этот результат даёт внешнее объединение таблиц GIRLS и BOYS. Схема формирования внешнего объединения изображена на рисунке. Вот инструкция SQL, дающая в результате внешнее объединение:

SELECT * FROM GIRLS, BOYS WHERE GIRLS.CITY *=* BOYS.CITY

Внешнее объединение двух таблиц содержит восемь строк. Четыре строки идентичны строкам внутреннего объединения этих таблиц. Две другие строки, для Анны и Нэнси, являются несвязанными строками таблицы GIRLS. Эти строки были расширены значениями NULL, т.е. приравнены к воображаемой строке таблицы BOYS, содержащей только значения NULL, и добавлены в результаты запроса. Две последние строки, для Джеймса и Джорджа, являются несвязанными строками таблицы BOYS. Эти строки также были расширены значениями NULL (приравнены к воображаемой строке таблицы GIRLS, содержащей только значения NULL) и добавлены в результаты запроса.

Как видно из этого примера, внешнее объединение является объединением, "сохраняющим информацию". Каждая строка таблицы BOYS представлена в таблице результате запроса (некоторые – более одного раза). Аналогично, каждая строка таблицы GIRLS представлена в таблице результатов (некоторые, опять-таки, более одного раза)

Рассмотрим логику построения внешнего объединения:

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

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

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

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