Использование операторов EXISTS, ANY, ALL, И SOME
В дальнейшем изложении будем использовать в качестве примера небольшую БД отражающую учет успеваемости студентов ВУЗа. В ней содержится четыре таблицы.
В таблице СТУДЕНТЫ (STUDENTS) содержится пять полей с информацией о студентах:
- SNUM - номер студенческого билета;
- SFAM - фамилия студента;
- SIMA - имя студента;
- SOTCH - отчество студента;
- STIP - размер получаемой студентом стипендии.
В таблице ПРЕДМЕТЫ (PREDMET), состоящей из пяти полей, содержится информация об учебных предметах. Назначение полей таблицы следующее:
- PNUM - номер (код) учебного предмета;
- PNAME - наименование учебного предмета:
- TNUM - номер (код) преподавателя;
- HOURS - продолжительность учебной дисциплины в часах:
- COURS - курс, на котором ведется данный учебный предмет.
В таблице ПРЕПОДАВАТЕЛИ (TEACHERS), состоящей из пяти полей, содержится информация о преподавателях. Поля таблицы следующие:
- TNUM - код преподавателя;
- TFAM - фамилия преподавателя;
- TIMA - имя преподавателя;
- TOTCH - отчество преподавателя;
- TDATE - дата принятия преподавателя на работу.
В таблице УСПЕВАЕМОСТЬ (USP), состоящей из пяти полей, хранится информация об успеваемости студентов по учебным дисциплинам. Поля таблицы следующие:
- UNUM - код факта сдачи учебной дисциплины;
- OCENKA - оценка, полученная студентом по учебному предмету;
- UDATE - дата сдачи;
- SNUM - номер студенческого билета;
- PNUM - код учебного предмета.
Таблица 1 PREDMET
PNUM | PNAME | TNUM | HOURS | COURS |
Физика | ||||
Химия | ||||
Математика | ||||
Философия | ||||
Экономика |
Таблица 2 STUDENTS
SNUM | SFAM | SIMA | SOTCH | STIP |
Поляков | Анатолий | Алексеевич | 25.50 | |
Старова | Любовь | Михайловна | 17.00 | |
Гриценко | Владимир | Николаевич | 0.00 | |
Котенко | Анатолий | Николаевич | 0.00 | |
Нагорный | Евгений | Васильевич | 25.50 |
Таблица 3 TEACHERS
TNUM | TFAM | TIMA | TOTCH | TDATE |
Викулина | Валентина | Ивановна | 01/04/1984 | |
Костыркин | Олег | Владимирович | 01/09/1997 | |
Казанко | Виталий | Владимирович | 01/09/1988 | |
Позднякова | Любовь | Алексеевна | 01/09/1988 | |
Загарийчук | Игорь | Дмитриевич | 10/05/1989 |
Таблица 4 USP
UNUM | OCENKA | UDATE | SNUM | PNUM |
10/06/1999 | ||||
10/06/1999 | ||||
11/06/1999 | ||||
12/06/1999 | ||||
12/06/1999 |
Необходимо поговорить о некоторых специальных операторах, которые всегда берут подзапросы в качестве аргументов – это EXISTS, ANY, ALL, и SOME.
Оператор EXISTS используется для указания предикату на то, производится или не производится вывод в подзапросе, при этом EXISTS дает в качестве результата значение ИСТИНА или ЛОЖЬ. Это в свою очередь означает, что он может работать в предикате или в комбинации с другими булевскими выражениями – AND, OR, и NOT. Другими словами, EXISTS берет подзапрос как аргумент и оценивает его как истинный, если он осуществляет любой вывод, или как ложный, если он не делает этого. Например, можно решить, извлекать ли данные из таблицы успеваемости, если в ней присутствуют отличные оценки. Это реализуется следующим образом:
SELECT * FROM USP WHERE USP.OCENKA = 4
AND EXISTS (SELECT * FROM USP WHERE USP.OCENKA = 5);
Результаты запроса будут следующие:
UNUM OCENKA UDATE SNUM PNUM
1001 5 10/06/1999 3412 2001
1005 5 12/06/1999 3416 2004
При этом внутренний запрос выбирает все данные для всех студентов, получивших оценку 5. Оператор EXISTS во внешнем предикате отмечает, что некоторый вывод в подзапросе имел место, значит, это делает предикат верным. Подзапрос был выполнен только один раз для всего внешнего запроса, и, следовательно, имеет одно значение во всех случаях – по этой причине EXISTS делает предикат верным или неверным для всех строк сразу. В последнем примере, если строго говорить, EXISTS должен быть установлен так, чтобы выбрать только один столбец. Однако он выбирает все столбцы во вложенном предложении SELECT *. Это не вызывает ошибки, поскольку при выборе EXISTS как одного столбца, так и всех столбцов, он просто замечает выполнение вывода из подзапроса, а полученные значения не использует.
В соотнесенном подзапросе, предложение EXISTS оценивается отдельно для каждой строки таблицы, имя которой указано во внешнем запросе, точно так же, как и другие операторы предиката, когда используется соотнесенный подзапрос. Это дает возможность использовать EXISTS как предикат, который генерирует различные значения для каждой записи таблицы, указанной в основном запросе. Следовательно, информация из внутреннего запроса будет сохраняться. Например, с помощью следующего запроса выведем информацию о студентах, которые имеют несколько оценок:
SELECT DISTINCT SNUM FROM USP FIRST WHERE EXISTS (SELECT *
FROM USP SECOND
WHERE SECOND.SNUM = FIRST.SNUM AND SECOND.PNUM <> FIRST.PNUM);
Вывод запроса следующий:
SNUM
Здесь для каждой текущей строки внешнего запроса внутренний запрос находит записи, которые совпадают со значением поля номера студента SNUM, но не совпадают со значением кода предмета PNUM. Если любые такие строки будут найдены подзапросом, это означает, что имеются, по крайней мере, две оценки, полученные текущим студентом. Если бы во внешнем запросе DISTINCT не был указан, то каждый из студентов, имеющий несколько оценок, был бы выбран столько раз, сколько у него оценок.
Для иллюстрации возможности использования комбинации из EXISTS и объединения, усовершенствуем последний пример таким образом, чтобы выводилась более подробная информация о студентах:
SELECT DISTINCT FIRST.SNUM, FIRST.SFAM, FIRST.SIMA, FIRST.SOTCH
FROM STUDENTS FIRST, USP SECOND WHERE EXISTS (SELECT *
FROM USP THIRD
WHERE SECOND.SNUM = THIRD.SNUM
AND SECOND.PNUM <> THIRD.PNUM) AND FIRST.SNUM = SECOND.SNUM;
В результате будет получено:
SNUM SFAM SIMA SOTCH
3412 Поляков Анатолий Алексеевич
В этом примере внутренний запрос, как и в предыдущем варианте, сообщает, что студент получил несколько оценок. Внешний запрос – это объединение таблиц успеваемости и студентов. Появившееся новое предложение основного предиката AND F1RST.SNUM = SECOND.SNUM оценивается на том же самом уровне, что и предложение EXISTS, т. к. это элемент предиката самого объединения двух таблиц из внешнего запроса.
С учетом того, что EXISTS может работать в комбинации с булевскими операторами, наиболее очевидным способом его использования является сочетание с оператором NOT. Например, для получения информации о студентах, имеющих только одну оценку, можно воспользоваться следующим запросом:
SELECT DISTINCT FIRST.SNUM, FIRST.SFAM, FIRST.SIMA, FIRST.SOTCH
FROM STUDENTS FIRST, USP SECOND WHERE NOT EXISTS (SELECT *
FROM USP THIRD
WHERE SECOND.SNUM = THIRD.SNUM
AND SECOND.PNUM <> THIRD.PNUM) AND FIRST.SNUM = SECOND.SNUM;
Вывод этого запроса приведен ниже:
SNUM SFAM SIMA SOTCH
3413 Старова Любовь Михайловна
3414 Гриценко Владимир Николаевич
3416 Нагорный Евгений Васильевич
Важным свойством EXISTS являетсято, что он не может взять агрегатную функцию в подзапросе, т. к. если агрегатная функция нашла какие-либо записи для операций с ними, то EXISTS будет верен в любом случае. Выходом из такой ситуации является использование вложенного подзапроса в подзапросе, в предикате которого присутствует EXISTS. Это позволяет тщательно структурировать запросы, делая их более понятными. Возвращаясь к примеру с нахождением информации о студентах, имеющих более одной оценки, можно предложить такой вариант запроса:
SELECT *
FROM STUDENTS FIRST WHERE EXISTS (SELECT *
FROM USP SECOND
WHERE FIRST.SNUM = SECOND.SNUM AND 1 <
(SELECT COUNT (*) FROM USP WHERE USP.SNUM = SECOND.SNUM));
В результате будет получено:
SNUM SFAM SIMA SOTCH STIP
3412 Поляков Анатолий Алексеевич 25.50
Такая конструкция работает следующим образом: внешний запрос выбирает из таблицы студентов STUDENTS текущую строку и выполняет подзапросы. Для текущей строки средним запросом берется в соответствие каждая строка из таблицы успеваемости USP. Всякий раз, когда обнаруживается информация в среднем запросе, который совпадает по номеру студенческого билета с текущей строкой во внешнем запросе. SQL должен рассматривать внутренний подзапрос для того, чтобы определить, будет ли предикат среднего запроса верен. Внутренний запрос считает количество оценок текущего студента, и, если это число больше, чем 1, делает предикат среднего запроса верным. Это, в свою очередь, делает EXISTS-предикат внешнего запроса верным для текущей строки таблицы.
Таким образом, несмотря на кажущуюся простоту, EXISTS может оказаться одним из самых трудных с точки зрения использования операторов в SQL. Существуют еще три специальных оператора, ориентируемых на подзапросы – это ANY. ALL и SOME, напоминающие EXISTS, которые воспринимают подзапросы, как аргументы. Однако эти операторы отличаются от EXISTS тем, что используются совместно с реляционными операторами, аналогично IN в подзапросах
Операторы SOME и ANY достаточно часто взаимозаменяемы, и в наших примерах будут работать одинаково. Различие в терминологии состоит в том, чтобы позволить пользователям употреблять тот термин, который наиболее однозначен. Поэтому все сказанное относительно ANY в равной степени относится и к SOME.
Рассмотрим следующий способ нахождения студентов, которые получали оценки по разным учебным предметам:
SELECT * FROM STUDENTS WHERE SNUM = ANY (SELECT SNUM FROM USP);
Вывод этого запроса приведен ниже:
SNUM SFAM SIMA SOTCH STIP
3412 Поляков Анатолий Алексеевич 25.50
3413 Старова Любовь Михайловна 17.00
3414 Гриценко Владимир Николаевич 0.00
3416 Нагорный Евгений Васильевич 25.50
Оператор ANY берет все значения, выведенные подзапросом, т. е. в примере – все значения поля SNUM, и оценивает их как верные, если любое из их равняется номеру студенческого билета текущей строки внешнего запроса. Отсюда следует, что подзапрос должен выбирать значения такого же типа, как и те, которые сравниваются в основном предикате. В этом его отличие от EXISTS, который просто определяет, производит ли вывод подзапрос или нет, фактически не используя эти результаты.
Вообще говоря, в ряде случаев допускается использование операторов IN или EXISTS вместо оператора ANY. Например, используя оператор IN, можно создать запрос, аналогичный предыдущему:
SELECT * FROM STUDENTS WHERE SNUM IN (SELECT SNUM FROM USP);
Вывод этого запроса такой, как в предыдущем примере, по этому приводить его не будем.
Интересен тот факт, что оператор ANY может использовать другие реляционные операторы, за исключением равняется, и. таким образом, реализовывать сравнения, которые недоступны с IN. Например, можно выполнить следующий запрос:
SELECT PNAME FROM PREDMET WHERE HOURS > ANY (SELECT HOURS FROM PREDMET);
Результат этого запроса следующий:
PNAME
Физика
Химия
Математика
Здесь выводятся название учебных предметов, для которых существует хотя бы одна учебная дисциплина с количеством часов, меньшим, чем у текущей.
В принципе, этот же запрос может быть реализован с использованием EXISTS, следующим образом:
SELECT PNAME
FROM PREDMET FIRST WHERE EXISTS (SELECT *
FROM PREDMET SECOND WHERE FIRST.HOURS > SECOND.HOURS);
В любом случае запрос, который может быть реализован с ANY и ALL, может быть также сформулирован с EXISTS, но не наоборот. Правда, в ряде случаев, из-за различий в обработке NULL значений, вариант с EXISTS не абсолютно идентичен, чем при использовании ANY, поэтому необходимо в таких случаях применять команду IS NULL.
Основное преимущество в использовании ANY и ALL по сравнению с EXISTS заключается в том, что последний требует соотнесенных подзапросов, порой сложных для понимания. Кроме того, подзапросы с ANY или ALL могут выполняться один раз и иметь вывод, используемый для определения предиката для каждой строки основного запроса, a EXISTS требует, чтобы весь подзапрос повторно выполнялся для каждой строки основного запроса.
Оператор ALL работает таким образом, что предикат является верным, если каждое значение, выбранное подзапросом, удовлетворяет условию в предикате внешнего запроса.
Например, если необходимо вывести только тех студентов, чьи оценки выше или равны полученным 10/06/1999, то можно воспользоваться следующим запросом:
SELECT * FROM USP
WHERE OCENKA >= ALL (SELECT OCENKA FROM USP WHERE UDATE = 10/06/1999);
Вывод этого запроса такой:
UNUM OCENKA UDATE SNUM PNUM
1001 5 10/06/1999 3412 2001
1005 5 12/06/1999 3416 2004
Запрос работает так: подзапрос проверяет значения оценок за 10/06/1999 для всех студентов. Затем он находит студентов с оценкой большей или равной по сравнению с оценками за 10/06/1999. Поскольку самая высокая оценка в этот день – отлично, то выбираются только записи с оценкой 5.
Как и в случае с ANY, допускается использовать EXISTS для альтернативной формулировки такого же запроса:
SELECT *
FROM USP FIRST WHERE EXISTS (SELECT * FROM USP SECOND
WHERE FIRST.OCENKA >= SECOND.OCENKA AND SECOND.UDATE = 10/06/1999);
Основное применение ALL находит со знаком неравенства, т. к. предикат может быть верным, если сравниваемое значение равно для всех, т. е. все записи, фактически, идентичны. Например, запрос
SELECT * FROM USP
WHERE OCENKA = ALL (SELECT OCENKA FROM USP WHERE UDATE = 10/06/1999);
является допустимым, но вывод будет только в случае, если все оценки за 10/06/1999 окажутся идентичными. Вероятно, не очень хорошо использовать запросы, которые работают только в определенных ситуациях.
Однако ALL гораздо более эффективно используется со знаком неравенства, т. е. с оператором < >. При этом обязательно надо учитывать, что если подзапрос возвращает много различных значений, то это означает неравенство любому результату. Иначе говоря, предикат верен, если данное значение не найдено среди результатов подзапроса. Рассмотрим такой запрос:
SELECT * FROM USP
WHERE OCENKA <> ALL (SELECT OCENKA FROM USP WHERE UDATE = 10/06/1999);
Вывод этого запроса таков:
UNUM OCENKA UDATE SNUM PNUM
1003 3 11/06/1999 3414 2005
Рассматриваемый запрос сделает следующее: подзапрос выберет все оценки за 10/06/1999 – это будут 5 и 4. После этого, основной запрос выведет все записи с оценкой, не совпадающей ни с одной из них. Аналогичный запрос можно сформулировать с использование оператора NOT IN:
SELECT * FROM USP
WHERE OCENKA NOT IN (SELECT OCENKA FROM USP WHERE UDATE = 10/06/1999);
или используя оператор ANY:
SELECT * FROM USP
WHERE NOT OCENKA = ANY (SELECT OCENKA FROM USP WHERE UDATE = 10/06/1999);
Вывод этих запросов такой же, как и первого. Таким образом, в SQL дать запрос на сравнение с использованием команды ANY для набора значений – то же самое, что произвести сравнение с любым отдельным значением из набора. Если запросить значения с помощью команды ALL не равные набору значений, то это то же самое, что определить факт отсутствия этого значения во всем наборе.
Как уже было сказано, имеются некоторые различия между EXISTS и операторами ANY и ALL с точки зрения работы с неизвестными и отсутствующими данными. Кроме того, важное различие между ALL и ANY – это способ действия в ситуации, когда подзапрос не возвращает никаких значений. Вообще говоря, во всех случаях, когда допустимый подзапрос не делает вывода. ALL автоматически возвращает значение ИСТИНА, a ANY –ЛОЖЬ. Например, это означает, что запрос:
SELECT * FROM USP
WHERE OCENKA >= ANY (SELECT OCENKA FROM USP WHERE UDATE = 10/06/2000);
не произведет никакого вывода, в то время как запрос
SELECT * FROM USP
WHERE OCENKA >= ALL (SELECT OCENKA FROM USP WHERE UDATE = 10/06/2000);
выведет всю таблицу USP. Действительно, если нет никаких оценок за 10/06/2000 то, естественно, ни одно из этих сравнений не имеет значения.
NULL значения имеют свои особенности при их обработке этими операторами. Когда SQL сравнивает два значения в предикате, одно из которых NULL, очевидно, результат этого неизвестен. Неизвестный предикат, аналогично неверному, является причиной того, что запись не выбирается, однако работать он будет несколько иначе, в зависимости от того, используется ALL или ANY. Рассмотрим предыдущий пример с ANY и такой запрос:
SELECT * FROM USP FIRST WHERE EXISTS (SELECT *
FROM USP SECOND
WHERE FIRST.OCENKA >= SECOND.OCENKA AND SECOND.UDATE = 10/06/2000);
Пока NULL значений в таблице нет, оба запроса будут вести себя одинаково. Но в случае появления NULL значения в поле OCENKA таблицы успеваемости, в варианте с ANY значение предиката при обработке этой строки станет неизвестным и вывода для нее не будет в любом случае, однако другие строки будут обрабатываться обычным образом. В варианте с EXISTS, значение NULL используется в предикате подзапроса, делая его неизвестным в каждом случае, а это означает, что подзапрос не будет производить никаких значений, и запрос не произведет вывода вообще. Это является важным фактором в пользу использования варианта с ANY.