Внешнее соединение таблиц

 

Рассмотренные соединения называют внутренними (INNER JOIN). В некоторых случаях требуются соединения другого вида – внешние соединения (OUTER JOIN). Рассмотрим две таблицы A (Stud, Prep) и B (Prep, Kaf) с информацией о студентах-дипломниках и их руководителях. Здесь Stud – фамилия студента, Prep – фамилия руководителя диплома, Kaf – название кафедры, где работает руководитель. Будем для определенности считать, что однофамильцы отсутствуют.

Пусть, например, таблицы заполнены следующим образом

Таблица 8

Записи таблицы A

Stud Prep
Иванов Гусев
Петров Николаев
Сидоров NULL

 

Таблица 9

Записи таблицы B

Prep Kaf
Гусев Информатика
Николаев Математика

 

Значение NULL для студента Сидорова показывает, что данные о его руководителе отсутствуют.

Требуется дать полный список дипломников со сведениями об их руководителях. Составим запрос

SELECT Stud, A.Prep, Kaf FROM A, B WHERE A.Prep = B.Prep

Результатом будет таблица

Таблица 10

Результат запроса

Stud A.Prep Kaf
Иванов Гусев Информатика
Петров Николаев Математика

 

А где же Сидоров? Хотелось бы получить таблицу

Таблица 11

Желаемый результат

Stud A.Prep Kaf
Иванов Гусев Информатика
Петров Николаев Математика
Сидоров NULL NULL

 

Как это сделать?

Выходом является внешнее соединение таблиц A и B, которое будем обозначать пока символами ‘*=*’. Для получения полного внешнего соединения двух таблиц необходимо

1. Создать внутреннее соединение таблиц

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

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

В приведенном выше запросе достаточно заменить знак ‘=’, определяющий внутреннее соединение таблиц A и B, на операцию внешнего соединения ‘*=*’.

Добавим еще одну запись в таблицу A

Таблица 12

Записи таблицы A

Stud Prep
Иванов Гусев
Петров Николаев
Сидоров NULL
Волков Митрофанов

 

Результатом запроса с внешним соединением будет таблица

 

Таблица 13

Результат запроса с внешним соединением

Stud A.Prep Kaf
Иванов Гусев Информатика
Петров Николаев Математика
Сидоров NULL NULL
Волков Митрофанов NULL

 

Кафедра преподавателя Митрофанова неизвестна.

Добавим сейчас запись в таблицу B

Таблица 14

Записи таблицы B

Prep Kaf
Гусев Информатика
Николаев Математика
Кокорин Математика

 

В результате получим таблицу

Таблица 15

Результат запроса

Stud A.Prep Kaf
Иванов Гусев Информатика
Петров Николаев Математика
Сидоров NULL NULL
Волков Митрофанов NULL
NULL NULL Математика

 

В последней строке фамилия Кокорин отсутствует, так как в запросе указано поле A.Prep, а не B.Prep.

Предположим, что нас в первую очередь интересует информация о студентах, а не о преподавателях. Тогда нам не нужна последняя строка. Можно получить требуемый результат, используя операцию левого внешнего соединения ‘*=’. При выполнении этой операции третий пункт операции полного внешнего соединения не выполняется. Аналогично, при правом внешнем соединении ‘=*’ пропускается второй пункт полного внешнего соединения.

Пусть, например, таблица A содержит полный список студентов-дипломников, а B – список преподавателей, планируемых в качестве руководителей. Сместим акцент запроса, потребовав дать сведения о руководстве дипломами только данных преподавателей. Выполним запрос

SELECT Stud, B.Prep, Kaf FROM A, B WHERE A.Prep =* B.Prep

Для таблиц, заполненных так же, как в последнем примере, в результате получим

Таблица 16

Результат запроса с правым внешним соединением

Stud A.Prep Kaf
Иванов Гусев Информатика
Петров Николаев Математика
NULL Кокорин Математика

 

Информации о студентах Сидорове и Волкове не будет, поскольку их руководители отсутствуют в таблице B.

По стандарту SQL соединения таблиц задаются в предложении FROM. Внутреннее соединение именуется INNER JOIN, полное внешнее соединение -FULL [OUTER] JOIN, левое и правое соединения – LEFT [OUTER] JOIN и RIGHT [OUTER] JOIN (слово OUTER может опускаться). Условие соединения определяется после слова ON. Соединение, как и прежде, возможно и по нескольким полям.

Например, рассмотренный выше запрос может быть задан в форме

SELECT Stud, A.Prep, Kaf FROM A LEFT JOIN B ON A.Prep = B.Prep

Для одноименных полей двух таблиц вместо условия со словом ON можно использовать форму USING (<список одноименных полей>) или просто слово NATURAL. В последнем случае соединение происходит по всем одноименным полям.

Полное внешнее соединение требуется редко и реализовано не во всех СУБД.