IN vs ANY operator in PostgreSQL

by

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

  • How to use ANY instead of IN in a WHERE clause?

IN taking a set is equivalent to = ANY taking a set, as demonstrated here:

  • PostgreSQL – IN vs ANY

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 with in
  • Pass multiple sets or arrays of values to a function
  • How to match elements in an array of composite type?

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:

  • Optimizing a Postgres query with a large IN

Related:

  • Can PostgreSQL index array columns?

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;

Leave a Comment