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

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

SELECT  *FROM    mytableWHERE   x <= ANY        (        SELECT  y        FROM    othertable        )

is the same as:

SELECT  *FROM    mytable mWHERE   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.

IN- Equal to any member in the listANY- Compare value to **each** value returned by the subqueryALL- 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_idFROM employeesWHERE 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_idFROM employeesWHERE 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_idFROM employeesWHERE salary <ALL                (SELECT salary                 FROM employees                 WHERE job_id = 'IT_PROG')AND job_id <> 'IT_PROG';

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

Hope it helps.-Noorin Fatima

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.