Re: Re : BUG #2251: NOT IN clause is not working correctly

On Sun, Feb 26, 2006 at 07:38:32PM +0530, Dhanaraj wrote:
> I am running a query:
>
> select * from A t1 where t1.id not in (select t2.A_id from B t2);
>
> It returns 0 rows.
>
> Now I run
> (select t1.id from A t1) except (select t2.A_id from B t2);
>
> And now Postgres correctly returns records from A that are not referenced by
> B.

Table B probably has some NULL values for A_id, so the first query's
NOT IN expression returns NULL instead of true because it's
indeterminate whether t1.id is in the set (NULL means unknown).
Here's an example:

CREATE TABLE a (id integer PRIMARY KEY);
CREATE TABLE b (a_id integer REFERENCES a);

INSERT INTO a VALUES (1);
INSERT INTO a VALUES (2);

INSERT INTO b VALUES (1);
INSERT INTO b VALUES (NULL);

SELECT * FROM a WHERE id NOT IN (SELECT a_id FROM B);
id
----
(0 rows)

SELECT * FROM a WHERE id NOT IN (SELECT a_id FROM b WHERE a_id IS NOT NULL);
id
----
2
(1 row)

According to past discussion this behavior is per the SQL specification.
Search the list archives for more information.

--
Michael Fuhr