On 2006-02-01, rlee0001 <robeddielee@hotmail.com> wrote:
> Stephan,
>
> How do IN and NOT IN treat NULLs? Don't these functions search an array
> for a specified value returning true or false? I guess the intuitive
> thing for IN and NOT IN to do would be to return NULL if NULL appears
> anywhere in the array since those elements values are "unknown".
foo IN (x1,x2,x3) is exactly equivalent to
(foo = x1) OR (foo = x2) OR (foo = x3)
foo NOT IN (x1,x2,x3) is likewise equivalent to
(foo <> x1) AND (foo <> x2) AND (foo <> x3)
In the first case, if one of the x? is null, then the result of the
expression is true if any of the clauses is true, or null otherwise;
TRUE OR NULL is true, while FALSE OR NULL is null.
In the second case, the result is likewise determined by the logic of
three-valued AND. Since TRUE AND NULL is null, and FALSE AND NULL is
false, that means that the expression can never return true if any of
the x? is null.
> Oh yeah, my favorite is this: NULL::VARCHAR? Nope, you can't do it.
=> select null::varchar;
varchar
---------
(1 row)
works for me. (Note: that's not an empty string; use \pset null in
psql to see the difference.)
> Not without creating your own CAST.
Casting from what? NULL isn't a type...
> Seems to me that an obvious value would be 'NULL'. Or maybe ''
> (empty string).
If NULL ever got converted to 'NULL' or '', how would you distinguish it
from the literal 'NULL' or ''?
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
