Re: IN clause

surabhi.ahuja 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?

Expressions comparing NULL usually result in NULL, and not in true or
false. That's why there are special operators on NULL, like IS and
COALESCE().

The "problem" is that the WHERE clause interprets a NULL value similar
to false (as per the SQL spec). There's some interesting literature
about this, for example by C.J.Date.

As an example,
NULL = NULL and NULL IS NULL;
have two different results (NULL and true respectively). You'll also
find that concatenation
'a' || NULL
results in NULL.

The same goes for IN (...).

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //