IN vs ANY operator in PostgreSQL

Question

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?

Zrike

Possible duplicate of postgreSQL - in vs any – Olwen

Does this answer your question? Difference between in and any operators in sql – Regime

Answer 1

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

Disingenuous

It'd be nice to explicitly clarify that the results of the second variants will always be the same. I'm 99% sure that is in fact the case but the answer doesn't seem to state it. Meaning that SELECT * from mytable where id in (1, 2, 3) will always result in the same rows as SELECT * from mytable where id = ANY('{1, 2, 3}'), even if they potentially might have different query plans. – Kelbee

ANY cannot be combined with the != operator. I don't think it's documented, but select * from foo where id != ANY (ARRAY[1, 2]) is not the same as select * from foo where id NOT IN (1, 2). On the other hand, select * from foo where NOT (id = ANY (ARRAY[1, 2])) works as expected. – Quadruplicate

@qris: ANY can be combined with the != operator. But there is more to it. I added a chapter above. (Note that <> is the operator in standard SQL - though != is accepted as well in Postgres.) – Disingenuous

How does the last version that includes NULL values work? Would WHERE id = ANY (ARRAY[1, 2]) OR id IS NULL; work just as well? – Pluralism

@dvtan: (id = ...) IS NOT TRUE works because id = ... only evaluates to TRUE if there is an actual match. Outcomes FALSE or NULL pass our test. See: https://mcmap.net/q/28734/-best-way-to-check-for-quot-empty-or-null-value-quot. Your added expression tests for something else. This would be equivalent WHERE id <> ALL (ARRAY[1, 2]) OR id IS NULL; – Disingenuous

It's said that index is not used with =ANY('{1, 2, 3}'), but I just tried to do both WHERE key = ANY ('{1, 3, 5, ...49}') as well as WHERE key IN (1, 3, 5, ...49) on a big table and the EXPLAIN output is absolutely the same and it uses index scan. Even more, it says =ANY in the explain output even for IN. I'm on PostgreSQL 12.4 – Chokecherry

@seriyPS: It's said that index is not used ...? I am not saying anything of the sort. If you mean "Index not used with =any() but used with in", that's just the title of a related question - linking to my related answer. – Disingenuous

@erwin-brandstetter ah, ok, sorry. But I think others may be confused the same way as I did, so I'll leave the comment where it is. – Chokecherry

@ErwinBrandstetter I found out this commit patched. not sure how faster can it be to speed IN clause. git.postgresql.org/gitweb/… – Joellejoellen

@jian: That should help. But the release notes only mention a related improvement for NOT IN. I'll have to test. But not now ... – Disingenuous

Answer 2

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.

Mansuetude

For the "simple list" case, WHERE id = ANY(array[1,2]) works. – Medico

or, using the example in this answer, WHERE id = ANY(array(<subquery>)) – Pisano

Answer 3

'in' is syntaxis sugar, you can take a look to plan analyse and will see that 'in' will be transform to =ANY('...,...')::yourType[]

Heteronomous

I don't think this is true. I have a query that was taking 26 seconds to execute using an IN constraint (values from a subquery), but when I switched to an = ANY(array(<subquery>)) the same query took a couple hundred milliseconds. The query plan definitely changed. PG 12.3 – Pisano

did the first attempt warm the cache maybe? run them both twice and see if the timing is different – Espinoza