Вложенные и коррелированные запросы

Объединение таблиц

В первоначальном SQL при объединении отношений (установки связи между отношениями) использовались только условия, задаваемые в части WHERE оператора SELECT. В современном SQL синтаксис фразы FROM выглядит следующим образом:

FROM <список исходных таблиц> |

< выражение естественного объединения > |

< выражение объединения >

< выражение перекрестного объединения > |

< выражение запроса на объединение >

<список исходных таблиц> это есть

<имя_таблицы_1> [ имя синонима таблицы_1] [...]

[,<имя_таблицы_n>[ <имя синонима таблицы_n> ] ]

Выражение естественного объединениям:

<имя_таблицы_1> NATURAL { INNER | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN <имя_таблицы_2>

Выражение объединениям:

<имя_таблицы_1>

{ INNER |FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]}

JOIN {ON условие [USING (список столбцов)]} <имя_таблицы_2>

Выражение перекрестного объединениям:

<имя_таблицы_1> CROSS JOIN <имя_таблицы_2>

Выражение запроса на объединением:

<имя_таблицы_1> UNION JOIN <имя_таблицы_2>

В этих определениях:

INNER – означает внутреннее объединение.

LEFT – левое объединение, то есть в результат входят все строки таблицы 1, а части результирующих кортежей, для которых не было соответствующих значений в таблице 2, дополняются значениями NULL (неопределено).

RIGHT – правое внешнее объединение, и в отличие от левого объединения в этом случае в результирующее отношение включаются все строки таблицы 2, а недостающие части из таблицы 1 дополняются неопределенными значениями.

FULL – определяет полное внешнее объединение: выполняются и правое и левое внешние объединения и в результирующее отношение включаются все строки из таблицы 1, дополненные неопределенными значениями, и все строки из таблицы 2, также дополненные неопределёнными значениями.

OUTER – означает внешнее, но если заданы ключевые слова FULL, LEFT, RIGHT, то объединение всегда считается внешним.

Рассмотрим примеры выполнения внешних объединений на примере БД «Сессия».

Пример. Создать отношение, в котором будут стоять все оценки, полученные всеми студентами по всем экзаменам, которые они должны были сдавать. Если студент не сдавал данного экзамена, то вместо оценки у него будет стоять неопределенное значение. Для этого выполним последовательно естественное внутреннее объединение таблиц R2 и R3 по атрибуту Группа, а полученное отношение соединим левым внешним естественным объединением с таблицей R1, используя столбцы ФИО и Дисциплина.

SELECT R1.ФИО, R1.Дисциплина. R1.Оценка

FROM (R2 NATURAL INNER JOIN R3)

LEFT JOIN R1 USING (ФИО. Дисциплина)

Пример. Возьмем БД «Библиотека». Она состоит из трех отношений, имена атрибутов здесь набраны латинскими буквами, что является необходимым в большинстве коммерческих СУБД.

Tаблица BOOKS описывает все книги, присутствующие в библиотеке, она имеет следующие атрибуты:

ISBN – уникальный шифр книги;

TITL – название книги;

AUTOR – фамилия автора;

Таблица READER хранит сведения обо всех читателях библиотеки, и она содержит следующие атрибуты:

NUM_READER – уникальный номер читательского билета;

NAME_READER – фамилию и инициалы читателя;

Таблица EXEMPLARE содержит сведения о текущем состоянии всех экземпляров всех книг. Она включает в себя следующие столбцы:

INV – уникальный инвентарный номер экземпляра книги;

ISBN – шифр книги, который определяет, какая это книга, и ссылается на сведения из первой таблицы;

NUM_READER – номер читательского билета, если книга выдана читателю, и Null в противном случае;

Определим перечень книг у каждого читателя; если у читателя нет книг, то номер экземпляра книги равен 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)

При этом для книг, ни один экземпляр которых не находится на руках у читателей, значения номера читательского билета и дат взятия и возврата книги будут неопределенными.

Перекрестное объединение соответствует операции расширенного декартова произведения, то есть операции соединения двух таблиц, при которой каждая строка первой таблицы соединяется с каждой строкой второй таблицы.

Рассмотри выражение запроса на объединение.

