"Jim C. Nasby" <jim@nasby.net> writes:
> On Tue, May 06, 2003 at 09:45:07AM -0400, Tom Lane wrote:
>> RTFM...
> As someone pointed out, the documentation says you can't. In this case
> the docs are wrong (I've added a note).
Perhaps you should have read to the end of the section.
>>> BTW, does postgresql handle IN and EXISTS differently?
>>
>> Yes.
>
> They appear to operate the same... what's different?
Supposing that tab1.col1 contains 1, NULL, 2, then for an outer
table row where col2 = 42
WHERE outer.col2 IN (SELECT col1 FROM tab1)
will yield NULL (not FALSE). But
WHERE EXISTS(SELECT * FROM tab1 WHERE col1 = outer.col2)
will yield FALSE (not NULL).
The distinction doesn't matter at the top level of WHERE, but it
matters a lot underneath a NOT ...
regards, tom lane
