Operatori di confronto modificati da ANY, SOME o ALL

Gli operatori di confronto che introducono una subquery possono essere modificati tramite le parole chiave ALL o ANY. SOME è l'equivalente di ANY nello standard ISO.

Le subquery introdotte da un operatore di confronto modificato restituiscono un elenco di zero o più valori e possono includere una clausola GROUP BY o HAVING. Tali subquery possono essere riformulate con EXISTS.

Utilizzando l'operatore di confronto > come esempio, >ALL indica maggiore di qualsiasi valore, ovvero maggiore del valore massimo. >ALL (1, 2, 3) significa maggiore di 3. >ANY significa invece maggiore di almeno un valore, ovvero maggiore del valore minimo. >ANY (1, 2, 3) significa maggiore di 1.

Una riga di una subquery che include >ALL rispetta la condizione specificata nella query esterna se il valore della colonna che introduce la subquery è maggiore di tutti i valori dell'elenco restituito dalla subquery.

In modo analogo, quando si utilizza >ANY, una riga rispetta la condizione specificata nella query esterna se il valore della colonna che introduce la subquery è maggiore di almeno uno dei valori dell'elenco restituito dalla subquery.

La query seguente, in cui viene illustrata una subquery introdotta da un operatore di confronto modificato da ANY, trova i prodotti con un prezzo di listino maggiore o uguale al prezzo di listino massimo di tutte le sottocategorie di prodotto.

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

Per ogni sottocategoria di prodotto, la query interna trova il prezzo di listino massimo. La query esterna esamina tutti i valori e determina quali prezzi di listino di singoli prodotti sono maggiori o uguali al prezzo di listino massimo delle sottocategorie di prodotto. Se ANY viene sostituito da ALL, la query restituirà unicamente i prodotti con un prezzo di listino maggiore o uguale a tutti i prezzi di listino restituiti dalla query interna.

Se la subquery non restituisce valori, anche la query non restituisce alcun valore.

L'operatore =ANY equivale a IN. Ad esempio, per trovare i nomi di tutti i prodotti di tipo wheel creati in Adventure Works Cycles, è possibile utilizzare IN o =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')

Di seguito è riportato il set di risultati per entrambe le query:

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)

L'operatore < >ANY è tuttavia diverso da NOT IN. < >ANY significa infatti not = a o not = b o not = c. NOT IN significa not = a e not = b e not = c. Il significato di <>ALL è uguale a quello di NOT IN.

Ad esempio, la query seguente trova i clienti di un'area in cui non sono disponibili venditori.

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

I risultati includono tutti i clienti, a eccezione di quelli per i quali l'area di vendita corrisponde a NULL, perché per ogni area assegnata a un cliente è disponibile un venditore. La query interna trova tutte le aree di vendita per le quali sono disponibili venditori e quindi, per ogni area, la query esterna trova i clienti che non sono associati all'area.

Per lo stesso motivo, se si utilizza NOT IN nella query, nei risultati non sarà incluso alcun cliente.

È possibile ottenere gli stessi risultati utilizzando l'operatore <> ALL, che equivale a NOT IN.