Использование операторов IN или EXISTS вместо оператора ANY

Мы можем также использовать оператор IN чтобы создать запрос аналогичный предыдущему:

SELECT *
FROM Salespeople
WHERE city IN
(SELECT city
FROM Customers);

Этот запрос будет производить вывод показанный в Рисунке 6.

Рисунок 6 Использование IN в качестве альтернативы к ANY

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

SELECT *
FROM Salespeople
WHERE sname < ANY
(SELECT cname
FROM Customers);

Рисунок 7 Использование оператора ANY с оператором «неравно» (<)

 

Все строки были выбраны для Serres и Rifkin, потому что нет других заказчиков чьи имена следовали бы за ими в алфавитном порядке. Обратите внимание что это является основным эквивалентом следующему запросу с EXISTS, чей вывод показывается в Рисунке 8:

SELECT *

FROM Salespeople outers

WHERE EXISTS

(SELECT *

FROM Customers inners

WHERE outers.sname < inners.cname);

Рисунок 8 Использование EXISTS как альтернатива оператору ANY

Любой запрос который может быть сформулирован с ANY, мог быть также сформулирован с EXISTS, хотя наоборот будет неверно. Строго говоря, вариант с EXISTS не абсолютно идентичен вариантам с ANY или с ALL из-за различия в том как обрабатываются пустые (NULL) значения (что будет обсуждаться позже в этой главе). Тем ни менее, с технической точки зрения, вы могли бы делать это без ANY и ALL если бы вы стали очень находчивы в использовании EXISTS (и IS NULL).

Основная причина для формулировки EXISTS как альтернативы ANY и ALL в том что ANY и ALL могут быть несколько неоднозначен, из-за способа использования этого термина в Английском языке, как вы это скоро увидите. С приходом понимания различия способов формулирования данного запроса, вы сможете поработать над процедурами которые сейчас кажутся Вам трудными или неудобными.

Как any может стать неоднозначным

Как подразумевалось выше, ANY не полностью однозначен. Если мы создаем запрос чтобы выбрать заказчиков которые имеют больший рейтинг чем любой заказчик в Риме, мы можем получить вывод который несколько отличался бы от того что мы ожидали (как показано в Рисунке 9):

SELECT *

FROM Customers

WHERE rating>ANY

(SELECT rating

FROM Customers

WHERE city='Rome');

В Английском языке, способ которым мы обычно склонны интерпретировать оценку «больше чем любой (где city = Rome)», должен вам сообщить что это значение оценки должно быть выше чем значение оценки в каждом случае где значение city = Rome. Однако это не так, в случае ANY - используемом в SQL. ANY оценивает как верно, если подзапрос находит любое значение которое делает условие верным.

Рисунок 9 Как оператор «больше чем» (>) интерпретируется ANY

Чтобы дать другой пример, предположим что мы должны были выбирать все продажи сумм приобретений которые были больше чем по крайней мере один из порядков на 6-е Октября:

SELECT *

FROM Orders

WHERE amt > ANY

(SELECT amt

FROM Orders

WHERE odate = #10/06/1990#);

Вывод для этого запроса показывается в Рисунке 10.

Рисунок 10 Выбранное значение больше чем любое (ANY) на 6-е Октября

Даже если самая высокая сумма приобретений в таблице (9891.88) - имелась на 6-е Октября, предыдущая строка имеет более высокое значение суммы чем другая строка на 6-е Октября, которая имела значение суммы = 1309.95. Имея реляционный оператор ">=" вместо просто " > ", эта строка будет также выбрана, потому что она равна самой себе. Конечно, вы можете использовать ANY с другой SQL техникой, например с техникой объединения. Этот запрос будет находить все порядки со значением суммы меньшей чем значение любой суммы для заказчика в San Jose. (вывод показывается в Рисунке 11):

SELECT *

FROM Orders

WHERE amt<ANY

(SELECT amt