История развития баз данных. Основные понятия и определения. Архитектура базы данных. Физическая и логическая независимость, страница 16

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

Пример:

Пусть требуется получить фамилии дипломников для определённой кафедры:

SELECT sname, sgrp FROM студентWHERE  spdp IN (SELECT pnum FROM Преподаватель WHERE pcaf = «K-1»);

Результат:

sname	sgrp
C-1	Г-1
C-2	Г-2
C-3	Г-2

Когда IN используется в подзапросе, множество значений IN строится по результатам подзапроса. Это позволяет во многих случаях оптимизировать запросы, отказавшись от построения декартова произведения таблиц. Так, предыдущий запрос можно было бы записать по-другому:

SELECT sname FROM студент, Преподаватель WHERE spdp = pnum AND pcaf = «K-1»;

Также возможен и такой вариант:

SELECT sname FROM студент INNER JOIN Преподаватель ON spdp = pnum WHERE  pcaf = «K-1»;

Оба варианта дадут верный запрос, но их использование менее эффективно, чем использование подзапросов. Допустим, для этого примера, что в таблице «Стдунты» n записей, а в таблице «Преподаватель» m записей. Для последнего примера условие spdp = pnumWHEREpcaf = «K-1» проверяется n*m количество раз, т.е. для всевозможного количества пар. В результате с подзапросом требуется однократная проверка таблицы «Преподаватель», по которому строится подмножество значений кодов преподавателя.

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

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

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

Пример:

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

SELECT sname FROM студентWHERE  #11/01/01# IN  (SELECT odate  FROM оценка WHERE snum = osnum);

Результат:

sname

C-2

C-3

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

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

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

Пример:

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

SELECT * FROM оценка 1 WHERE  ocen >  (SELECT AVG (ocen) FROM оценка 02 WHERE 02.osnum =01.osnum );

Результат:

opnum	sname	odate	ocen
003	003	13/01/01	4

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

Пример:

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

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