Полное внешнее соединение

FULL (OUTER) - тип соединения "полное (внешнее)". Это комбинация левого и правого соединений. В полное соединение включаются все строки из обеих таблиц. Для совпадающих строк поля заполняются реальными значениями, для несовпадающих строк поля заполняются в соответствии с правилами левого и правого соединений. На практике этот тип соединения требуется чрезвычайно редко. Поэтому примеров мы приводить не будем.

Более сложные примеры соединений

Количество выполняемых соединений таблиц в одном операторе SELECT не имеет физических ограничений, однако рекомендуется использовать их не более 16.

Рассмотрим пример двойного внешнего соединения, т. е. тот случай, когда к первой таблице присоединяется не одна, а уже две таблицы.

Чтобы увидеть фамилии с именами и отчествами всех членов экипажа с указанием их роли, номера авиалинии и даты вылета нужно выполнить два соединения таблицы EQUIPAGE с таблицей PERSON и с таблицей RANK.

Введите и выполните оператор:[38]

SELECT E.EQ_FL_DATE AS "Дата вылета",

E.EQ_FL_NUMAS"Авиалиния",

P.PR_NAME2||' '||P.PR_NAME3||' '||P.PR_NAMEAS"ФИО",

R.RNK_NAMEAS"Роль"

FROM EQUIPAGEEINNER JOINPERSONP

ON E.EQ_PR_CODE=P.PR_CODEINNER JOINRANKR

ON E.EQ_RNK_CODE=R.RNK_CODE

WHERE E.EQ_FL_DATEBETWEEN'1.02.08'AND'29.02.08'

ORDER BY E.EQ_FL_DATE,E.EQ_FL_NUM, 3

Вначале отбираются все члены экипажа из таблицы EQUIPAGE (здесь она становится главной) на основании условий предложения WHERE (все рейсы за февраль 2008). В выбранные строки добавляются полные имена из таблицы PERSON. Условием соединения является равенство кода в таблице PERSON коду человека в таблице EQUIPAGE. После чего к этим строкам добавляются названия роли из таблицы RANK. Здесь условием соединения является равенство кода, первичного ключа таблицы RANK коду роли в начальной, "главной", таблице EQUIPAGE. Результат сортируем по дате вылета, номеру авиалинии и фамилиям именам и отчествам (так как поле ФИО вычисляется, в списке сортировки указываем номер поля).

В результате выполнения двойного соединения мы получим

Листинг 19. Двойное внешнее соединение – список всех членов экипажа с указанием их роли, номера авиалинии и даты вылета

Необходимо отметить, что порядок указания соединяемых таблиц не имеет значения. Здесь важно задать первую, "главную", таблицу сразу после ключевого слова FROM, все остальные соединяемые таблицы "подключаются" к строкам, выбранным именно из этой таблицы, и ее строки выбираются на основании условия в предложении WHERE.

Для иллюстрации этого изменим предыдущий оператор SELECT, поменяв местами соединяемые таблицы. Выполните оператор:

SELECT E.EQ_FL_DATE AS "Дата вылета",

E.EQ_FL_NUMAS"Авиалиния",

P.PR_NAME2||' '||P.PR_NAME3||' '||P.PR_NAMEAS"ФИО",

R.RNK_NAMEAS"Роль"

FROM PERSONPINNER JOINEQUIPAGEE

ON E.EQ_PR_CODE=P.PR_CODEINNER JOINRANKR

ON E.EQ_RNK_CODE=R.RNK_CODE

WHERE E.EQ_FL_DATEBETWEEN'1.02.08'AND'29.02.08'

ORDER BY E.EQ_FL_DATE,E.EQ_FL_NUM, 3

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

Приведем еще один более сложный пример, который требует соединения четырех таблиц. Выведем тариф для всех рейсов вылетающих из города «Пермь». Мы должны получить название аэропорта прилета, номер маршрута, вид салона и саму стоимость билета. Выполните оператор:

SELECT T.TR_AL_NUM AS "Номер Авиалинии",

ATO.AP_NAMEAS"Аэропорт прилета",

S.SL_NAMEAS"Салон",

T.TR_COSTAS"Стоимость"

FROM TARIFFT

INNER JOIN AIRPORT AFROMON (T.TR_AP_FROM=AFROM.AP_CODE)

