Re: [GENERAL] Diferences between IN and EXISTS?
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
Re: [GENERAL] Diferences between IN and EXISTS?
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
Re: [GENERAL] Diferences between IN and EXISTS?
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
Re: [GENERAL] Diferences between IN and EXISTS?
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
Re: [GENERAL] Diferences between IN and EXISTS?
On Sun, Feb 3, 2013 at 3:31 AM, Edson Richter 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, [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 >> Blog sobre TI: http://fabriziomello.blogspot.com >> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello
Re: [GENERAL] Diferences between IN and EXISTS?
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 : 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
Re: [GENERAL] Diferences between IN and EXISTS?
Hello 2013/2/3 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? > 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
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
