Thread: Difference between "in (...)" and "= any(...)" queries when using arrays

Hi all!

I was playing with "in (...)"  and "= any (...)" queries and found a
difference between them and I wonder:

why this works:

select * from table_of_integers where integer_column = any (array[5,6]);

and this doesn't:

select * from table_of_integers where integer_column in (array[5,6]);

Although documentation says:

9.20.4. ANY/SOME

[...]
SOME is a synonym for ANY. IN is equivalent to = ANY.
[...]

I thought that if IN is equivalent to = any, both queries above should work.

Am I missing something?

Thanks in advance.



--
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://fxjr.blogspot.com
http://twitter.com/franciscojunior

Francisco Figueiredo Jr. wrote:
> Hi all!
>
> I was playing with "in (...)"  and "= any (...)" queries and found a
> difference between them and I wonder:
>
> why this works:
>
> select * from table_of_integers where integer_column = any (array[5,6]);

This checks if integer_column matches any value in the specified array.

> select * from table_of_integers where integer_column in (array[5,6]);

This checks if integer_column IS an array[5,6] (which it isn't).

You probably want ... IN (5,6)

> SOME is a synonym for ANY. IN is equivalent to = ANY.

I think this is probably talking with respect to sub-queries.

--
   Richard Huxton
   Archonet Ltd

On Tue, May 12, 2009 at 05:02, Richard Huxton <dev@archonet.com> wrote:
> Francisco Figueiredo Jr. wrote:
>>
>> Hi all!
>>
>> I was playing with "in (...)"  and "= any (...)" queries and found a
>> difference between them and I wonder:
>>
>> why this works:
>>
>> select * from table_of_integers where integer_column = any (array[5,6]);
>
> This checks if integer_column matches any value in the specified array.
>
>> select * from table_of_integers where integer_column in (array[5,6]);
>
> This checks if integer_column IS an array[5,6] (which it isn't).
>
> You probably want ... IN (5,6)
>
>> SOME is a synonym for ANY. IN is equivalent to = ANY.
>
> I think this is probably talking with respect to sub-queries.
>


Ahhhh, thank you very much for your feedback and for explaining it, Richard.

Now I understand it better.

--
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://fxjr.blogspot.com
http://twitter.com/franciscojunior