Difference between IN and ANY operators in SQL

IN - It is easy to understand. The query should select only those values which are specified in 'IN' clause. Now, let us understand 'ANY' with a query. ANY means it should be greater or less than any of the values in the list.

Assume a Orders table which has OrderID from 1 to 10

Observer the below query:
select OrderID from Orders
where OrderID < ANY (3,5,7)

The answer to above query is :
OrderID
1,2,3,4,5,6

Explanation :The query says find OrderIDs which are less than ANY of the specified values. So the database searches and includes OrderID as follows:
Is 1<3- Yes hence OrderID 1 is included
Is 2<3- Yes hence OrderID 2 is included
Is 3<3- No, is 3<5 -Yes (as 5 is specified value), hence OrderID 3 is included
Is 4<3- No, is 4<5 -Yes, hence OrderID 4 is included
Is 5<3- No, is 5<5 -No, is 5<7(as 5 is specified value)-Yes hence OrderID 5 is included
Is 6<3- No, is 6<5 -No, is 6<7-Yes hence OrderID 6 is included
Is 7<3- No, is 7<5 -No, is 7<7-No hence OrderID 7 is NOT included as no more values in specified list to compare
Is 8<3- No, is 8<5 -No, is 8<7-No hence OrderID 8 is NOT included as no more values in specified list to compare
Is 9<3- No, is 9<5 -No, is 9<7-No hence OrderID 9 is NOT included as no more values in specified list to compare
Is 9<3- No, is 9<5 -No, is 9<7-No hence OrderID 9 is NOT included as no more values in specified list to compare

Apply the same logic for greater than
select OrderID from Orders
where OrderID > ANY (3,5,7)

The answer to above query is :
OrderID
4,5,6,7,8,9,10

The ANY and ALL operators are used with a WHERE or HAVING clause.

The ANY operator returns true if any of the subquery values meet the condition.

The ALL operator returns true if all of the subquery values meet the condition.

= ANY is equivalent to IN operator. "<>, <, >, <=, or >=" one of them can be placed before ANY operator. Note that the <> ANY operator is different from NOT IN.

The ANY and ALL operators are used with a WHERE or HAVING clause.

The ANY operator returns true if any of the subquery values meet the condition.

The ALL operator returns true if all of the subquery values meet the condition.

When we are comparing any column value using "IN" some set say {value1,value2 ...} then the column value must be present in the set but in case of ANY we compare like this:

col.value > ANY ({value1,value2,...})

then the value must be greater than any one of the set value.

in case of "ALL"

col.value> ALL({value1,value2,...})

the value must be greater than all the values in the set.

Refer to the following images for better understanding:

• this is my database employee
• all query
• any query
• in query

(in) is a special kind of operator which is use to pick value one by one from list of values which we have specified.while (any) is use with where clause