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 >=
IN - Equals 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 the 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 Employee Controlling Other Emp?
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 Emps Who are Earning more than the least paid of 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 is also used instead of ANY.
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.