SOME, All and ANY operators in SQL Server

In this blog we will talke about some, all and any operator in sql server.
All: represent every value in the sub query. Some & Any: We could use ANY or SOME interchangeably and both operators work the same way. = ANY will return nothing <>ANY means the main query is different from at least one of the sub query values = ALL has a meaning of it is equal to all values <>ALL equivalent to NOT IN operator Let us see how we could us ALL, ANY and SOME operators in action:

-- = ANY operator
SELECT DISTINCT S.CustomerID
FROM [Sales].[SalesOrderHeader] S
WHERE S.CustomerID = ANY(
      SELECT C.CustomerID
      FROM [Sales].[Customer] C
      WHERE C.CustomerID >30111)
ORDER BY S.CustomerID
 
-- = SOME operator
 
SELECT DISTINCT S.CustomerID
FROM [Sales].[SalesOrderHeader] S
WHERE S.CustomerID = SOME(
      SELECT C.CustomerID
      FROM [Sales].[Customer] C
      WHERE C.CustomerID >30111)
ORDER BY S.CustomerID
 
-- IN operator
 
SELECT DISTINCT S.CustomerID
FROM [Sales].[SalesOrderHeader] S
WHERE S.CustomerID IN(
      SELECT C.CustomerID
      FROM [Sales].[Customer] C
      WHERE C.CustomerID >30111)
ORDER BY S.CustomerID

All the above scripts give the same result set.

ANY could also be used with other relational operators and we could use EXISTS operator to deliver the same result set.

-- >ANY
 
SELECT DISTINCT S.CustomerID
FROM [Sales].[SalesOrderHeader] S
WHERE S.CustomerID > ANY(
      SELECT C.CustomerID
      FROM [Sales].[Customer] C
      WHERE C.CustomerID =30111)
ORDER BY S.CustomerID
 
--EXISTS
 
SELECT DISTINCT S.CustomerID
FROM [Sales].[SalesOrderHeader] S
WHERE EXISTS (
      SELECT C.CustomerID
      FROM [Sales].[Customer] C
      WHERE C.CustomerID =30111 AND
      S.CustomerID > C.CustomerID)
ORDER BY S.CustomerID

Both the above SELECT statements give the same result set.

all and any operator in sql server

<> ALL produce the same result set as NOT IN operator

-- <> ALL operator
 
SELECT DISTINCT S.CustomerID
FROM [Sales].[SalesOrderHeader] S
WHERE S.CustomerID <> ALL(
      SELECT C.CustomerID
      FROM [Sales].[Customer] C
      WHERE C.CustomerID <30111)
ORDER BY S.CustomerID
 
-- NOT IN operator
 
SELECT DISTINCT S.CustomerID
FROM [Sales].[SalesOrderHeader] S
WHERE S.CustomerID NOT IN(
      SELECT C.CustomerID
      FROM [Sales].[Customer] C
      WHERE C.CustomerID <30111)
ORDER BY S.CustomerID

Both the above SELECT statements give the same result set.

all and any operator in sql server

Even though =ALL is okay syntax wise it will not produce any logical result and will return nothing.

-- = ALL operator
SELECT DISTINCT S.CustomerID
FROM [Sales].[SalesOrderHeader] S
WHERE S.CustomerID = ALL(
      SELECT C.CustomerID
      FROM [Sales].[Customer] C
      WHERE C.CustomerID <30111)
ORDER BY S.CustomerID

Sometimes ANY could not be confusing. We should not assume the restriction in the sub query will also apply for the main query. To demonstrate this let’s see this example:

-- Misunderstanding with ANY
 
SELECT D.SalesOrderID,D.UnitPrice,D.ProductID
FROM [Sales].[SalesOrderDetail] D
WHERE D.UnitPrice > ANY(
      SELECT S.UnitPrice
      FROM [Sales].[SalesOrderDetail] S
      WHERE S.ProductID = 710);

all and any operator in sql server

Though we have restricted the ProductID to 710 in the sub query we have all kinds of product IDs returned.
Read more:
http://technet.microsoft.com/en-us/library/ms187074(v=sql.105).aspx
http://msdn.microsoft.com/en-us/ms188047.aspx
http://msdn.microsoft.com/en-us/ms177682.aspx
http://msdn.microsoft.com/en-us/ms173545.aspx