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

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

Sname

C-1

C-2

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

SELECT sname FROM СТУДЕНТ

WHERE spdp=

(SELECT pnum FROM ПРЕПОДАВАТЕЛЬ

WHERE pname = ”П-1”);

В данном примере, чтобы выполнить основной (внешний) запрос SQL сначала выполняет внутренний запрос (подзапрос) в предложении WHERE основного запроса.

В результате будет получен код преподавателя с его фамилией. Затем выполняется основной запрос с предложением WHERE.

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

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

SELECT sname FROM СТУДЕНТ

WHERE spdp=

(SELECT pnum FROM ПРЕПОДАВАТЕЛЬ

WHERE Pcaf = “K-1”);

Запрос такого типа приведет к ошибке, потому что будет возвращать два значения. Иногда проблему единственности можно решить с помощью параметра DISTINCT в предложении SELECT.

Пример: получить фамилию преподавателя, принимавшего экзамен 11/01/01.

Pname

П-1

SELECT pname FROM ПРЕПОДАВАТЕЛЬ

WHERE pnum=

(SELECT DISTINCT opnum

FROM оценка WHERE odate = “11/01/01”);

Без параметра DISTINCT запрос был бы ошибочным. Потому что возвращал бы 2 значения, хотя и совпадающие. С помощью DISTINCT эта ошибка устраняется. Однако, в данном примере возможна ошибка, если 2 преподавателя принимают экзамен в один день. Следует отметить, что по стандарту SQL логические выражения с использование подзапросов, должны иметь вид:

WHERE скалярное_выражение = (подзапрос)

а не наоборот.

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

Пример: получить коды студентов, имеющих у преподавателя П-1 оценки не ниже среднего балла, полученного всеми студентами у этого преподавателя.

SELECT osnum, odate,ocen FROM оценка

WHERE ocen >=

osnum	Odate	Ocen
001	10/01/01	5
002	11/01/01	4

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

WHERE opnum=

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

WHERE pname = “П-1”))

AND opnum =

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

WHERE pname = “П-1”);

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

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

sname	Sgrp
C-1	Г-1
C-2	Г-2
C-3	Г-2

SELECT sname, sgrp FROM студент

WHERE spdp IN

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

WHERE pcaf = “K-1”);

Когда 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-записей. Для последнего примера, условие spd=pnum WHERE pcaf = “K-1” проверяется n*m количество раз.

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