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

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

Оператор ANY может использовать другие операторы отношения кроме равенства и, следовательно, выполняет сравнения, отличные от сравнений в IN. Например, мож­ но найти всех продавцов, имеющих покупателей, имена которых следуют в алфавит­ ном порядке за именем продавца (выходные данные представлены на рис. 13.3):

SELECT *

FROM Salespeople WHERE sname < ANY

(SELECT cname

FROM Custome rs);

Все строки, которые были выбраны, сохраняются для Serres и Rifkin, поскольку они не имеют покупателей, имена которых следуют за их именами в алфавитном по­

рядке. Это эквивалентно следующему запросу с EXISTS, выходные данные для кото­

рого представлены на рис. 13.4:

SELECT *

FROM Salespeople outer WHERE EXISTS

(SELECT *

FROM Customers inner

WHERE outer.sname < inner.cname);

Любой запрос, сформулированный с ANY (или с ALL), можно сформулировать и с

EXISTS, хотя обратное утверждение неверно. Строго говоря, версии с EXISTS не со­

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

Рис. 13.3. Использование ANY с неравенством

130

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

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

ANY и ALL, если умело применять EXISTS (и IS NULL). Многие пользователи счита­ ют, что использовать ANY и ALL проще, чем EXISTS, который требует связанных под­ запросов. В зависимости от практической реализации, ANY и ALL могут, по крайней мере теоретически, быть более эффективными по сравнению с EXISTS. Подзапрос с ANY или ALL можно выполнять один раз для каждой строки основного запроса и пре­ доставлять выходные данные для определения предиката. С другой стороны, EXISTS использует связанный подзапрос, который требует повторного выполнения всего подза­ проса для каждой строки основного запроса. SQL пытается найти наиболее эффектив­ ный способ выполнения любой команды, поэтому он может пытаться преобразовать менее действенную формулировку запроса в более эффективную (но нельзя рассчиты­ вать на то, что при этом будет найдена наиболее эффективная формулировка).

Основная причина предложения формулировки с EXISTS, как альтернативы ANY и ALL, состоит в противоречии ANY и ALL интуиции, связанной со спецификой приме­

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

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

процедур для сложных или неясных случаев.

Неоднозначности при использовании ANY

ANY не является полностью интуитивно очевидным. Если запрос формулируется для выбора покупателей, имеющих рейтинг, превышающий рейтинг любого покупате­

ля в Rome, можно получить выходные данные, которые отличаются от ожидаемых (как показано на рис. 13.5):

SELECT •

131

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

Рис. 13.5. Больше, чем ANY в интерпретации SQL

FROM Customers

WHERE rating > ANY

(SELECT rating

FROM Customers

WHERE city = 'Rome');

В английском языке выражение "рейтинг больше, чем любой (апу) другой (где поле city равно Rome)", обычно интерпретируется так: значение данного рейтинга должно быть выше, чем значение рейтинга для каждого (eve1y) случая, когда значение поля city равно Rome. Однако в SQL оператор ANY интерпретируется иначе. ANY оценива­ ется как истина, если подзапрос находит любое (апу) значение (любые значения), удов­ летворяющее (удовлетворяющие) условию.

Если бы ANY интерпретировалось как обычное слово английского языка, покупате­ ли с рейтингом 300 оказались бы выше Giovanni, находящегося в Rome и имеющего рейтинг 200. Однако, подзапрос с ANY находит также и Periera из Rome с рейтингом 100. Все покупатели с рейтингом 200 были выбраны (поскольку их рейтинги превыша­ ют 100) несмотря на то, что в Rome был другой покупатель (Giovanni), рейтинг которого выше 200 (то, что один из выбранных покупателей тоже находится в Rome, здесь значе­ ния не имеет). Они были выбраны, поскольку подзапрос сгенерировал значение, сделав­

шее предикат истинным для этих строк.

Другой пример: предположим, нужно выбрать все заказы, величина которых пре­ восходит величину по крайней мере одного из заказов, сделанных 6 октября 1990 года:

SELECT *

FROM Orders

132

СпециШlьный оператор ANY или SOME

WHERE amt > ANY (SELECT amt

FROM Orders

WHERE odate = 10/06/1990);

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

Даже если наибольшая величина заказов, представленных в таблице (9891.88), приходится на 6 октября, предшествующие строки имеют значения, превышающие значение поля amounts в другой строке таблицы за 6 октября 1990 года - 1309.95.

Рис. 13.6. Выбор записей, значение поля amt которых превышает ANY на 6 октября

Если бы использовался оператор отношения>= вместо просто>, эта строка тоже была бы выбрана, так как она равна сама себе.

Можно использовать ANY с другими SQL-средствами, например, с соединениями. Этот запрос отыскивает все заказы, величина которых меньше величины заказа любого покупателя в (выходные данные представлены на рис. 13.7):

SELECT *

FROM Orders

WHERE amt < ANY

(SELECT amt

FROM Orders a,Customers Ь

WHERE a.cnum = b.cnum

AND b.city = 'San Jose');

Даже если минимальное значение в таблице указано в заказе из San Jose, имеется другое, превышающее его, поэтому почти все строки были выбраны. Легко запом-

133

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

**ililШM

311101

31111113 767.19 10/03/199111

31111112 19111111.1111 1111/1113/199111

311106 1098.16 10/1113/1990

31111119 1713.23 1111/04/199111

31111117 75.75 10/1114/199111

3008 4723.1110 10/05/1990

31111111 131119.95 10/06/199111

Рис. 13. 7. Использование ANY с JOIN (соединением)

Рис. 13.8. Использование функции агрегатирования вместо ANY

нить, что< ANY означает "меньше, чем наибольшее выбранное значение", а> ANY означает "больше, чем наименьшее выбранное значение". Фактически, такую коман­ ду можно сформулировать следующим образом (выходные данные представлены на

рис. 13.8):

134