Re: Incorrect results with NOT IN

2008/8/15 Nick <nboutelier(at)hotmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>