Внутренние и внешние соединения

Внутренние соединения.В стандарте SQL применяются отличные от рассмотренной выше синтаксические конструкции для соединения нескольких таблиц, которые могут быть использованы и в СУБД Oracle. Причем различают внешние и внутренние соединения, которые рассмотрим на примере двух абстрактных таблиц girls (id, g_fname, g_lname,city, age)и boys (id,b_fname, b_lname,city, age).

Инструкцию внутреннего соединения по полю city можно записать следующим образом:

SELECT *

FROM girls [INNER] JOIN boys ON girls.city=boys.city;

В этом случае две таблицы объединяются явно посредством операции join, а условие отбора, описывающее соединение, находится теперь в предложении ON внутри предложения FROM. В условии отбора могут быть заданы любые критерии сравнения строк двух соединяемых таблиц.

В таких простых двухтабличных соединениях все содержимое предложения WHERE просто переходит в предложение ON, т.е. последнее не добавляет ничего нового в язык SQL.

Стандарт SQL допускает еще один вариант запроса на простое внутреннее соединение таблиц. Так как связанные столбцы этих таблиц имеют одинаковые имена и сравниваются на предмет равенства (что делается довольно часто), то можно использовать альтернативную форму предложения ON, в которой задается список имен связанных столбцов:

SELECT *

FROM girls [INNER] JOIN boys USING (city, age);

В предложении USING перечисляются через запятую имена связанных столбцов; котрые должны быть идентичными в обеих таблицах. Это предложение полностью эквивалентно предложению ON, в котором каждая пара связанных столбцов задается явно, но намного компактнее и, следовательно, легче для понимания. Конечно, если связанные столбцы имеют разные имена в таблицах, то необходимо использовать предложение ON или WHERE со знаком равенства. Предложение ON требуется использовать также в том случае, если соединение не производится по равенству связанных столбцов. Например, если вы хотите выбрать пары девочка/мальчик, в которых девочка старше мальчика, то должны задать соединение с помощью предложения ON:

SELECT * FROM girls [INNER] JOIN boys

ON (girls.city=boys.city AND girls.age>boys.age);

Наконец, имеется еще один вариант этого простого запроса. Соединение двух таблиц, в котором связанные столбцы имеют идентичные имена, называется естественным соединением, так как обычно это действительно самый «естественный» способ соединения двух таблиц. Запрос на выборку пар девочка/мальчик, живущих в одних и тех же городах и имеющих тот же самый возраст, можно выразить как естественное соединение следующим образом:

SELECT * FROM girls NATURAL [INNER] JOIN boys;

Если задано ключевое слово natural, предложения on и using могут отсутствовать в запросе на соединение, так как в естественном соединении точно определено условие отбора для соединения двух таблиц - сравниваются все столбцы с идентичными именами в обеих таблицах.

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

Внешние соединения.Стандарт SQL обеспечивает полную поддержку внешних соединений, расширяя языковые конструкции, используемые для внутренних соединений. Например, полное внешнее соединение таблиц girls и boys (без столбцов age) создается следующим запросом:

SELECT *

FROM girls FULL [OUTER] JOIN boys ON girls.city=boys.city;

Таблица результатов запроса будет содержать по одной строке для каждой связанной по полю city пары девочка/мальчик, а также по одной строке для каждой несвязанной равенством значений в поле city записи для девочки или мальчика, расширенной значениями NULL в столбцах другой таблицы. В стандарте SQL для внешних соединений допустимы те же самые вариации, что и для внутренних соединений, т.е. запрос можно было бы записать так:

SELECT *

FROM girls NATURAL FULL [OUTER] JOIN boys;

Или так:

SELECT *

FROM girls FULL [OUTER] JOIN boys USING (city);

Ключевое слово outer также не является обязательным. По слову fulL СУБД сама определяет, что запрашивается внешнее соединение.

Левое и правое внешние соединения обозначаются словами left и right вместо слова full. Вот вариант того же запроса, определяющий левое внешнее соединение:

SELECT *

FROM girls LEFT [OUTER] JOIN boys USING (city);

В таком запросе в результат войдут строки связанных пар девочка/мальчик и все несвязанные строки из таблицы girls, содержащие NULL для значений полей таблицы boys, но не войдут несвязанные строки из таблицы boys.

Правое внешнее соединение:

SELECT *

FROM girls RIGHT [OUTER] JOIN boys USING (city);

В данном случае в таблице результатов будут представлены пары девочка/мальчик и несвязанные строки из таблицы boys («правая» таблица объединения) содержащие значения NULL для несвязанных значений полей таблицы girls, но будут отсутствовать несвязанные строки из таблицы girls.

Расширенное предложение FROM поддерживает также два других способа соединения таблиц - декартово произведение и собственное соединение. Вот запрос, создающий декартово произведение таблиц girls и boys:

SELECT *

FROM girls CROSS JOIN boys;

По определению, декартово произведение (называемое перекрестным соединением (cross join)) содержит все возможные пары строк из двух таблиц. Оно является результатом «умножения» двух таблиц и превращает таблицы с данными, например, о трех девочках и двух мальчиках в таблицу шести пар девочка/мальчик (3*2=6). Перекрестным соединениям не сопутствуют никакие «связанные столбцы» или «условия отбора», поэтому предложения ON и USING в них не допускаются. Следует отметить, что операция перекрестного соединения не добавляет ничего нового к возможностям SQL. Те же результаты можно получить с помощью простого внутреннего соединения, если не задать в нем условия отбора. Поэтому предыдущий запрос можно переписать так:

SELECT * FROM girls, boys;

Ключевые слова CROSS JOIN в предложении FROM просто в явном виде указывают на то, что создается декартово произведение. Для большинства БД такая операция вряд ли будет представлять практический интерес. Она полезна лишь тогда, когда на основе полученной таблицы строятся более сложные выражения, например итоговые запросы.

И, наконец, таблица может соединяться сама с собой – так называемое собственное соединение (self-join). Например, вернемся к основному примеру БД и составим все возможные пары между сотрудниками:

SELECT s1.fname||' и '||s2.fname

FROM staff s1

JOIN staff s2

ON (s2.sno!=s1.sno);

Основные выводы.Имена таблиц, из которых берутся данные, задаются в предложении FROM многотабличного запроса (соединения).

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

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

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

Таблицу можно соединять саму с собой, при этом необходимо использовать псевдоним таблицы.

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

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

На практике левое и правое внешние соединения более полезны, чем полное внешнее соединение, особенно если таблицы связаны через внешний и первичный ключи.