Thread: Incorrect results with NOT IN

I have a weird scenario on a table when I run this query...

table1 has 1500 rows
table2 has 1200 rows
table2.id is a foreign key of table1.id

SELECT COUNT(*) FROM table1
WHERE id NOT IN (
  SELECT id FROM table2
);

however, using NOT EXISTS works

SELECT COUNT(*) FROM table1
WHERE NOT EXISTS (
  SELECT id FROM table2 WHERE table1.id = table2.id
);

Can you not use NOT IN over a specific number of values??

2008/8/15 Nick <nboutelier@hotmail.com>:
> I have a weird scenario on a table when I run this query...
>
> table1 has 1500 rows
> table2 has 1200 rows
> table2.id is a foreign key of table1.id
>
> SELECT COUNT(*) FROM table1
> WHERE id NOT IN (
>  SELECT id FROM table2
> );
>
> however, using NOT EXISTS works
>
> SELECT COUNT(*) FROM table1
> WHERE NOT EXISTS (
>  SELECT id FROM table2 WHERE table1.id = table2.id
> );
>
> Can you not use NOT IN over a specific number of values??

yes, NULLs
http://www.depesz.com/index.php/2008/08/13/nulls-vs-not-in/

regards
Pavel Stehule

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

Wow, very good to know. That was driving me crazy.