4.4. Специальные операторы для обработки подзапросов

Существует несколько специальных операторов, использующих подзапросы в качестве аргументов и позволяющих оценить результаты их выполнения:

EXIST - фиксирует наличие в таблице, являющейся результатом подзапроса, хотя бы одной строки. Если подзапрос порождает выходные данные (строки, хотя бы одну), результатом оператора EXIST будет значение “истина”, иначе - “ложь”.

ANY(SOME) - фиксирует наличие в подзапросе хотя бы одного значения поля, удовлетворяющего некоторому условию. Если такое значение существует, в качестве результата выполнения ANY возвращается “истина”, в противном случае - “ложь”. Подзапрос обязательно должен возвращать таблицу, строки которой включают только одно поле того типа, которое позволит корректно выполнить сравнение. Операции ANY и SOME полностью эквивалентны.

ALL - используется аналогично ANY, но возвращает значение “истина” в том случае, когда значение поля каждой строки таблицы, сформированной в качестве подзапроса, удовлетворяет заданному условию.

Приведем пример двух различных запросов, которые позволяют получить одинаковый результат - выявить в списке преподавателей всех, являющихся лекторами (т.е. тех, с кем связаны дисциплины в списке предметов - таблице subject).

Запрос:

SELECT * FROM Teacher

WHERE Nteach=ANY ( SELECT Nteach FROM Subject )

и запрос:

SELECT * FROM Teacher

WHERE EXIST ( SELECT Nteach FROM Subject

WHERE Teacher.Nteach=Subject.Nteach )

дадут одинаковый результат:

Nteach Cname Ndep Npost

210 Поляков В.В. 100 30

220 Кузнецов В.А. 100 30

230 Бердино Н.Ю. 100 20

310 Стефанович Г.Б. 200 30

410 Дмитриева Л.Н. 300 50

420 Литвинас А.П. 300 40

А вот пример запроса с использованием оператора ALL. Запрос позволяет выбрать предметы с наибольшим объемом занятий:

SELECT Cname,Nhour FROM Subject

WHERE Nhour>=ALL ( SELECT Nhour FROM Subject )

Cname Nhour

Информационные системы 90

Экономическая география 90

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

Поскольку мы используем связанный подзапрос, он выполняется для каждой строки внешней таблицы, то есть таблицы, являющейся результатом внешнего запроса. Если результатом подзапроса в каждом случае будет не пустая таблица (в случае, если задано существующее наименование факультета, результирующая таблица должна содержать одну строку), то по найденному коду выбираются и включаются в результат строки, получившиеся сцеплением двух строк таблиц Subject и Teacher, у которых совпадают коды преподавателей, причем код факультета, на котором работает преподаватель (из таблицы Teacher) должен совпадать с кодом факультета, найденного в подзапросе.

SELECT Subject.Cname FROM Subject,Teacher

WHERE Subject.Nteach=Teacher.Nteach AND EXIST

( SELECT Ndep FROM Depart

WHERE Teacher.Ndep=Ndep AND

Cname='Математический' )

Результат запроса:

Cname

Информационные системы

Исследование операций

Практикум на ЭВМ

Экономическая информатика

Упражнения к разделу 4.4

1. Сформулировать запрос, который позволит получить список студентов, научными руководителями которых являются преподаватели, ведущие занятия по предметам, в названиях которых есть фрагменты ‘эконом’ или ‘бух’. При работе с таблицей Subject использовать подзапрос.

2. Сформулировать запрос, позволяющий выбрать студентов с наибольшим показателем интеллекта. Использовать в запросе оператор ALL.

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