PostgreSQL - IN vs ANY

Question

I have tried both:

smthng = ANY (select id from exmplTable)
smthng IN (select id from exmplTable)

and I am getting the same results for my data.

Is there any difference for the two expressions?

From the [docs](http://www.postgresql.org/docs/current/static/functions-subquery.html): *`SOME` is a synonym for `ANY`. `IN` is equivalent to `= ANY`.* — pozs, May 15 '15 at 16:03
Have a look, its explained here: http://www.postgresql.org/docs/current/static/functions-comparisons.html — Roger, May 15 '15 at 16:04
Does this answer your question? [Difference between in and any operators in sql](https://stackoverflow.com/questions/3699356/difference-between-in-and-any-operators-in-sql) — philipxy, Feb 16 '20 at 11:06

score 41 · Accepted Answer · answered May 15 '15 at 15:59

No, in these variants are same:

You can see - the execution plans are same too:

postgres=# explain select * from foo1 where id in (select id from foo2);
┌──────────────────────────────────────────────────────────────────┐
│                            QUERY PLAN                            │
╞══════════════════════════════════════════════════════════════════╡
│ Hash Semi Join  (cost=3.25..21.99 rows=100 width=4)              │
│   Hash Cond: (foo1.id = foo2.id)                                 │
│   ->  Seq Scan on foo1  (cost=0.00..15.00 rows=1000 width=4)     │
│   ->  Hash  (cost=2.00..2.00 rows=100 width=4)                   │
│         ->  Seq Scan on foo2  (cost=0.00..2.00 rows=100 width=4) │
└──────────────────────────────────────────────────────────────────┘
(5 rows)

postgres=# explain select * from foo1 where id = any (select id from foo2);
┌──────────────────────────────────────────────────────────────────┐
│                            QUERY PLAN                            │
╞══════════════════════════════════════════════════════════════════╡
│ Hash Semi Join  (cost=3.25..21.99 rows=100 width=4)              │
│   Hash Cond: (foo1.id = foo2.id)                                 │
│   ->  Seq Scan on foo1  (cost=0.00..15.00 rows=1000 width=4)     │
│   ->  Hash  (cost=2.00..2.00 rows=100 width=4)                   │
│         ->  Seq Scan on foo2  (cost=0.00..2.00 rows=100 width=4) │
└──────────────────────────────────────────────────────────────────┘
(5 rows)

Note that while this is true for the form taking a *set*, there is a second form for each `IN ()` and `= ANY()` and those are not completely equivalent. Consider: http://stackoverflow.com/a/34627688/939860 and http://dba.stackexchange.com/q/125413/3684 — Erwin Brandstetter, Jan 07 '16 at 14:26
@ErwinBrandstetter: =ANY(ARRAY) is different creature .. although the syntax is same — Pavel Stehule, Jan 07 '16 at 17:24

score 22 · Answer 2 · answered Apr 25 '18 at 17:59

22

This may be an edge case but:

select * from myTable where id IN ()

will produce: ERROR: syntax error at or near ")"

but

select * from myTable where id = ANY('{}');

Will return an empty result set

answered Apr 25 '18 at 17:59

WigglyWorld

705
6
13

1

This is a good thing to know when querying with dynamic set of data (Pg/PLSQL, application programing, etc..) – sweet suman Nov 14 '19 at 07:13

score 3 · Answer 3 · answered Feb 12 '21 at 09:52

Note: verified and working

Create Table: user

CREATE TABLE user (
  id serial PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  skills VARCHAR[50]
);

Insert data

insert into user (username, skills) values ('user1', '{java, python}');
insert into user (username, skills) values ('user2', '{python}');
insert into user (username) values ('user3');

In the above table user when we search for 'python' in column skills, then it will return 2 rows. As it matches python in the first 2 rows.

SELECT * FROM user where  'python' = ANY (skills);

output

 1 | user1 | {java, python}
 2 | user2 | {python}

PostgreSQL - IN vs ANY

3 Answers3

Linked