Re: Diferences between IN and EXISTS?

Em 04/02/2013 07:35, zeljko escreveu:
> Edson Richter wrote:
>
>> Hi!
>>
>> Assumptions: PostgreSQL 9.2.2, Win64, already run vacumm full freeze
>> analyze. No problems in the database. I know there are 1247 records to
>> be found.
>> Why does these queries return different results:
>>
>>
>> select count(*) from parcela
>> where id not in (select parcela_id from cadastroservicoparcela);
> I'm always using
> WHERE NOT id in (blabla) and never had such problems.

There relevant portion of the problem is here:

|IN|predicate (unlike|EXISTS|) is trivalent, i. e. it can
return|TRUE|,|FALSE|or|NULL|:

* |TRUE|is returned when the non-|NULL|value in question is found in
the list
* |FALSE|is returned when the non-|NULL|value is not found in the
list/and the list does not contain|NULL|values/
* |NULL|is returned when the value is|NULL|, or the non-|NULL|value is
not found in the list/and the list contains at least one|NULL|value/

The 3rd point is the one I was hitting.

Edson

>
> zeljko
>
>