Difference between IN and ANY operators in SQL

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 >=

Maybe for better understanding, these two conditions are equivalent. It's a matter of taste which one you use (provided the RDBMS supports both of them)

... WHERE x IN (SELECT Y FROM THE_TABLE)  
... WHERE x =ANY (SELECT Y FROM THE_TABLE) 

and these also

... WHERE x NOT IN (SELECT Y FROM THE_TABLE) 
... WHERE x <>ALL (SELECT Y FROM THE_TABLE) 

Actually my personal habit is to use IN for list expression (like WHERE x IN (2,4,6,8) and =ANY, resp. <>ALL for sub-queries.

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.

While using all

SELECT empno, sal FROM emp WHERE sal > ALL (2000, 3000, 4000);

 EMPNO        SAL

---

  7839       5000

It will return result equivalent to query:

SELECT empno, sal FROM emp WHERE sal > 2000 AND sal > 3000 AND sal > 4000;

While using any

SELECT empno, sal FROM emp WHERE sal > ANY (2000, 3000, 4000);

 EMPNO        SAL

---

  7566       2975
  7698       2850
  7782       2450
  7788       3000
  7839       5000
  7902       3000

Returns a result same as

SELECT empno, sal FROM emp WHERE sal > 2000 OR sal > 3000 OR sal > 4000;