Предикаты в подчиненных запросах.

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

· Сравнение с результатом подчиненного запроса. Значение выражения сравнивается с одним значением, возвращаемым подчиненным запросом. Для сравнения используются операторы =, >, <, >=, <=, <>. Подчиненный запрос в этом случае должен возвращать в качестве результата единичное значение, т.е. одну строку, содержащую один столбец. Если в результате выполнения подчиненного запроса не будет получено ни одной строки или будет получено значение NULL, то операция сравнения возвращает NULL.

· Проверка на принадлежность результатам подчиненного запроса. Значение выражения проверяется на равенство одному из множества значений, которые возвращаются подчиненным запросом. Для проверки используется предикат IN. Например, для того, чтобы вывести имена всех старост со специальности “ВМК”, можно использовать следующий запрос:

SELECT name

FROM students AS S, stud_info AS I

WHERE (S.id = I.id) AND (S.starosta = TRUE) AND

(id_group IN (SELECT id

FROM groups

WHERE spec = ‘BMK’));

В данном случае подчиненный запрос возвращает список идентификаторов групп специальности “ВМК”. Главный запрос затем проверяет каждую строку таблицы “students”, чтобы определить, числится ли соответствующий студент в одной из отобранных групп и является ли он при этом старостой.

· Проверка на существование. Проверяется наличие строк в таблице результатов подчиненного запроса. Для проверки используется предикат EXISTS. Если предположить, что до вступительных экзаменов записи для новых групп уже созданы и в них зачислены только абитуриенты с золотыми медалями, то следующий запрос выведет названия групп, в которые уже зачислены студенты:

SELECT G.kurs, G.spec, G.year, G.index

FROM groups AS G

WHERE EXISTS (SELECT id_group

FROM students

WHERE id_group = G.id);

Главный запрос последовательно перебирает все строки таблицы “groups”, и для каждой группы выполняется подчиненный запрос. Результатом подчиненного запроса является столбец данных, содержащий идентификаторы всех студентов, зачисленных в “текущую” группу. Если такие студенты есть (столбец не пустой), то проверка EXISTS возвращает значение TRUE. Если подчиненный запрос не дает ни одной строки, проверка EXISTS возвращает значение FALSE. Эта проверка не может возвращать значение NULL.

· Многократное сравнение. Значение выражения сравнивается с каждым из множества значений, возвращаемых подчиненным запросом. В SQL имеются две разновидности многократного сравнения – ANY и ALL, расширяющие проверку на членство в множестве до уровня других операторов сравнения. С помощью многократного сравнения можно организовать проверку не только на равенство некоторому элементу множества, возвращаемого подчиненным запросом, но и использовать другие виды сравнения, такие как больше (>), меньше (<), и т.д.

§ Квантор ANY. Проверяемое значение (выражение) поочередно сравнивается с каждым элементом, содержащемся в столбце результатов подчиненного запроса. Если хотя бы одно из этих сравнений дает результат TRUE, то проверка ANY возвращает значение TRUE.

§ Квантор ALL. Проверяемое значение (выражение) поочередно сравнивается с каждым элементом, содержащемся в столбце результатов подчиненного запроса. Если все сравнения дает результат TRUE, то проверка ALL возвращает значение TRUE.

Для примера создадим запрос, выводящий имена и телефоны всех некоммерческих студентов без использования соединений:

SELECT name, phone

FROM stud_info

WHERE id <> ALL (SELECT id

FROM students

WHERE commerce = TRUE);