Diferences between IN and EXISTS?

[Edson Richter]

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?


Thanks,

Edson

[Pavel Stehule]

Hello

Post by Edson Richter
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.
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

Post by Edson Richter
Thanks,
Edson
--
http://www.postgresql.org/mailpref/pgsql-general

[Edson Richter]

Ok, I get it. Good education!

Thank you very much, saved me a big headache!

Edson

Post by Pavel Stehule
Hello

Post by Edson Richter
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.
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

Post by Edson Richter
Thanks,
Edson
--
http://www.postgresql.org/mailpref/pgsql-general

[Fabrízio de Royes Mello]

Post by Edson Richter
Ok, I get it. Good education!
Thank you very much, saved me a big headache!

Also Bruce Momjian wrote some articles about NULLs [1] and one of them is
about "NOT IN" [2]

Best Regards,

[1] http://momjian.us/main/blogs/pgblog/2013.html#January_23_2013
[2] http://momjian.us/main/blogs/pgblog/2013.html#January_7_2013

[zeljko]

Post by Edson Richter
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.
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.

zeljko

[Thomas Kellerer]

Post by zeljko

Post by Edson Richter
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.
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.

If blabla returns NULL values, then you will have problems eventually.

[zeljko]

Post by Thomas Kellerer

Post by zeljko

Post by Edson Richter
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.
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.

If blabla returns NULL values, then you will have problems eventually.

but it doesn't, then blabla should say WHERE NOT some ISNULL.

zeljko

[Edson Richter]

Post by zeljko

Post by Edson Richter
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.
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

Post by zeljko
zeljko