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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
