On 2006-02-01, rlee0001 <robeddielee@xxxxxxxxxxx> 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
