eidylon eidylon - 2 years ago 88
SQL Question

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

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 Source

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

FROM    mytable
WHERE   x <= ANY
        SELECT  y
        FROM    othertable

is the same as:

FROM    mytable m
        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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download