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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

On Sun, Feb 3, 2013 at 3:31 AM, Edson Richter <edsonrichter@hotmail.com>wrote:

Ok, I get it. Good education!

Thank you very much, saved me a big headache!

Also Bruce Momjian wrote some articles about NULLs [1]http://momjian.us/main/blogs/pgblog/2013.html#January_23_2013 and one of them is
about "NOT IN" [2]http://momjian.us/main/blogs/pgblog/2013.html#January_7_2013

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

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

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.

zeljko

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

zeljko, 04.02.2013 10:35:

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thomas Kellerer wrote:

zeljko, 04.02.2013 10:35:

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.

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

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

zeljko

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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