Две таблицы совместимы по объединению и к ним может быть применен оператор UNION когда они имеют одинаковое число столбцов, объединяемые столбцы имеют в точности одинаковый тип данных и допустимое состояние обязательного заполнения (NULL).

Пример. Выдать номера деталей, которые имеют вес более 16 фунтов либо поставляются поставщиком S2.

SELECT НОМЕР_ДЕТАЛИ FROM P WHERE BEC>16
UNION SELECT НОМЕР_ДЕТАЛИ FROM SP WHERE НОМЕР_ПОСТАВЩИКА ='S2';

Следует иметь ввиду, что избыточные дубликаты всегда исключаются, из результата UNION и любое число предложений SELECT может быть соединено операторами UNION.

Любая фраза ORDER BY в запросе должна входить как часть только в последнее предложение SELECT и должна указывать столбцы, по которым осуществляется упорядочение.

Когда строковая константа выступает в качестве элемента, подлежащего выборке, считается, что она имеет тип VARCHAR и длину, равную числу литер в константе, и допускаются неопределенные значения.

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

SELECT Р.НОМЕР_ДЕТАЛИ, 'вес в граммах = ', Р. ВЕС*454, Р.ЦВЕТ *
'максимальный объем поставки = ', MAX (SP.КОЛИЧЕСТВО)
FROM P, SP
WHERE Р.НОМЕР_ДЕТАЛИ = SP.НОМЕР_ДЕТАЛИ AND

P. ЦВЕТ IN ('Красный', 'Голубой') AND SP.КОЛИЧЕСТВО > 200
GROUP BY Р. НОМЕР_ДЕТАЛИ, Р. ВЕС, Р. ЦВЕТ
HAVING SUM (КОЛИЧЕСТВО) > 350
ORDER BY 6, P. НОМЕР_ДЕТАЛИ DESC;

С помощью SQL можно вкладывать запросы внутрь друг друга. Обычно внутренний (вложенный) запрос генерирует значение, которое проверяется в условии внешнего запроса (в фразе WHERE или HAVING), определяющего, верно оно или нет. Совместно с подзапросом можно использовать предикат EXISTS, который возвращает истину, если вывод подзапроса не пуст. Подзапрос заключается в круглые скобки.

Пример. Вывести крупные стройки со сметой выше средней по стройкам:

SELECT * FROM Stroiki WHERE Ss > (SELECT AVG(Ss) FROM Stroiki).

Пример. Вывести из базы «Сессия» список тех, кто сдал все положенные экзамены.

SELECT ФИО

FROM R1

WHERE Оценка > 2

GROUP BY ФИО

HAVING COUNT(*) = (SELECT COUNT(*)

FROM R2, R3

WHERE R2.Группа=R3.Группа AND R2.ФИО=R1.ФИО)

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

Особенностью этого запроса является то, что в подзапросе используется поле (R1.ФИО) таблицы R1, которая указана во внешнем запросе. Такой подзапрос называется коррелированным. В этом случае подзапрос выполняется для каждой текущей записи таблицы R1 (что может существенно увеличить время выполнения всего запроса – подза*прос будет выполняться для каждого студента; лучшим решением является оформление подзапроса в форме представления, в котором выводятся число всех экзаменов по группам). Например, если первая запись таблицы R1 содержит значения: Иванов, Информатика и 4, то выполнится подзапрос, в котором будут связаны таблицы R2 и R3 по полю Группа в которой учится Иванов (можно заметить, что связь таблиц R1 и R2 по полю ФИО не допускает полных тезок, лучше связывать по учетным номерам студентов). В результате, определится число экзаменов в группе, в которой учится Иванов и далее, выполнится группировка по Иванову и посчитается число положительных оценок за экзамены и если это число совпадет с числом экзаменов по группе, то фамилия Иванова выведется в результирующую таблицу запроса.

Можно заметить, что фразы FROM и WHERE можно написать более современно, используя фразу установки связи между таблицами – JOIN:

FROM R2, R3 INNER JOIN ON (R2.Группа=R3.Группа)

WHERE R2.ФИО=R1.ФИО

Дата добавления: 2014-12-10