Связанные подзапросы. Оператор EXISTS

Когда в SQL используются подзапросы во внутреннем вложенном запросе можно ссылаться на таблицу, имя которой указано в предложении FROM внешнего запроса, тем самым формируя связанный подзапрос.

Пример: Построить список студентов, сдававших экзамен 11/01/01

SELECT sname FROM студент

WHERE 11/01/01 IN

(SELECT odate FROM оценка WHERE snum = osnum);

Поскольку значение в поле snum внешние запросы изменяются, внутренний запрос должен выполнятся отдельно для каждой строки внешнего запроса. Таким образом схемы выполнения связанного подзапроса имеют вид:

• Из таблицы внешнего запроса выбирается очередная строка-кандидат
• Выполняется подзапрос с использованием значения строки-кандидата.
• Оцениваются условия внешнего запроса, по результатам чего строка-кандидат включается в результат или нет.
• Если не конец таблицы внешнего запроса, то переход к пункту 1.

Следует отметить, что связанные подзапросы являются очень мощным средством SQL, т.к. позволяют записать или получить результаты с использованием сложных функций при достаточно компактных командах.

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

Пример: получить оценки студентов, превышающие их средний балл.

SELECT * FROM оценка01

WHERE ocen >

(SELECT AVG(ocen) FROM оценка02

WHERE 02.osnum=01.osnum);

Связанные подзапросы могут использоваться в предложении HAVING при группировке данных.

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

SELECT opnum, AVG(ocen) FROM оценка01

GROP BY opnum

HAVING AVG(ocen)<

(SELECT MAX(ocen)-1 FROM оценка 02

WHERE 01.opnum=02.opnum);

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

При использовании вложенных подзапросов в условных выражениях внешнего подзапроса могут использоваться служебные операторы EXIST, ANY, ALL, COME.

EXIST – это оператор, генерирующий значения истина\ложь. Это значит, что его можно использовать самостоятельно в условиях или комбинировать с логическими выражениям с помощью AND, OR, NOT. Используя подзапрос в качестве аргумента, этот оператор возвращает истину, если результат подзапроса не пуст.

Пример: получить фамилии преподавателей, принимавших экзамен с 10/01/01 по 11/01/01.

SELECT pname FROM преподаватель

WHERE EXIST

(SELECT*FROM оценка

WHERE odate >=10/01/01 AND pnnum=opnum);

В этом запросе не имеет значения какие поля заданы в предложении SELECT внутреннего подзапроса. Важно в принципе наличие строк таблицы ОЦЕНКА, удовлетворяющих условию. Эту задачу можно решить и с помощью соединения таблиц:

SELECT DISTINCT FROM преподаватель, оценка

WHERE odate >=10/01/01 AND =<11/01/01 AND pnum=opnum;

Данный запрос выглядит короче, но с EXIST – эффективнее работает, по следующим причинам:

• Для каждой записи таблицы ПРЕПОДАВАТЕЛЬ, таблица ОЦЕНКА просматривается только до первого выполнения условия.
• Из выходных данных внешнего запроса не требуется удалять совпадающие значения.

Рассмотрим другой пример использования EXIST: получить коды преподавателей, имеющих более одного дипломника.

SELECT DISTINCT spdp FROM студент С1

WHERE EXIST

(SELECT FROM студент С2

WHERE C1.snum <> C2.snum

AND C1.spdp=C2.spdp);

EXIST также можно комбинировать соединениями.

Пример: получить фамилии преподавателей, имеющих более 1 дипломника.

SELECT DISTINCT pname

FROM преподаватель INNER JOIN

Студент С1 ON pnum-spdp

WHERE EXIST

(SELECT*FROM студент C2

WHERE C1.snum <> C2.snum

AND C1.spdp=C2.spdp);

Рассмотрим еще один пример вложенных подзапросов: получить фамилии преподавателей, имеющих студентов, которые сдавали экзамен более 1 раза.

SELECT pname FROM преподаватель

WHERE EXIST

(SELECT*FROM студент

WHERE 1<(SELECT count(*)

FROM оценка WHERE snum=osnum

AND pnum=opnum));