Thread: Diferences between IN and EXISTS?
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
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
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@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 >
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] and one of them is about "NOT IN" [2]
Best Regards,
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
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
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.
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
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
>
> zeljko
>
>