Операторы сравнения с модификаторами ANY, SOME или ALL
Операторы сравнения с вложенными запросами могут быть уточнены с помощью ключевых слов ALL или ANY. SOME является эквивалентом ANY в стандарте ISO.
Вложенные запросы операторов сравнения с модификаторами возвращают список из нуля или более значений и могут включать предложения GROUP BY или HAVING. Эти вложенные запросы могут быть переформулированы с использованием ключевого слова EXISTS.
Рассмотрим, например оператор сравнения >: >ALL будет означать «больше любого значения». Другими словами, это сравнение с максимальным значением. Например, >ALL (1, 2, 3) означает «больше 3». >ANY означает «больше по крайней мере одного значения», т. е. «больше минимума». Поэтому >ANY (1, 2, 3) означает «больше 1».
Чтобы строка результата вложенного запроса с >ALL удовлетворяла условию, заданному внешним запросом, значение в столбце, для которого вводится вложенный запрос, должно быть больше каждого значения из списка, возвращаемого вложенным запросом.
Аналогичным образом, чтобы строка результата вложенного запроса с >ANY, удовлетворяла условию, заданному внешним запросом, значение в столбце, для которого вводится вложенный запрос, должно быть больше хотя бы одного значения из списка, возвращаемого вложенным запросом.
Следующий запрос сдержит пример вложенного запроса, используемого оператором сравнения с модификатором ANY. Он вернет все продукты, цены на которые больше или равны максимальной цене в любой подкатегории продуктов.
USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE ListPrice >= ANY
(SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID) ;
Для каждой подкатегории продуктов внутренний запрос найдет максимальную цену. Внешний запрос получит эти значения и определит, какие цены отдельных продуктов больше или равны максимальной цене в любой из подкатегорий продуктов. Если ключевое слово ANY заменить на ALL, запрос вернет только те продукты, цена которых больше или равна всем ценам, возвращаемым внутренним запросом.
Если вложенный запрос не возвращает значений, весь запрос не возвратит никаких значений.
Оператор =ANY эквивалентен оператору IN. Например, чтобы найти названия всех колес, производимых Adventure Works Cycles, можно использовать или IN, или =ANY.
--Using =ANY
USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID =ANY
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Wheels') ;
--Using IN
USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN
(SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Wheels') ;
Вот результирующий набор, возвращаемый любым из этих запросов:
Name
--------------------------------------------------
LL Mountain Front Wheel
ML Mountain Front Wheel
HL Mountain Front Wheel
LL Road Front Wheel
ML Road Front Wheel
HL Road Front Wheel
Touring Front Wheel
LL Mountain Rear Wheel
ML Mountain Rear Wheel
HL Mountain Rear Wheel
LL Road Rear Wheel
ML Road Rear Wheel
HL Road Rear Wheel
Touring Rear Wheel
(14 row(s) affected)
Однако оператор <>ANY отличается от NOT IN: < >ANY означает «не равно a, или не равно b, или не равно c». NOT IN означает «не равно a, и не равно b, и не равно c». <>ALL означает то же, что и NOT IN.
Например, следующий запрос отобразит заказчиков, находящихся на территории, где не работает ни один менеджер по продажам.
Use AdventureWorks2008R2;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
(SELECT TerritoryID
FROM Sales.SalesPerson) ;
В результат включены все заказчики, кроме тех, чьим территориям продаж соответствует NULL, так как любая территория, назначенная заказчику, обслуживается менеджером по продажам. Внутренний запрос находит все территории продаж, обслуживаемые менеджерами по продажам, а затем для каждой территории внешний запрос находит заказчиков, которые ей не принадлежат.
По этой же причине, если в этом запросе использовать NOT IN, в результат не войдет ни один из заказчиков.
Те же результаты можно получить с помощью оператора <>ALL, эквивалентного NOT IN.
См. также