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?

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) 

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