Difference between IN and ANY operators in SQL

Question

What is the difference between IN and ANY operators in SQL?

Answer 1

SQL> SQL> -- Use the ANY operator in a WHERE clause to compare a value with any of the values in a list. SQL> 

SQL> -- You must place an =, <>, <, >, <=, or >= operator before ANY.

SQL> SELECT *   2  FROM employee   3  WHERE salary > ANY (2000, 3000, 4000); 

For In Operator

SQL> -- Use the IN operator in a WHERE clause to compare a value with any of the values in a list. SQL> SELECT *   2  FROM employee   3  WHERE salary IN (2000, 3000, 4000); 

But with the IN operator you cannot use =, <>, <, >, <=, or >=

Answer 2

IN - Equal to anything in the list

ANY - Compares value to each value returned by the sub query.

ALL - Compares value to every value returned by the sub query.

For example:

IN:

Display the details of all employees whose salaries are matching with the least investments of departments?

 Select Ename, Sal, Deptno   from Emp   Where Sal IN (Select Min(Sal)                 From Emp                 Group By Deptno); 

ANY:

< ANY means less than the maximum value in the list.

Get the details of all employees who are earning less than the highest earning manager?

 Select Empno, Ename, Job, Sal   From Emp  Where Sal < Any (Select Distinct MGR                    From Emp); 

> ANY means more than the minimum value in the list.

Get the details of all employees who are earning more than the least paid in Department 10?

 Select Empno, Ename, Job, Sal   From Emp  Where Sal > Any (Select Min(Sal)                    From Emp                    Where Deptno 10); 

= ANY is equivalent to in operator.

Note: SOME can also be used instead of ANY.