Вложенные подзапросы. Использование оператора exists.

SQL позволяет использовать одни запросы внутри других.

Пример: известна фамилия студента (петров), но неизвестно значение его идентификатора

Select *

From exam_mark

Where student_id=

(Select student_id

From student

Where surname='Петров');

Алгоритм работы запроса sql со связанным подзапросом

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

2. Полученное значение выбирается для анализа строк в условии предложения where внешнего запроса

3. ПО результатам принимается решении по включении или невключении строки в состав выходных данных

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

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

возвращается единственное значение. Если в результате выполнения подзапроса будет возвращено несколько значений, то этот

подзапрос будет ошибочным.

В некоторых случаях для гарантии получения единственного значения выполнения подзапроса используется distinct.

Одним из видов функций, которые автоматически возвращает в результате единственное значение для любого количества строк,

являтся агрегирующие функции.

Оператор in также широко применяется в подзапросе.

Используемый в SQL оператор exists генерирует значения истина или ложь, используя подзапросы в качестве аргумента. Этот оператор оценивает результаты выполнения подзапроса как истинный, если этот подзапрос генерирует выходные данные, т. е. в случае существования хотя бы одного найденного значения, в противном случае результат подзапроса ложный.

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

Select distinct student_id

From exam_mark A

Where exists

(Select *

From exam_mark B

Where mark > 3 and A.student_id = B.student_id)

25. Формирование связанных подзапросов.

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

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

SELECT *

FROM SUBJECT SU WHERE '20/01/1999' IN

(SELECT 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;

Использование предложения HAVING позволяет при выводе осуществлять фильтрацию таких групп.

Предикат предложения HAVING оценивается не для каждой

строки результата, а для каждой группы выходных записей,

сформированной предложением GROUP BY внешнего запроса.

Пусть, например, необходимо по данным из таблицы

EXAM_MARKS определить сумму полученных студентами оценок

(значений поля MARK), сгруппировав значения оценок по датам

экзаменов и исключив те дни, когда число студентов, сдавав-

ших в течение дня экзамены, было меньше 10.

SELECT EXAM_DATE, SUM(MARK)

FROM EXAM_MARKS A

GROUP BY EXAM_DATE

HAVING 10 <

(SELECT COUNT(MARK)

FROM EXAM_MARKS В

WHERE A.EXAM_DATE = В.EXAM_DATE);

Подзапрос вычисляет количество строк с одной и той же

датой, совпадающей с датой, для которой сформирована оче-

редная группа основного запроса.