Oracle: '= ANY()' vs. 'IN ()'

Question

I just stumbled upon something in ORACLE SQL (not sure if it's in others), that I am curious about. I am asking here as a wiki, since it's hard to try to search symbols in google...

I just found that when checking a value against a set of values you can do

WHERE x = ANY (a, b, c)

As opposed to the usual

WHERE x IN (a, b, c)

So I'm curious, what is the reasoning for these two syntaxes? Is one standard and one some oddball Oracle syntax? Or are they both standard? And is there a preference of one over the other for performance reasons, or ?

Just curious what anyone can tell me about that '= ANY' syntax.

Answer 1

ANY (or its synonym SOME) is a syntax sugar for EXISTS with a simple correlation:

SELECT  *
FROM    mytable
WHERE   x <= ANY
        (
        SELECT  y
        FROM    othertable
        )

is the same as:

SELECT  *
FROM    mytable m
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    othertable o
        WHERE   m.x <= o.y
        )

With the equality condition on a not-nullable field, it becomes similar to IN.

All major databases, including SQL Server, MySQL and PostgreSQL, support this keyword.

Answer 2

IN- Equal to any member in the list
ANY- Compare value to **each** value returned by the subquery
ALL- Compare value to **EVERY** value returned by the subquery

<ANY() - less than maximum
>ANY() - more than minimum
=ANY() - equivalent to IN
>ALL() - more than the maximum
<ALL() - less than the minimum

eg:

Find the employees who earn the same salary as the minimum salary for each department:

SELECT last_name, salary,department_id
FROM employees
WHERE salary IN (SELECT MIN(salary)
                 FROM employees
                 GROUP BY department_id);

Employees who are not IT Programmers and whose salary is less than that of any IT programmer:

SELECT employee_id, last_name, salary, job_id
FROM employees
WHERE salary <ANY
                (SELECT salary
                 FROM employees
                 WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

Employees whose salary is less than the salary ofall employees with a job ID of IT_PROG and whose job is not IT_PROG:

SELECT employee_id,last_name, salary,job_id
FROM employees
WHERE salary <ALL
                (SELECT salary
                 FROM employees
                 WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

....................

Hope it helps. -Noorin Fatima

Answer 3

To put it simply and quoting from O'Reilly's "Mastering Oracle SQL":

"Using IN with a subquery is functionally equivalent to using ANY, and returns TRUE if a match is found in the set returned by the subquery."

"We think you will agree that IN is more intuitive than ANY, which is why IN is almost always used in such situations."

Hope that clears up your question about ANY vs IN.

Answer 4

I believe that what you are looking for is this:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/opt_ops.htm#1005298 (Link found on Eddie Awad's Blog) To sum it up here:

last_name IN ('SMITH', 'KING', 'JONES')

is transformed into

last_name = 'SMITH' OR last_name = 'KING' OR last_name = 'JONES'

while

salary > ANY (:first_sal, :second_sal)

is transformed into

salary > :first_sal OR salary > :second_sal

The optimizer transforms a condition that uses the ANY or SOME operator followed by a subquery into a condition containing the EXISTS operator and a correlated subquery

Answer 5

The ANY syntax allows you to write things like

WHERE x > ANY(a, b, c)

or event

WHERE x > ANY(SELECT ... FROM ...)

Not sure whether there actually is anyone on the planet who uses ANY (and its brother ALL).

Answer 6

A quick google found this http://theopensourcery.com/sqlanysomeall.htm

Any allows you to use an operator other than = , in most other respect (special cases for nulls) it acts like IN. You can think of IN as ANY with the = operator.

Answer 7

This is a standard. The SQL 1992 standard states

8.4 <in predicate>

[...]

<in predicate> ::=
    <row value constructor>
      [ NOT ] IN <in predicate value>

[...]

2) Let RVC be the <row value constructor> and let IPV be the <in predicate value>.

[...]

4) The expression

  RVC IN IPV

is equivalent to

  RVC = ANY IPV  

So in fact, the <in predicate> behaviour definition is based on the 8.7 <quantified comparison predicate>. In Other words, Oracle correctly implements the SQL standard here

Answer 8

Perhaps one of the linked articles points this out, but isn't it true that when looking for a match (=) the two return the same thing. However, if looking for a range of answers (>, <, etc) you cannot use "IN" and would have to use "ANY"...

I'm a newb, forgive me if I've missed something obvious...

Answer 9

MySql clears up ANY in it's documentation pretty well:

The ANY keyword, which must follow a comparison operator, means “return TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns.” For example:

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

Suppose that there is a row in table t1 containing (10). The expression is TRUE if table t2 contains (21,14,7) because there is a value 7 in t2 that is less than 10. The expression is FALSE if table t2 contains (20,10), or if table t2 is empty. The expression is unknown (that is, NULL) if table t2 contains (NULL,NULL,NULL).

https://dev.mysql.com/doc/refman/5.5/en/any-in-some-subqueries.html

Also Learning SQL by Alan Beaulieu states the following:

Although most people prefer to use IN, using = ANY is equivalent to using the IN operator.

Answer 10

Why I always use any is because in some oracle or mssql versions IN list is limited by 1000/999 elements. While = any () is not limited by 1000.

Nobody likes their sql query crashing a web request. So there is a practical difference.

Second reason it is the more modern form. As it correlates with expressions like > all (...).

Third reason is somehow for me as non-native English speaker it appears more natural to use "any" and "all" than to use IN.