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:
Learn something new today..
ANY == associated with OR
ALL == associated with AND
great explanation..
Thank you..
Great, glad to hear that you learnt something from this post.
Cheers!
Short and easy to understand...this solved my doubt!!
Post a Comment