Martijn van Oosterhout <kleptog@svana.org> writes:
> On postgres at least, exists is faster than in.
> They are equivalent though.
Not really.
For one thing, IN can return a NULL (don't know) result; EXISTS cannot.
regression=# select * from foo;
f1
----
1
(2 rows)
regression=# select 2 in (select f1 from foo);
?column?
----------
(1 row)
regression=# select exists (select 1 from foo where f1 = 2);
?column?
----------
f
(1 row)
Yes, this behavior is spec-compliant. Think: we don't know what the NULL
represents, therefore we don't know whether 2 is in the column or not,
therefore IN should return NULL.
regards, tom lane