Re: Diferences between IN and EXISTS?

Ok, I get it. Good education!

Thank you very much, saved me a big headache!

Edson

Em 03/02/2013 03:06, Pavel Stehule escreveu:
> Hello
>
> 2013/2/3 Edson Richter <edsonrichter(at)hotmail(dot)com>:
>> 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);
>>
>> count
>> --------
>> 0
>>
>>
>> select count(*) from parcela
>> where not exists (select 1 from cadastroservicoparcela where parcela_id =
>> parcela.id);
>>
>> count
>> --------
>> 1247
>>
>>
>> I can't figure out, I did expect that the first one returns exactly same
>> result!
>> Could null values in cadastroservicoparcela.parcela_id affect the first
>> query?
>>
> sure
>
> http://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null
>
> Regards
>
> Pavel
>
>> Thanks,
>>
>> Edson
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>