On Tuesday 15 Jul 2003 7:38 pm, Vincent Hikida wrote: > OK. This has been bothering me. I finally recreated what I remember as an > anomoly in Oracle involving nulls. It also works the same way in > PostgreSQL. I guess in mind I extrapolated that there was a problem with > "WHERE 1 IN (1,2,NULL)" which there isn't. > > The following is my query in 9i: > > SQL> select 1 from dual where 2 not in (1,null); > no rows selected > > The following is in PostgreSQL: > > select 1 where 2 not in (1,null) > ?column? > ------------ > (0 rows)
> Now that I think about it a little, SQL does make sense. We are sure that > "1 is in (1,2,NULL)". However we cannot be sure that in 3 is not in (1,2, > NULL) because the NULL maybe 3 (if you interpret null as being unknown). > However, the SQL does not work if you have other interpretations of NULL > (e.g. Null means not applicable, Null means divide by 0). You might like to look over: http://techdocs.postgresql.org/guides/BriefGuideToNulls It's my typing, but I got a lot of input from the list, so it's good stuff. Sorry I didn't post this link earlier but I'm afraid I didn't look into your thread until tonight. -- Richard Huxton ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
