Re: Diferences between IN and EXISTS? - Mailing list pgsql-general

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
>
>