Re: Re: Does PostgreSQL support EXISTS?

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