INNER JOIN AIRPORT ATOON (T.TR_AP_TO=ATO.AP_CODE)

INNER JOIN SALONSON (T.TR_SL_TYPE=S.SL_TYPE)

INNER JOIN CITY C ON (AFROM.AP_CT_CODE= C.CT_CODE)

WHERE C.CT_NAME = 'Пермь'

Таким образом, в запросе будут задействованы следующие таблицы: TARIFF, AIRPORT, SALON, CITY. Обратите внимание, что таблица AIRPORT используется в запросе два раза – один раз мы используем соединение используя поле TR_AP_TO таблицы TARIFF, таблица в этом соединении обозначается псевдонимом ATO и означает аэропорт прилета, второй раз в соединении используется поле TR_AP_FROM, в этом случае таблица AIRPORT будет обозначаться псевдонимом AFROM и означает аэропорт вылета.

Следующий пример потребует объединения шести таблиц. Получим список (фамилию, имя, отчество) тех кто имеет билеты из Перми в Москву на 11.01.2008, кроме фамилии имени отчества выведем вид салона, стоимость билета и занимаемое место. В запросе мы должны связать следующие таблицы: PERSON связываем с таблицей TICKET, TICKET – с TARIFF, TARIFF – с SALON, далее TARIFF дважды связываем а AIRPORT (первый раз связываем используя поле TR_AP_FROM, т.е. определяя аэропорт вылета, второй раз связываем используя поле TR_AP_TO, тем самым будет определен аэропорт прилета), AIRPORT дважды связывается с CITY (один раз для города, где размещен аэропорт вылета, второй раз связь будет определять город, где размещен аэропорт прилета). Так как таблицы AIRPORT и CITY используются два раза, для них используются различные синонимы.

Приводим вид запроса:[39]

SELECT PR_NAME2AS "Имя",

PR_NAME3 AS"Отчество",

PR_NAMEAS"Фамилия",

S.SL_NAME AS"Салон",

TC.TC_SEAT AS"Место",

TR.TR_COST AS"Стоимость"

FROM PERSONP

INNER JOIN TICKETTCONTC.TC_PR_CODE=P.PR_CODE

INNER JOIN TARIFFTRONTR.TR_CODE=TC.TC_TR_CODE

INNER JOIN SALONSONTR.TR_SL_TYPE=S.SL_TYPE

INNER JOIN AIRPORTAFROMONAFROM.AP_CODE=TR.TR_AP_FROM

INNER JOIN CITYCFROMONCFROM.CT_CODE=AFROM.AP_CT_CODE

INNER JOIN AIRPORTATOONATO.AP_CODE=TR.TR_AP_TO

INNER JOIN CITYCTOONCTO.CT_CODE=ATO.AP_CT_CODE

WHERE CFROM.CT_NAME='Пермь'ANDCTO.CT_NAME='Москва'

AND TC.TC_FL_DATE='11.01.2008'

Получаем список:

Листинг 20. Список купивших билеты из Перми в Москву на 11.01.2008, объединение шести таблиц.

Последний пример демонстрирует использование соединения таблиц в сочетании с группировкой. Найдем суммарную стоимость проданных за 2008 год билетов для каждой из авиакомпаний. Для того чтобы найти то что нам требуется нужно просуммировать стоимость билета из таблицы TARIFF для каждого проданного (за указанный год) билета. В список вывода нужно поместить наименование авиакомпании и сумму стоимостей из таблицы TARIFF. Результат должен быть сгруппирован по наименованиям авиакомпаний. Для наглядности упорядочим выводимые строки по величине суммы.

Введем запрос:[40]

SELECT AC.AC_NAME AS “Авиакомпания“,

SUM( TRF.TR_COST) AS“Стоимость”

FROM TARIFFTRF

INNER JOIN TICKETTCON (TRF.TR_CODE=TC.TC_TR_CODE)

INNER JOIN AIRLINEAL ON (TRF.TR_AL_NUM=AL.AL_NUM)

INNER JOIN AIRCOMPANYACON (AL.AL_AC_CODE=AC.AC_CODE)

GROUP BY AC.AC_NAME

ORDER BY2

В результате получим список:

Листинг 21. Результат объединения и группировки, суммарная стоимость проданных за 2008 год билетов для каждой из авиакомпаний.