Difference between IN and ANY operators in SQL

Question

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

Interinsurance

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

Pedicle

is there a reason to use one over the other when doing = ANY? (of course use ANY for the other operators besides = because IN is only for =) – Ranunculus

As @Ranunculus I would also like to know if ANY/ALL offers any special cases where it for =ANY or <>ALL is different from IN/NOT IN? And for > and < the use of MAX/MIN in a scalar sub query? I mean is it a pure matter of taste, or are there special cases where they act differently? I tried with some null values and empty sets but have not found a difference. – Gramercy

There IS a very special case where they are clearly better, the odd "=ALL" and "<>ANY", that both tests if all in the set are equal AND if so if equal/different to the expression value. But should you ever be in need of that odd construction, then "...WHERE value=ALL (SELECT a FROM Tbl)" is much more readable than e.g. "...WHERE value=(SELECT MIN(a) FROM Tbl HAVING MIN(a)=MAX(a))" - i do not think I ever needed that construction though... – Gramercy

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.

Deanndeanna

In other words, Is "where Val< ANY(10,20,33)" the same like "where Val < 10 OR Val < 20 OR Val < 30"? – Vaniavanilla

Answer 3

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.

Fordham

This is the simpler (and better) answer IMO – Yield

Answer 4

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;

Marbut

But be aware that they act quite oddly on empty sets... All six "ALL" variants always returns true, and all six "ANY" variants always return false. Try e.g. " SELECT 5 WHERE 4=ALL (SELECT 1 WHERE 2=3) " – Gramercy

Answer 5

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

Mangrum

it's clear now. – Premillennial

Answer 6

ANY and ALL OPERATOR IN SQL SERVER 2008R2.

Using the > comparison operator as an example, >ALL means greater than every value--in other words, greater than the maximum value. For example, >ALL (1, 2, 3) means greater than 3. >ANY means greater than at least one value, that is, greater than the minimum. So >ANY (1, 2, 3) means greater than 1.

Similarly, >ANY means that for a row to satisfy the condition specified in the outer query, the value in the column that introduces the subquery must be greater than at least one of the values in the list of values returned by the subquery.

Banbury

Answer 7

With ANY, you need an operator:

WHERE X > ANY (SELECT Y FROM Z)

With IN, you can't. It's always testing for equality.

Jaf

Answer 8

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

Vice

Answer 9

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.

Renner

Answer 10

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

Biflagellate

Answer 11

(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

Recital