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 expresions?

Answer 1

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

Answer 2

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)