Tuesday, March 9, 2010

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.

3 comments:

Udaya Ravi said...

Learn something new today..

ANY == associated with OR
ALL == associated with AND

great explanation..

Thank you..

Dinesh Priyankara said...

Great, glad to hear that you learnt something from this post.

Cheers!

Ashish Sinha said...

Short and easy to understand...this solved my doubt!!