Re: Diferences between IN and EXISTS?

[Edson Richter <edsonrichter@xxxxxxxxxxx>]

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