Re: IN clause

On fös, 2006-11-24 at 10:10 -0800, Richard Broersma Jr wrote:
> > That is fine
> > but what I was actually expecting is this
> > if
> > select * from table where col_name in (null, 'a', 'b');
> >
> > to return those rows where col_name is null or if it = a or if it is = b
> >
> > But i think in does not not support null queries , am i right?
> >
>
> that is correct: if col_name was actually 'a' then you would get:
>
> 'a' in ( null, 'a', 'b', ...) works the same as:
>
> 'a' = null ~ resolves to Unknown
> or
> 'a' = 'a' ~ resovles to true
> or
> 'a' = 'b' ~ resovles to false
> or
> ...
>
> so you end up with:
> (unknown or true or false) = true
> but if you have
> (unknown or false or false) = false

yes, except I think you meant:
(unknown or false or false) = unknown

as can be demonstrated by:

test=# \pset null 'null'
Null display is "null".
test=# select (null or true);
?column?
----------
t
(1 row)

test=# select (null or false);
?column?
----------
null
(1 row)

and indeed the IN operator does behave this way:

test=# select 'a' in (null,'a');
?column?
----------
t
(1 row)

test=# select 'a' in (null,'b');
?column?
----------
null
(1 row)

test=# select 'a' in ('a','b');
?column?
----------
t
(1 row)

test=# select 'a' in ('b','c');
?column?
----------
f
(1 row)

and finally: NULL IN (NULL,'b') will return NULL
because it will translate to
(NULL = NULL) or (NULL = 'b')

test=# select null in (null,'b');
?column?
----------
null
(1 row)