Re: [PERFORM] Hypothetical suggestions for planner, indexing improvement

"Jim C. Nasby" <jim(at)nasby(dot)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