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

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

SELECT *

FROM SUBJECT SU WHERE 20/01/1999 IN

(SELE EXAM DATE PROM EXAM MARKS EX WHERE SU.SUBJ ID = EX.SUBJ ID);

В некоторых СУБД дя выполнения этого запроса может потребоваться преобразование значения даты в символьный тип. В приведенном запросе su и ЕХ являются псевдонимами (алиасами), то есть специально вводимыми именами, которые могут быть использованы в данном запросе вместо настоящих имен. В приведенном примере они используются вместо имен таблиц SUBJECT и EXAM MARKS.

Эту же задачу можно решить с помощью операции соединения таблиц:

SELECT DISTINCT SU.SUBJ ID, SUBJJJAME, HOUR, SEMESTER PROM SUBJECT FIRST,EXAM MARKS SECOND

WHERE FIRST.SUBJ ID = SECOND.SUBJ ID AND SECOND.EXAM DATE = 20/01/1999;

В этом выражении алиасами таблиц являются имена FIRST

И SECOND.

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

SELECT DISTINCT STUDENT ID,SURNAME, STIPEND FROM STUDENT El WHERE STIPEND >

(SELECT AVG(STIPEND) FROM STUDENT E2 WHERE El.KURS = E2.KURS);

Тот же результат можно получить с помощью следующего запроса:

SELECT DISTINCT STUDENT ID, SURNAME,STIPEND

FROM STUDENT El,

(SELECT KURS, AVG (STIPENDj AS AVG STIPEND

FROM STUDENT E2

GROUP BY E2.KURS) E3 WHERE El.STIPEND > AVG STIPEND AND El.KURS=E3.KURS;

Обратите внимание - второй запрос будет выполнен гораздо быстрее. Дело в том, что в первом варианте запроса агрегирующая функция AVG выполняется над таблицей, указанной в подзапросе, для каждой строки внешнего запроса. В другом варианте вторая таблица (алиас Е2) обрабатхвается агрегирующей функцией один раз, в результате чего формируется вспомогательная таблица (в запросе она имеет алиас ЕЗ), со строками которой затем соединяются строки первой таблицах (алиас Е1). Следует иметь в виду, что реальное время выполнения запроса в большой степени зависит от оптимизатора запросов конкретной СУБД.

2.10. Связанные подзапросы в HAVING

В разделе 2.4 указывалось, что предложение GROUP BY позволяет группировать вхводимые SELECT-запросом записи по значению некоторого поля. Использование предложения HAVING

2.10. Связанные подзапросы в HAVING 55

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

Пусть, например, необходимо по данным из таблицы EXAMMARKS определить сумму полученнхх студентами оценок (значений поля MARK), сгруппировав значения оценок по датам экзаменов и исключив те дни, когда число студентов, сдававших в течение дня экзамены, бхло меньше 10.

SELECT EXAM DATE, SUM(MARK)

FROM EXAM MARKS A GROUP BY EXAM DATE HAVING 10 <

(SECT COUNT(MARK) FROM EXAM MARKS В

WHERE A.EXAM DATE = В.EXAM DATE),

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

Упражнения

1. Напишите запрос с подзапросом для получения данных обо всех

оценках студента с фамилией Иванов . Предположим, что его персональный номер неизвестен. Всегда ли такой запрос будет корректным?

2. Напишите запрос, выбирающий данные об именах всех студентов, имеющих по предмету с идентификатором 101 балл выше общего среднего балла.

3. Напишите запрос, который выполняет выборку имен всех студентов, имеющих по предмету с идентификатором 102 балл ниже общего среднего балла

4. Напишите запрос, выполняющий вывод количества предметов, по котор1м экзаменовался каждый студент, сдававший более 20 предметов.

5. Напишите команду SELECT, использующую связанные подзапросы и выполняющую вывод имен и идентификаторов студентов,