Стандарт SQL2 расширил понятие условного объединения. В стандарте SQL1 при объединении отношений использовались только условия, задаваемые в части WHERE оператора SELECT, и в этом случае в результирующее отношение попадали только сцепленные по заданным условиям кортежи исходных отношений, для которых эти условия были определены и истинны. Однако в действительности часто необходимо объединять таблицы таким образом, чтобы в результат попали все строки из первой таблицы, а вместо тех строк второй таблицы, для которых не выполнено условие соединения, в результат попадали бы неопределенные значения. Или наоборот, включаются все строки из правой (второй) таблицы, а отсутствующие части строк из первой таблицы дополняются неопределенными значениями. Такие объединения были названы внешними в противоположность объединениям, определенным стандартом SQL1, которые стали называться внутренними.
В общем случае синтаксис части FROM в стандарте SQL2 выглядит следующим образом:
FROM <список исходных таблиц> |
|
|
< выражение естественного объединения > |
< выражение объединения >
< выражение перекрестного объединения > |
< выражение запроса на объединение >
<список исходных таблиц>::= <имя_таблицы_1>
[ имя синонима таблицы_1] [...]
[,<имя_таблицы_п>[ <имя синонима таблицы_n> ] ]
<выражение естественного объединениям:: =
<имя_таблицы_1> NATURAL { INNER | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN <имя_таблицы_2>
<выражение перекрестного объединениям: = <имя_таблицы_1> CROSS JOIN <имя_таблицы_2>
<выражение запроса на объединением:=
<имя_таблицы_1> UNION JOIN <имя_таблицы_2>
<выражение объединениям:= <имя_таблицы_1> { INNER |
FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN {ON условие [USING (список столбцов)]} <имя_таблицы_2>
В этих определениях INNER — означает внутреннее объединение, LEFT — левое объединение, то есть в результат входят все строки таблицы 1, а части результирующих кортежей, для которых не было соответствующих значений в таблице 2, дополняются значениями NULL (неопределено). Ключевое слово RIGHT означает правое внешнее объединение, и в отличие от левого объединения в этом случае в результирующее отношение включаются все строки таблицы 2, а недостающие части из таблицы 1 дополняются неопределенными значениями, Ключевое слово FULL определяет полное внешнее объединение: и левое и правое. При полном внешнем объединении выполняются и правое и левое внешние объединения и в результирующее отношение включаются все строки из таблицы 1, дополненные неопределенными значениями, и все строки из таблицы 2, также дополненные неопределёнными значениями.
Ключевое слово OUTER означает внешнее, но если заданы ключевые слова FULL, LEFT, RIGHT, то объединение всегда считается внешним.
|
|
Рассмотрим примеры выполнения внешних объединений. Снова вернемся к БД «Сессия». Создадим отношение, в котором будут стоять все оценки, полученные всеми студентами по всем экзаменам, которые они должны были сдавать. Если студент не сдавал данного экзамена, то вместо оценки у него будет стоять неопределенное значение. Для этого выполним последовательно естественное внутреннее объединение таблиц R2 и R3 по атрибуту Группа, а полученное отношение соединим левым внешним естественным объединением с таблицей R1, используя столбцы ФИО и Дисциплина. При этом в стандарте разрешено использовать скобочную структуру, так как результат объединения может быть одним из аргументов в части FROM оператора SELECT.
SELECT Rl.ФИО, R1.Дисциплина. Rl.Оценка
FROM (R2 NATURAL INNER JOIN R3) LEFT JOIN Rl USING (ФИО. Дисциплина)
Результат:
ФИО | Дисциплина | Оценка | ||
Петров Ф. И. | Базы данных | |||
Сидоров К. А. | Базы данных | 4 | ||
Миронов Л. В. | Базы данных | |||
Степанова К. Е. | Базы данных | |||
Крылова Т. С. | Базы данных | |||
Владимиров В. А. | Базы данных | |||
Петров Ф. И. | Теория информации | Null | ||
Сидоров К. А. | Теория информации | |||
Миронов А. В. | Теория информации | Null | ||
Степанова К. Е. | Теория информации | |||
Крылова Т. С. | Теория информации | |||
Владимиров В. А. | Теория информации | Null | ||
Петров Ф. И. | Английский язык | |||
Сидоров К. А. | Английский язык | Null | ||
Миронов А. В. | Английский язык | Null | ||
Степанова К. Е. | Английский язык | Null | ||
Крылова Т. С. | Английский язык | Null | ||
Владимиров В. А. | Английский язык | |||
Трофимов П. А. | Сети и телекоммуникации | |||
Иванова Е. А. | Сети и телекоммуникации | |||
ФИО | Дисциплина | Оценка | ||
Уткина Н. В. | Сети и телекоммуникации | |||
Трофимов П. А. | Английский язык | |||
Иванова Е. А. | Английский язык | |||
Уткина Н. В. | Английский язык | Null | ||
Рассмотрим еще один пример, для этого возьмем БД «Библиотека». Она состоит из трех отношений, имена атрибутов здесь набраны латинскими буквами, что является необходимым в большинстве коммерческих СУБД.
BOOKS(ISBN, TITL. AUTOR. COAUTOR. YEARJZD, PAGES)
READER(NUM_READER. NAME_READER, ADRESS. HOOM_PHONE. WORK_PHONE. BIRTH_DAY)
EXEMPLARE (INV, ISBN, YES_NO. NUM_READER. DATE_IN. DATE_DUT)
Здесь таблица BOOKS описывает все книги, присутствующие в библиотеке, она имеет следующие атрибуты:
- ISBN — уникальный шифр книги;
- TITL — название книги;
- AUTOR — фамилия автора;
- COAUTOR — фамилия соавтора;
- YEARIZD — год издания;
- PAGES — число страниц.
Таблица READER хранит сведения обо всех читателях библиотеки, и она содержит следующие атрибуты:
- NUM_READER — уникальный номер читательского билета;
- NAME_READER — фамилию и инициалы читателя;
- ADRESS — адрес читателя;
- HOOM_PHONE — номер домашнего телефона;
- WORK_PHONE — номер рабочего телефона;
- BIRTH_DAY — дату рождения читателя.
Таблица EXEMPLARE содержит сведения о текущем состоянии всех экземпляров всех книг. Она включает в себя следующие столбцы:
- INV — уникальный инвентарный номер экземпляра книги;
- ISBN — шифр книги, который определяет, какая это книга, и ссылается на сведения из первой таблицы;
- YES_NO — признак наличия или отсутствия в библиотеке данного экземпляра в текущий момент;
- NUM_READER — номер читательского билета, если книга выдана читателю, и Null в противном случае;
- DATE_IN — если книга у читателя, то это дата, когда она выдана читателю; a DATE_OUT — дата, когда читатель должен вернуть книгу в библиотеку.
Определим перечень книг у каждого читателя; если у читателя нет книг, то номер экземпляра книги равен NULL. Для выполнения этого поиска нам надо использовать левое внешнее объединение, то есть мы берем все строки из таблицы READER и соединяем со строками из таблицы EXEMPLARE, если во второй таблице нет строки с соответствующим номером читательского билета, то в строке результирующего отношения атрибут EXEMPLARE.INV будет иметь неопределенное значение NULL:
|
|
SELECT READER.NAME_READER, EXEMPLARE.INV
FROM READER RIGHT JOIN EXEMPLARE ON READER.NUM_READER=EXEMPLARE.NUM_READER
Операция внешнего объединения, как мы уже упоминали, может использоваться для формирования источников в предложении FROM, поэтому допустимым будет, например, следующий текст запроса:
SELECT *
FROM (BOOKS LEFT JOIN EXEMPLARE)
LEFT JOIN (READER NATURAL JOIN EXEMPLARE)
USING (ISBN)
При этом для книг, ни один экземпляр которых не находится на руках у читателей, значения номера читательского билета и дат взятия и возврата книги будут неопределенными.
Перекрестное объединение в трактовке стандарта SQL2 соответствует операции расширенного декартова произведения, то есть операции соединения двух таблиц, при которой каждая строка первой таблицы соединяется с каждой строкой второй таблицы.
Операция запроса па объединение эквивалентна операции теоретико-множественного объединения в алгебре. При этом требование эквивалентности схем исходных отношений сохраняется. Запрос на объединение выполняется по следующей схеме:
SELECT - запрос
UNION SELECT - запрос
UNION SELECT - запрос
Все запросы, участвующие в операции объединения, не должны, содержать выражений, то есть вычисляемых полей.
Например, нужно вывести список читателей, которые держат на руках книгу «Идиот» или книгу «Преступление и наказание». Вот как будет выглядеть запрос:
SELECT READER. NAME_READER
FROM READER, EXEMPLARE.BOOKS
WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND
EXEMPLRE.ISBN = BOOKS.ISBN AND
BOOKS.TITLE = "Идиот"
UNION
SELECT READER.NAME_READER
FROM READER, EXEMPLARE,BOOKS
WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND
EXEMPLRE.ISBN = BOOKS.ISBN AND
BOOKS.TITLE = "Преступление и наказание"
По умолчанию при выполнении запроса на объединение дубликаты кортежей всегда исключаются. Поэтому, если найдутся читатели, у которых находятся на руках обе книги, то они все равно в результирующий список попадут только один раз.
|
|
Запрос на объединение может объединять любое число исходных запросов.
Так, к предыдущему запросу можно добавить еще читателей, которые держат на руках книгу «Замок»:
UNION
SELECT READER. NAME_READER
FROM READER. EXEMPLARE,BOOKS
WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND.
EXEMPLRE.ISBN = BOOKS.ISBN AND
BOOKS.TITLE = "Замок"
В том случае, когда вам необходимо сохранить все строки из исходных отношений, необходимо использовать ключевое слово ALL в операции объединения. В случае сохранения дубликатов кортежей схема выполнения запроса на объединение будет выглядеть следующим образом:
SELECT - запрос
UNION ALL
SELECT - запрос
UNION ALL
SELECT - запрос
Однако тот же результат можно получить простым изменением фразы WHERE первой части исходного запроса, соединив локальные условия логической операцией ИЛИ и исключив дубликаты кортежей.
SELECT DISTINCT READER.NAME_READER
FROM READER. EXEMPLARE.BOOKS
WHERE EXEMPLARE.NUM_READER= READER.NUM_READER AND
EXEMPLRE.ISBN = BOOKS.ISBN AND
BOOKS.TITLE = "Идиот" OR
BOOKS.TITLE = "Преступление и наказание" OR
BOOKS.TITLE = "Замок"
Ни один из исходных запросов в операции UNION не должен содержать предложения упорядочения результата ORDER BY, однако результат объединения может быть упорядочен, для этого предложение ORDER BY с указанием списка столбцов упорядочения записывается после текста последнего исходного SELECT-запроса.