Re: Diferences between IN and EXISTS?

Hello

2013/2/3 Edson Richter <edsonrichter@hotmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general