Подзапросы

Подзапрос – это команда SELECT, вложенная в предложение другой команды SQL. Подзапросы могут использоваться в командах SELECT, UPDATE, INSERT, DELETE, CREATE TABLE. Например, каждая команда SELECT может включать в себя несколько других команд SELECT^[6]. При этом подзапрос (внутренний запрос) генерирует значение, которое проверяется в предикате внешнего запроса. Подзапросы всегда выполняются от внутренних к внешнему, если только не являются коррелированными. Подзапрос может возвращать одну и более строк или один и более столбцов.

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

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

3. Подзапрос может задаваться в сложных критериях поиска внешних запросов с использованием логических связок AND и OR.

4. Предложение ORDER BY ставится последним в основном запросе и не может содержаться в подзапросе.

5. В команде SELECT подзапрос может стоять в предложениях FROM, WHERE, HAVING.

6. Подзапрос может содержать группы и групповые функции.

7. Имена столбцов в предложении SELECT внутреннего запроса должны стоять в той же последовательности, что и имена столбцов в левой части оператора сравнения внешнего запроса. Типы столбцов должны попарно соответствовать.

8. В критерии поиска могут использоваться логические операторы, операторы ANY (SOME), ALL.

Подзапрос на уровне предложения WHERE

Пример 69

Задача.

Вывести имена студентов и их оценки, если оценка меньше средней по университету.

Решение.

SELECT StName, Mark

FROM Student S,Progress P

WHERE S.NRecordBook=P. NRecordBook

AND Mark<(SELECT AVG(Mark)

FROM Progress)

Результат реализации запроса:

Подзапрос вычисляет среднюю оценку и подставляет высчитанное значение в предложение WHERE внешнего запроса.

Коррелированные подзапросы на уровне предложения WHERE

Коррелированные подзапросы – это вложенные подзапросы. Они выполняются для каждой строки главного запроса.

Последовательность выполнения коррелированного подзапроса:

· внешний запрос выбирает строку;

· выполняется внутренний запрос, используя значение строки внешнего запроса;

· результат выполнения внутреннего запроса возвращается во внешний запрос, где проверяется его соответствие выбранной строке;

· выбирается следующая строка внешнего запроса.

При задании вложенных запросов допускаются применение операторов АNY, EXISTS, ALL и логических операторов.

Пример 70

Задача.

Вывести имена студентов, чьи оценки выше, чем средняя оценка в их группе.

Решение:

SELECT StName,Mark

FROM Student s,Progress p

WHERE S.NRecordBook=P.NRecordBook

AND Mark>(SELECT AVG(Mark)

FROM Progress P1,Student S1

WHERE S1.IDGroup=S.IDGroup

AND S1.NRecordBook=P1.NRecordBook)

Результат реализации запроса:

Рисунок 3

Из примера становится очевидным, что коррелированные запросы следует применять только в случае крайней необходимости, так как производительность их мала. Например, при реализации запроса средняя оценка по группе вычисляется столько раз, сколько раз информация о группе встречается в первом запросе. Далее (см. Пример 75) приведен пример использования коррелированного подзапроса в команде UPDATE.

Задание 22

Вывести имя студента, название предмета и оценку студентов для тех студентов, у которых оценка по той или иной дисциплине выше средней оценки по этой самой дисциплине.

Задание 23

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

Задание 24

Вывести имена студентов, у которых средняя оценка равна средней оценке по группе, в которой учится студент.

Подзапрос на уровне предложения HAVING

Пример 71

Задача.

Вывести имена студентов, у которых средняя оценка, выше средней по университету.

Решение.

Было бы ошибочно использовать следующий синтаксис команды. Выше уже говорилось о том, что отбор групп по условию возможен только в предложении HAVING.

SELECT StName, AVG(Mark)

FROM Student s,Progress p

WHERE s.NRecordBook =p. NRecordBook

AND AVG(Mark) >(SELECT AVG(Mark)