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