Use of All and ANY Operator in MS SQL

By AMIT S VAISHNAV
main Image
All and Any operator are used in Subquery.

When a scalar value has to be compared with a single-column set of values, we usually use IN or JOINs. In addition to that, we can extend the comparison by using ANY and ALL operators which have rarely been used. These two operators work differently, understanding them would be beneficial to all of us, avoiding complexity of queries.

ANY operator returns true when the scalar value matches with any value in the single-column set of values.
ALL operator returns true when the scalar value matches with all value in the single-column set of values. The comparison can be extended with <, >, and < > operators too.

We will understand this by example as mentioned below:

Consider two tables as Table1 (tb1Id)
Table2 (tb2Id)

We have tb1Id value as 1,2,3,4 and 5 in Table1 and tb2Id value as 1,3 and 5 in Table2

Now to understand this concept we will write study some query and their output.
-------------------------------------------------------------------------------------------------
Example 1 :
SELECT tb1Id
FROM Table1 WHERE tb1Id = ANY (SELECT tb2Id FROM Table2)

Here the inner query will return value 1,3 and 5.
This checks:
(Table1:tb1Id 1 = Table2:tb2Id 1)
OR (Table1:tb1Id 1 = Table2:tb2Id 3)
OR (Table1:tb1Id 1 = Table2:tb2Id 5)

Here in above comparision first condition is true,Hence we get 1 in output.Similarly for value 2,3,4 and 5 we get true condition for 3 and 5.

Query Output is 1,3 and 5

In simple words tb1Id is compared with all the value of tb2Id return by inner query and if that value is present in inner query result then outer query will also return result.
-------------------------------------------------------------------------------------------------
Example 2 :
SELECT tb1Id
FROM Table1 WHERE tb1Id = ALL (SELECT tb2Id FROM Table2)

Here the inner query will return value 1,3 and 5.
This checks:
(Table1:tb1Id 1 = Table2:tb2Id 1)
AND (Table1:tb1Id 1 = Table2:tb2Id 3)
AND (Table1:tb1Id 1 = Table2:tb2Id 5)

Here in above comparision each condition is false,Hence we get nothing in output.Similarly for value 2,3,4 and 5 we get false condition.
Query Output is empty set
-------------------------------------------------------------------------------------------------
Example 3 :
SELECT tb1Id
FROM Table1 WHERE tb1Id >= ANY (SELECT tb2Id FROM Table2)

Here the inner query will return value 1,3 and 5.
This checks:
(Table1:tb1Id 1 >= Table2:tb2Id 1)
OR (Table1:tb1Id 1 >= Table2:tb2Id 3)
OR (Table1:tb1Id 1 >= Table2:tb2Id 5)

Here in above comparision first condition is true,Hence we get 1 in output.Similarly for value 2,3,4 and 5 we get true condition.
Query Output is 1,2,3,4 and 5
-------------------------------------------------------------------------------------------------
Example 4 :
SELECT tb1Id
FROM Table1 WHERE tb1Id >= ALL (SELECT tb2Id FROM Table2)

Here the inner query will return value 1,3 and 5.
This checks:
(Table1:tb1Id 1 >= Table2:tb2Id 1)
AND (Table1:tb1Id 1 >= Table2:tb2Id 3)
AND (Table1:tb1Id 1 >= Table2:tb2Id 5)

Here in above comparision each condition is false,Hence we get nothing in output.Similarly for value 2,3 and 4 we get false condition.But for value 5 comparision as shown below:

(Table1:tb1Id 5 >= Table2:tb2Id 1)
AND (Table1:tb1Id 5 >= Table2:tb2Id 3)
AND (Table1:tb1Id 5 >= Table2:tb2Id 5)


In this 5 is greater than equal to 1,3 and 5.So all condition are true so result is true.
Query Output is 5

I hope this article will help you.