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

Question

Hi all, 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. CheerZ!

Answer 1

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 2

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 3

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

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