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

Теперь вернемся к БД «Сессия» и рассмотрим на ее примере использование вложенных запросов.

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

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

FROM Rl AS A, Rl AS В

ИЛИ

FROM Rl A. Rl В:

оба выражения эквивалентны и рассматриваются как применения оператора SELECT к двум экземплярам таблицы R1.

Например, покажем, как выглядят на SQL некоторые запросы к БД «Сессия»:

Список тех, кто сдал все положенные экзамены.

SELECT ФИО

FROM Rl as a

WHERE Оценка > 2

GROUP BY ФИО

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

FROM R2.R3

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

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

Список тех, кто должен был сдавать экзамен по БД, но пока еще не сдавал.

SЕLЕСТ ФИО

FROM R2 a, R3

WHERE R2.Fpynna=R3.Группа AND Дисциплина = "БД" AND NOT EXISTS

(SELECT ФИО FROM Rl WHERE ФИО=а.ФИО AND Дисциплина = "БД")

Предикат EXISTS ( SubQuery) истинен, когда подзапрос SubQuery не пуст, то есть содержит хотя бы один кортеж, в противном случае предикат EXISTS ложен.

Предикат NOT EXISTS обратно — истинен только тогда, когда подзапрос SubQuery пуст.

Обратите внимание, каким образом NOT EXISTS с вложенным запросом позволяет обойтись без операции разности отношений. Например, формулировка запроса со словом «все» может быть выполнена как бы с двойным отрицанием. Рассмотрим пример базы, которая моделирует поставку отдельных деталей отдельными поставщиками, она представлена одним отношением SP «Поставщики—детали» со схемой

SP (Номер_поставщика. номер_детали) Р (номер_детали. наименование)

Вот каким образом формулируется ответ на запрос: «Найти поставщиков, которые поставляют все детали».

SELECT DISTINCT НОМЕР_ПОСТАВЩИКА FROM SP SP1 WHERE NOT EXISTS

(SELECT номер_детали

FROM P WHERE NOT EXISTS

(SELECT * FROM SP SP2

WHERE SР2.номер_поставщика=SР1.номер_поставщика AND

sр2.номер_детали = Р.номер_детали)):

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

SELECT DISTINCT Номер_поставщика

FROM SP

GROUP BY Номер_поставщика

HAVING CounKDISTINCT номер_детали) =

(SELECT Count( номер_детали)

FROM P)

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

Ключевое слово ANY, поставленное в любом предикате сравнения, означает, что предикат будет истинен, если хотя бы для одного значения из подзапроса предикат сравнения истинен. Ключевое слово ALL требует, чтобы предикат сравнения был бы истинен при сравнении со всеми строками подзапроса.

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

R1 = (ФИО, Дисциплина, Оценка);

R2 = (ФИО, Группа);

R3 = (Группы, Дисциплина )

R4 = (ФИО, Дисциплина, Номер_лаб_раб, Оценка);

Select R1.ФИО From R1 Where 4 > = All (Select Rl.Оценка

From Rl as R11

Where R1.Фио = R11.Фио)

Рассмотрим еще один пример:

Выбрать студентов, у которых оценка по экзамену не меньше, чем хотя бы одна оценка по сданным им лабораторным работам по данной дисциплины:

Select R1.Фио

From R1 Where R1.Оценка >= ANY (Select R4.Оценка

From R4

Where Rl.Дисциплина = R4. Дисциплина AND R1.Фио = R4.Фио)

Содержание раздела