Operadores de comparação modificados por ANY, SOME ou ALL

Os operadores de comparação que introduzem uma subconsulta podem ser modificados pelas palavras-chave ALL ou ANY. SOME é um padrão ISO equivalente a ANY.

As subconsultas introduzidas por um operador de comparação modificado retornam uma lista com zero ou mais valores e podem incluir uma cláusula GROUP BY ou HAVING. Essas subconsultas podem ser declaradas novamente com EXISTS.

Usando o > operador de comparação como um exemplo, >ALL significa maior do que todos os valores. Em outras palavras, significa maior do que o valor máximo. Por exemplo, >ALL (1, 2, 3) quer dizer maior do que 3. >ANY quer dizer maior do que pelo menos um valor, isto é, maior do que o mínimo. Assim >ANY (1, 2, 3) quer dizer maior do que 1.

Para uma linha em uma subconsulta com >ALL satisfazer a condição especificada na consulta externa, o valor na coluna que introduz a subconsulta deve ser maior do que cada valor na lista de valores retornada pela subconsulta.

De modo semelhante, >ANY significa que para uma fila satisfazer a condição especificada na consulta exterior, o valor na coluna que introduz a subconsulta deve ser maior do que pelo menos um dos valores na lista de valores retornada pela subconsulta.

A consulta a seguir fornece um exemplo de uma subconsulta introduzido com um operador de comparação modificado por ANY. Encontra os produtos cujos preços de tabela são maiores ou iguais ao preço máximo de tabela de qualquer subcategoria de produto.

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ListPrice >= ANY
    (SELECT MAX (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID)

Para cada subcategoria de produto, a consulta interna encontra o preço máximo de tabela. A consulta exterior procura todos esses valores e determina quais os preços de tabela do produto individual são maiores ou iguais ao preço máximo de tabela de qualquer subcategoria de produto. Se ANY for alterado para ALL, a consulta retornará apenas aqueles produtos cujo preço de tabela é maior ou igual a todos os preços de tabela retornados na consulta interna.

Se a subconsulta não retornar nenhum valor, a consulta inteira não retorna qualquer valor.

O operador =ANY é equivalente a IN. Por exemplo, para achar os nomes de todos os produtos de roda que o Adventure Works Cycles produz, você pode usar IN ou =ANY.

--Using =ANY
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID =ANY
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels')

--Using IN
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels')

Aqui está o conjunto de resultados para ambas as consultas:

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)

O operador < >ANY, entretanto difere de NOT IN: < >ANY quer dizer não=a, ou não = b, ou não = c. NOT IN quer dizer não= a, e não = b, e não = c. <>ALL significa a mesma coisa que NOT IN.

Por exemplo, a consulta a seguir encontra os clientes localizados em um território não coberto por qualquer vendedor.

Use AdventureWorks;
GO
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID <> ANY
    (SELECT TerritoryID
     FROM Sales.SalesPerson)

Os resultados incluem todos os clientes, exceto aqueles cujos territórios de vendas são NULL, porque todo território atribuído a um cliente está coberto por um vendedor. A consulta interna encontra todos os territórios de vendas cobertos por vendedores e então, para cada território, a consulta externa encontra os clientes que não estão em um.

Pela mesma razão, quando você usar NOT IN nessa consulta, os resultados não incluem nenhum dos clientes.

Você pode obter os mesmos resultados com o operador < >ALL, que é equivalente a NOT IN.