On Sun, Jul 13, 2003 at 11:14:15PM -0700, Vincent Hikida wrote: > Oops forgot to cc the list. > > I just happened to notice another difference recently between Oracle and > > Postgresql for the clause > > > > WHERE 1 IN (1,2,NULL) > > > > In Oracle, this clause is false because 1 compared to a NULL is false. > > However, Postgresql will return a true. I actually don't know what the > ANSI > > standard is for this case. Perhaps someone else on this list will know. > > Perhaps the standard body never even thought of this. Yes, I was actually > > stung by this particular while using it in Oracle.
I can;t comment on what the correct answer is, but I beleive the reason it works in Postgres is because the expression is expanded to: WHERE (1=1) OR (1=0) OR (1=NULL) which becomes: WHERE TRUE OR FALSE OR NULL which is TRUE. (standard tri-value logic) -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organized violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
pgp00000.pgp
Description: PGP signature