Многотабличные запросы на выборку в Oracle

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

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;

Таблица результатов запроса будет содержать по одной строке для каждой связанной пары девочка/мальчик, а также по одной строке для каждой несвязанной записи для девочки или мальчика, расширенной значениями 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);

Как уже говорилось, в результаты запроса войдут строки связанных пар девочка/мальчик и все несвязанные строки, содержащие NULL, из таблицы girls, но не войдут несвязанные строки из таблицы boys. И наоборот, вот вариант этого же запроса, задающий правое внешнее объединение:

SELECT *

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

В данном случае в таблице результатов будут представлены пары девочка/мальчик и несвязанные строки из таблицы boys ("правая" таблица объединения), но будут отсутствовать несвязанные строки из таблицы 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 просто в явном виде указывают на то, что создается декартово произведение. Для большинства БД такая операция вряд ли будет представлять практический интерес. Она полезна лишь тогда, когда на основе полученной таблицы строятся более сложные выражения, например итоговые запросы.

Расширенный запрос на соединение заключает в себе некоторые черты как операции объединения union, так и операции соединения join. Напомним, что операция union объединяет строки двух таблиц, у которых должно быть одинаковое число столбцов, причем типы соответствующих столбцов обеих таблиц должны быть совместимыми. Простейший запрос на объединение:

SELECT * FROM girls

UNION [ALL]

SELECT * FROM boys;

Например, будучи примененным к таблице girls, состоящей из трех строк, и таблице boys, содержащей две строки, такой запрос возвратит таблицу из пяти строк. Каждая из них будет в точности соответствовать одной из строк таблицы girls либо boys. В таблице результатов запроса будет два столбца, name и city, как и в обеих исходных таблицах. Отметим, что по умолчанию оператор UNION удаляет все повторяющиеся записи (как DISTINCT) и получить полный набор записей можно используя конструкцию UNION ALL.

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

SELECT e1.ename||' works for '||e2.ename "Employees and their Managers"

FROM emp e1

JOIN emp e2

ON (e1.mgr=e2.empno);

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

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

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

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

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

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

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

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