IN vs ANY operator in PostgreSQL
SqlPostgresqlSql InSql Problem Overview
What is the difference between IN
and ANY
operator in PostgreSQL?
The working mechanism of both seems to be the same. Can anyone explain this with an example?
Sql Solutions
Solution 1 - Sql
(Strictly speaking, IN
and ANY
are Postgres "constructs" or "syntax elements", rather than "operators".)
Logically, quoting the manual:
> IN
is equivalent to = ANY
.
But there are two syntax variants of IN
and two variants of ANY
. Details:
IN
taking a set is equivalent to = ANY
taking a set, as demonstrated here:
But the second variant of each is subtly different. The second variant of the ANY
construct takes an array (must be an actual array type), while the second variant of IN
takes a comma-separated list of values. This leads to different restrictions in passing values and can also lead to different query plans in special cases:
- Index not used with
=any()
but used within
- https://stackoverflow.com/questions/34600567/pass-multiple-sets-or-arrays-of-values-to-a-function/34601242#34601242
- https://stackoverflow.com/questions/69590314/how-to-match-elements-in-an-array-of-composite-type/69590967
ANY
is more versatile
The ANY
construct is far more versatile, as it can be combined with various operators, not just =
. Example:
SELECT 'foo' LIKE ANY('{FOO,bar,%oo%}');
For a big number of values, providing a set scales better for each:
Related:
Inversion / opposite / exclusion
"Find rows where id
is in the given array":
SELECT * FROM tbl WHERE id = ANY (ARRAY[1, 2]);
Inversion: "Find rows where id
is not in the array":
SELECT * FROM tbl WHERE id <> ALL (ARRAY[1, 2]);
SELECT * FROM tbl WHERE id <> ALL ('{1, 2}'); -- equivalent array literal
SELECT * FROM tbl WHERE NOT (id = ANY ('{1, 2}'));
All three equivalent. The first with ARRAY constructor, the other two with array literal. The type of the untyped array literal is derived from (known) element type to the left.
In other constellations (typed array value / you want a different type / ARRAY constructor for a non-default type) you may need to cast explicitly.
Rows with id IS NULL
do not pass either of these expressions. To include NULL
values additionally:
SELECT * FROM tbl WHERE (id = ANY ('{1, 2}')) IS NOT TRUE;
Solution 2 - Sql
There are two obvious points, as well as the points in the other answer:
-
They are exactly equivalent when using sub queries:
SELECT * FROM table WHERE column IN(subquery); SELECT * FROM table WHERE column = ANY(subquery);
On the other hand:
-
Only the
IN
operator allows a simple list:SELECT * FROM table WHERE column IN(… , … , …);
Presuming they are exactly the same has caught me out several times when forgetting that ANY
doesn’t work with lists.