PostgreSQL - IN vs ANY

Question

I have tried both:

1. smthng = ANY (select id from exmplTable)

2. smthng IN (select id from exmplTable)

and I am getting the same results for my data.

Is there any difference for the two expressions?

Velites

From the docs: SOME is a synonym for ANY. IN is equivalent to = ANY. – Kerrill

Have a look, its explained here: postgresql.org/docs/current/static/functions-comparisons.html – Medievalism

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

Answer 1

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)

Nogas

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: https://mcmap.net/q/28710/-in-vs-any-operator-in-postgresql and dba.stackexchange.com/q/125413/3684 – Bandstand

@ErwinBrandstetter: =ANY(ARRAY) is different creature .. although the syntax is same – Nogas

Answer 2

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

Royo

This is a good thing to know when querying with dynamic set of data (Pg/PLSQL, application programing, etc..) – Ligni

This is different operator. There is difference between = ANY ( subquery ) and = ANY ( array ). ` = ANY( '{}' )` is example of usage of array. – Nogas

Answer 3

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} 

Carbazole