SQL Server ANY and ALL Operators

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. Here are few sample queries to understand them:

   1: -- Lets create two tables and inser values

   2: USE tempdb

   3: GO

   4: CREATE TABLE Table1 (Id int)

   5: GO

   6: INSERT INTO Table1

   7: VALUES (1), (2), (3), (4), (5)

   8:  

   9: CREATE TABLE Table2 (Id int)

  10: GO

  11: INSERT INTO Table2 

  12: VALUES (1), (3), (5)

  13:  

  14: -- =ANY sees whether the scalar value is

  15: -- equal to any from Table2

  16: -- Eg. This checks: 

  17: -- (Table1:Id1 = Table2:Id1)

  18: -- OR (Table1:Id1 = Table2:Id3)

  19: -- OR (Table1:Id1 = Table2:Id5)

  20: SELECT Id FROM Table1 

  21: WHERE Id =ANY (SELECT Id FROM Table2)

  22: -- Finally, code returns 1, 3, and 5.

  23:  

  24: -- =ALL sees whether the scalar value is

  25: -- equal with all values in Table2.

  26: -- Eg. (Table1:Id1 = Table2:Id1)

  27: -- AND (Table1:Id1 = Table2:Id3)

  28: -- AND (Table1:Id1 = Table2:Id5)

  29: SELECT Id FROM Table1 

  30: WHERE Id =ALL (SELECT Id FROM Table2)

  31: -- Code returns nothing.

  32:  

  33: -- >=ANY sees whether the scalar value is

  34: -- greater than any value in Table2

  35: -- Eg. (Table1:Id1 > Table2:Id1)

  36: -- OR  (Table1:Id1 > Table2:Id3)

  37: -- OR (Table1:Id1 > Table2:Id5)

  38: -- Since the smallest one in Table2 is 1,

  39: -- As long as the Table1:Id is >= 1

  40: -- It is added to the resultset.

  41: SELECT Id FROM Table1 

  42: WHERE Id >=ANY (SELECT Id FROM Table2)

  43: -- Code returns 1, 2, 3, 4, and 5

  44:  

  45: -- >=ALL sees whether the scalar value is

  46: -- greater than all in Table2

  47: -- Eg. (Table1:Id1 > Table2:Id1)

  48: -- AND (Table1:Id1 > Table2:Id3)

  49: -- AND (Table1:Id1 > Table2:Id5)

  50: -- Since the highest number in Table2 is 5,

  51: -- As long as Table1:Id is >= 5, 

  52: -- It is added to the resultset.

  53: SELECT Id FROM Table1 

  54: WHERE Id >=ALL (SELECT Id FROM Table2)

  55: -- Code returns 5 only.

You should notice that =ANY is equal to IN. But the opposite of it, <>ANY and NOT IN are NOT equal. Remember, the equal one for NOT IN is, <>ALL.