On Fri, 17 Mar 2006, Patrick Narkinsky wrote: > This may be expected behavior, but it certainly doesn't seem right to me, > and it works as expected in sqlite. > > The database is as follows: > > BEGIN TRANSACTION; > create table a ( > id integer, > text varchar(20) > ); > INSERT INTO a VALUES(0,'test'); > INSERT INTO a VALUES(1,'test2'); > create table b ( > id integer, > a_id integer); > INSERT INTO b VALUES(0,NULL); > INSERT INTO b VALUES(1,NULL); > INSERT INTO b VALUES(2,NULL); > COMMIT; > > The following query returns everything in a in sqlite, but returns nothing > in postgresql: > > select * from a where a.id not in (select a_id from b);
AFAICS, our behavior follows SQL. a NOT IN b is NOT(a IN b) IN is defined in terms of = ANY. a =ANY (b) is basically (by my reading of 8.8 anyway): True if a = bi for some bi in b False if b is empty or a <> bi for all bi in b Unknown otherwise Since a <> NULL returns unknown, the second one won't come up, so the whole expression won't ever be true after the negation. It might be false or it might be unknown. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
