Использование вложенных запросов

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

SELECT * FROM USP

WHERE SNUM = (SELECT SNUM

FROM STUDENTS WHERE SFAM = 'Поляков');Чтобы выполнить основной запрос, SQL сначала должен оценить внутренний запрос (его называют подзапросом) внутри предложения WHERE. Происходит это традиционным образом, т.е. исполняется вложенный запрос, извлекающий необходимые для определения значения предиката данные, а только затем -основной. Разумеется, подзапрос должен выбрать только одно поле, а тип данных этого поля должен совпадать с тем значением, с которым он будет сравниваться в предикате.

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

Подзапрос установил, что значение поля TNUM совпало с фамилией Викулина при значении 4001, а затем основной запрос выделил все записи с этим значением TNUM из таблицы предметов. Т.к., вообще говоря, могло получиться, что преподаватель ведет несколько предметов, то фраза DISTINCT в данном случае обязательна - если подзапрос возвратил бы более одного значения, то это вызвало бы ошибку.

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

<ВЫРАЖЕНИЕ> <ОПЕРАТОР> <ПОДЭАПРОС>, и ни в коем случае не

<ПОДЗАПРОС> <ОПЕРАТОР> <ВЫРАЖЕНИЕ>,

или

<ПОДЗАПРОС> <ОПЕРАТОР> <ПОДЗАПРОС>,

Иначе говоря, предыдущий пример, записанный следующим образом:

SELECT *

FROM PREDMET WHERE (SELECT DISTINCT TNUM

FROM TEACHERS

WHERE TFAM = 'Никулина') = TNOM;

является неверным.

Средняя оценка по имеющимся данным составляет 4.2, следовательно, основной запрос выбирает только те записи, в которых значение поля OCENKA больше, чем 4.2.

Не стоит забывать, что сгруппированные агрегатные функции, определенные в терминах предложения GROUP BY, могут выдавать многочисленные значения, а значит, не допускаются в подзапросах такого характера. Даже если GROUP BY или HAVING используются так, что только одна группа значений выводится с помощью подзапроса, все равно команда будет отклонена. Кстати говоря, можно использовать подзапросы, которые производят любое число строк, если используется специальный оператор IN (операторы BETWEEN, LIKE, и IS NULL не могут использоваться с подзапросами). IN определяет набор значений предиката, одно из которых должно совпадать с другим по порядку. При использовании IN с подзапросом, SQL просто формирует этот набор из вывода подзапроса, а значит, допускается использование IN для того, чтобы выполнить такой же подзапрос. Например, запрос

SELECT *

EROM EREDMET WHERE PREDMET.TNUM IN (SELECT TEACHERS. TNUM FROM TEACHERS WHERE TEACHERS.TEAM

BETWEEN 'И' AND 'С');

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

SELECT PREDMET.PNUM, PREDMET.PNAME, TEACHERS.TNUM, PREDMET.HOURS, PREDMET. COURS FROM PREDMET, TEACHERS WHERE TEACHERS.TNUM = PREDMET.TNUM

AND TEACHERS. TFAM BETWEEN 'И' AND 'C;

Скорость выполнения того или иного запроса, например, как в рассмотренном случае, зависит от реализации той СУБД, которая для этого используется. Выше уже поднималась проблематика, связанная с работой оптимизатора запросов, который пытается найти наиболее эффективный способ выполнения. Традиционно хороший оптимизатор чаще всего преобразует вариант объединения в подзапрос, который обычно выполняется быстрее.

И еще один интересный момент: в любой ситуации, где применяется реляционный оператор равенства (=), можно использовать IN. В отличие от первого, IN не может заставить запрос потерпеть неудачу, если подзапросом выбрано больше чем одно значение. Это может быть или преимуществом или недостатком. Например, уже рассмотренный нами выше запрос можно переписать следующим образом:

SELECT *

FROM PREDMET WHERE TNUM IN (SELECT TNUM

FROM TEACHERS WHERE TPAM = 'Викулина');

Таким образом, подзапросы всегда определяют одиночные столбцы - это обязательно, поскольку выбранный вывод сравнивается с одиночным значением. Подтверждением этому является то, что команда SELECT * не может использоваться в подзапросе. В подзапросе допускается использовать выражение, основанное на поле, а не просто само поле, в предложении SELECT. Это может быть' выполнено с помощью реляционных операторов или при использовании IN. Примером может служить следующий запрос:

select *

FROM PREDMET WHERE ENUM =

(SELECT PNUM-1 FROM PREDMET WHERE PNAME = ' Философия');

Этот запрос находит информацию об учебном предмете, код которого на 1 меньше кода философии. Разумеется, поле PNUM не должно содержать повторяющихся значений, иначе запрос вызовет ошибку.

Можно также использовать подзапросы внутри предложения HAVING. Эти подзапросы могут использовать свои собственные агрегатные функции, если они не производят многочисленных значений, или использовать GROUP BY или HAVING. Например, рассмотрим запрос:

SELECT OCENKA, COUNT (DISTINCT SNUM) FROM USP

GROUP BY OCENKA HAVING OCENKA > =

(SELECT AVG (OCENKA) FROM USP WHERE PNUM = 2003);

Вывод для этого запроса следующий:

OCENKA

5 2

4 2

Данный запрос подсчитывает количество студентов с оценками выше средней, чем по дисциплине с PNUM = 2003.

Теперь несколько слов об объединении запросов и использовании при этом подзапросов. Операция, которая бывает часто полезна - это объединение из двух запросов, в котором второй запрос выбирает строки, исключенные первым. Наиболее часто это делают для того, чтобы не исключать строки, которые не удовлетворили предикату при объединении таблиц. Этот процесс принято называть внешним объединением.

Предположим, что некоторые из студентов еще не получили оценку, однако уже внесены в таблицу USP. Например, в эту таблицу добавлена запись {1006, NULL, NULL, 3416, NULL}. Если возникает необходимость в просмотре успеваемости студентов по дисциплине, не учитывая тех, кто еще не получил оценку. Этого можно достичь, формируя объединение из двух запросов, один из которых выполняет объединение, а другой выбирает студентов с NULL значениями поля OCENKA. Этот последний запрос должен вставлять сообщение в поля, соответствующие полю PNAME, и значение 0 в поле OCENKA в первом запросе. Как было рассмотрено ранее, можно вставлять текстовые строки в вывод, чтобы идентифицировать запрос, который вывел данную строку. Использование этой методики во внешнем объединении дает возможность применять предикаты для классификации, а не для исключения. Следующий запрос выполняет эти действия:

SELECT USP.SNUM, STUDENTS.SFAM,

PREDMET.PNAME, USP.OCENKA FROM USP, STUDENTS, PREDMET

WHERE USP.SNUM = STUDENTS.SNUM

AND USP.PNUM = PREDMET.PNUM UNION SELECT USP.SNUM, STUDENTS.SFAM,

'НЕТ ', 0 FROM USP, STUDENTS

WHERE USP.SNUM = STUDENTS.SNUM AND NOT USP.OCENKA = ANY (SELECT OCENKA

FROM USP) ORDER BY 2 ASC;

Вывод этого запроса следующий:

3414 Гриценко Экономика 3

3416 Нагорный Философия 5

3416 Нагорный НЕТ 0

3412 Поляков Фиалка 5

3412 Поляков Математика 4

3413 Старова Математика 4

Обратите внимание на то, что строка ▒ НЕТ ▓ была дополнена пробелами, чтобы получить совпадение поля PNAME по длине. Второй запрос выбирает даже те строки, которые были исключены первым.