Re: Re: Does PostgreSQL support EXISTS?

Martijn van Oosterhout <kleptog(at)svana(dot)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