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 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.
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); |
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