4.3. Вложенные запросы (подзапросы)

Возможности SQL позволяют вкладывать одни запросы в другие. При этом внутренние запросы обычно генерируют результаты, которые проверяются на истинность в предложениях WHERE и HAVING.

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

SELECT Cname FROM Student

WHERE Ndep=( SELECT Ndep FROM Depart

WHERE Cname LIKE ‘Матем%’ )

Cname

Отличников К.Л.

Дубинин У.У.

Мозгов О.Г.

В первую очередь при выполнении команды выполняется внутренний запрос (подзапрос), помещенный в предложении WHERE внешнего запроса и на основе полученных результатов происходит выполнение внешнего (основного) запроса. Отметим, что в приведенном примере подзапрос должен выдать в качестве результата единственное числовое значение - код факультета. Если данное условие не будет выполнено, то операция сравнения значения поля Ndep и результата подзапроса не может быть выполнена корректно, что приведет к возникновению ошибки. В силу вышесказанного в подзапросах нельзя использовать предложение GROUP BY, хотя запросы такого рода могут давать и единственное значение.

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

SELECT A.Cname, B.Cname FROM Student A, Teacher B

WHERE A.Nteach=B.Nteach AND

A.Ndep IN ( SELECT Ndep FROM Depart

WHERE (Cname LIKE ‘Матем%’)

OR

(Cname LIKE ‘Экон%’) )

Cname_A Cname_B

Отличников К.Л. Поляков В.В.

Мозгов О.Г. Кузнецов В.А.

Хвостов П.П. Дмитриева Л.Н.

Дубинин У.У. Дмитриева Л.Н.

Двоечников Н.Е. Дмитриева Л.Н.

Отметим, что при формировании подзапросов, согласно соглашениям ANSI SQL, первым указывается скалярное значение - имя поля, выражение или константа, которое может сравниваться с результатами подзапроса, но не наоборот. Так, запрос на поиск студентов факультета недопустимо записать в форме:

SELECT Cname FROM Student

WHERE ( SELECT Ndep FROM Depart

WHERE Cname LIKE 'Матем%' )=Ndep

Также запрещается использовать в сравнении результаты двух подзапросов.

Если в подзапросах не допускается использовать предложения GROUP BY и HAVING, то использование подзапросов совместно с HAVING вполне допустимо. Продемонстрируем такую возможность, а также использование агрегатных функций в подзапросах HAVING - осуществим выбор факультетов, для которых минимальное значение показателя интеллекта студента выше среднего значения по всем факультетам:

SELECT A.Cname FROM Student, Depart A

WHERE Student.Ndep=Depart.Ndep

GROUP BY Ndep

HAVING MIN(Niq)>( SELECT AVG(Niq) FROM Student )

Примечание: в FoxPro подзапросы в предложении HAVING не поддерживаются.

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

SELECT * FROM Teacher A

WHERE Nteach IN ( SELECT Nteach FROM Subject

WHERE Nhour>40

AND A.Nteach=Nteach )

Nteach Cname Ndep Npost

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

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

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

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

Кажется, что тот же результат можно получить, используя следующую команду:

SELECT * FROM Teacher A, Subject B

WHERE B.Nhour>40 AND A.Nteach=B.Nteach

Nteach_A Cname_A Ndep Npost Nsub Cname_B Nhour Nteach_B

210 Поляков В.В. 100 30 103 Информационные системы 90 210

210 Поляков В.В. 100 30 317 Экономическая информатика 80 210

220 Кузнецов В.А. 100 30 42 Исследование операций 60 220

410 Дмитриева Л.Н. 300 50 302 Бухгалтерский учет 50 410

420 Литвинас А.П. 300 40 305 Экономическая география 90 420

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

SELECT DISTINCT A.* FROM Teacher A, Subject B

WHERE B.Nhour>40 AND A.Nteach=B.Nteach

и результат будет получен в необходимом виде.

Здесь задание списка полей в виде A.* означает необходимость включения в результирующую таблицу всех полей исходной таблицы Teacher, но не включение ни одного поля Subject. Использование DISTINCT исключает дублирование в результирующей таблице строк с полностью одинаковыми значениями полей.

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

SELECT Subject.Nteach вместо SELECT Nteach

и

A.Nteach= Subject.Nteach вместо A.Nteach=Nteach.

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

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

SELECT DISTINCT Nteach FROM Subject A

WHERE Nteach=( SELECT DISTINCT Nteach FROM Subject B

WHERE A.Nteach=B.Nteach

AND A.Cname<>B.Cname )

Nteach

210

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

1. Сформулировать запрос, который позволит выявить все предметы, занятия по которым ведут преподаватели в должности “доцент”. При работе с таблицей Post использовать подзапрос.

2. Сформулируйте запрос, который выявит все предметы, объем занятий по которым выше среднего по всем предметам.

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