Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Специальный оператор ALL

SELECT •

FROM Orders WHERE amt <

(SELECT MAX(amt)

FROM Orders а, Customers Ь WHERE a.cnum = b.cnum

AND b.city = 'San Jose');

Специш~ьпый оператор ALL

Предикат с ALL принимает значение "истина", если ка:ждое (eve1y) значение, вы­ бранное в процессе выполнения подзапроса, удовлетворяет условию, заданному в пре­ дикате внешнего запроса. Если бы было нужно, чтобы в предыдущем примере в состав

выходных данных включались только те покупатели, рейтинг которых превышает рей­

тинг каждого покупателя в Rome, то необходимо было бы ввести следую1Ц)10 команду для получения результата, представленного на рис. 13.9:

SELECT •

FROM Customers

Рис. 13.9. Использование оператора ALL

135

Глава 13. Использование операторов ANY, ALL и SOME

WHERE rating > ALL

(SELECT rating

FROM Customers

WHERE city = 'Rome');

Это предложение проверяет значения рейтинга для всех покупателей в Риме, а за­ тем находит тех покупателей, рейтинг которых превышает рейтинг каждого покупате­

ля из Рима. Наибольший рейтинг в Риме имеет Giovanni, его значение составляет 200. Следовательно, в состав выходных данных включаются только те покупатели, рейтинг которых превышает 200.

EXISTS можно использовать, как в случае с ANY, для получения альтернативной фор­ мулировки того же запроса (выходные данные представлены на рис. 13.10):

Рис. 13.10. Использование EXISTS как альтернативы ALL

136

Специальиый оператор ALL

Равенства и неравенства

ALL, как правило, используется с неравенствами, а не с равенствами, поскольку значение "равно всем" (equal to all), которое должно получиться в результате выполне­ ния подзапроса, может быть получено в случае, если все результаты идентичны. Сле­

дующий запрос выглядит так:

SELECT •

FROM Customers WHERE rating = ALL

(SELECT rating

FROM Customers

WHERE city = ·san Jose');

Команда задана корректно, но в настоящем примере выходные данные не будут по­

лучены. Единственный случай, когда этот запрос продуцирует выходные данные, -

значения рейтинга всех покупателей из San Jose одинаковы. Тогда запрос можно сфор­

мулировать иначе:

SELECT •

FROM Customers WHERE rating =

(SELECT DISTINCT rating FROM Customers

WHERE city = ·san Jose");

Основное различие заключается в признании последней команды ошибочной, если в результате выполнения подзапроса будет получено множество значений; в этом слу­ чае вариант с ALL просто не генерирует никаких выходных данных. Поскольку в дей­ ствительности база данных постоянно изменяется, неразумно заведомо делать какие­ либо предположения относительно ее содержания.

Тем не менее ALL можно эффективно использовать с неравенствами, то есть с опе­ ратором <>. Однако, фраза "значение неравно всем результатам подзапроса" выражает­ ся не так, как в привычном английском языке. Если подзапрос генерирует множество различных значений, как чаще всего это и бывает, то никакое одно значение не может бьrrь равно всем значениям в обычном смысле. В SQL <> ALL реально означает "не

равно ни одному" из результатов подзапроса. Другими словами, предикат имеет значе­

ние истина, если значение не принадлежит множеству результатов подзапроса. Следова­ тельно, предыдущий запрос можно сформулировать, например, так (выходные данные для запроса представлены на рис. 13.11 ):

SELECT •

FROM Customers WHERE rating <> ALL

(SELECT rating

137

Глава 13. Использование 011ераторов ANY, ALL и SOME

Рис. 13.11. Использование ALL с символом<>

FROM Customers

WHERE city = ·san Jose');

Этот подзапрос выбирает все рейтинги, для которых в поле city указано значение San Jose. В результате получается множество из двух значений: 200 (для Liu) и 300 (для Cisneros). Основной запрос затем выбирает все строки, в которых значение поля rating отличается от этих значений, т.е. все строки со значением рейтинга l 00. Этот же запрос можно сформулировать с

SELECT •

FROM Custome rs

WHERE rating NOT IN

(SELECT rating

FROM Customers

WHERE city = ·san Jose');

Можно также использовать ANY:

SELECT •

FROM Customers

(SELECT rating

FROM Customers

WHERE city = ·san Jose');

Выходные данные одинаковы для трех последних запросов.

138

ФункционированиеANY, ALL и EXISTS при потере данных

Непосредственная поддержка ANY и ALL

В языке SQL выражение "значение больше (или меньше), чем любое (ANY) из множества значений" эквивалентно выражению "значение больше (или меньше) какого-либо из множества значений". Соответственно, "значение неравно всему мно­ жеству значений (not equal ALL)" означает "в этом множестве нет значений, с которым

оно совпадает".

Функционирование ANY, ALL и EXISTS при потере данных или с неизвестными данными

Ранее упоминалось, что существуют некоторые различия между EXISTS и введен­ ными в этой главе операторами, касающиеся обработки NULL-значений. ANY и ALL

также отличаются друг от друга по своей реакции, когда в результате выполнения подза­ проса не получено значений, которые могут использоваться в сравнении. Если эти отли­

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

Когда подзапрос возвращает значение ЕМРТУ

Важное различие между ALL и ANY заключается в их реакции на ситуацию, когда подзапрос не генерирует никакого значения. Когда правильный подзапрос не генерирует выходных данных, ALL автоматически принимает значение "истина", а ANY - "ложь".

Это означает, что следующий запрос:

SELECT •

FROM Customers WHERE rating > ANY

(SELECT rating

FROM Customers

WHERE city = 'Boston');

не генерирует выходных данных, тогда как запрос:

SELECT •

FROM Customers

WHERE rating > ALL

(SELECT rating

139