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

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

Solution 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

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