The IN and ANY constructs works with different kinds of collections to the
right. This is the current situation:
SUB QUERY VALUE LIST ARRAY
IN X X
ANY X X
The SQL standard specifies that = ANY should be equivalent to IN
(including the value list case) but it is not.
Take for example this:
select 'a' = ANY ('{a,b}');
which according to the SQL spec should evaluate to False but in pg
evaluates to True. Look for at this run in pg:
dennis=# select 'a' IN ('{a,b}');?column?
----------f
(1 rad)
dennis=# select 'a' = ANY ('{a,b}');?column?
----------t
I only have the SQL200x draft to check. And in section 8.4 it's
clearly stated that "rvc IN ipv" is equivalent to "rvc = ANY ipv" and an
ipv is defined to be either a subquery or a value list.
*** some time later ***
Hmm, the draft seems to be broken since I can only find ANY defined for
subqueries in other sections, and not for value lists. Strange but not
uncommon. Now I don't know what the standard says about this. Maybe
someone with the sql99 spec wants to check.
The question is also what makes sense. I'm not too fond of the above
situation, but I'm not sure if anything can be done now.
--
/Dennis Björklund