Re: 1 very small question for a fundamental concept.

xav_x_at_noos.fr (Xavier Autret) wrote i

> select TOTO from BABA where TOTO <> 1;
> select TOTO from BABA where TOTO <> 1 or TOTO is null;
>
> why first statement does not return null values?
> Am I obliged to specify the statement "or TOTO is null" ?
> "1 <> null" condition is true or not?
>
> does that behaviour can be circumvented?

Xavier, my 2'c. :-)

NULL is *not* a value. It is a *state*.

This is a fundemental principle. And the cause for confusing when it comes to dealing with NULLs

NULL is treated as a value (which it is NOT!) and value operators (e.g. equal, not equal, etc.) are used on it. This IMO is a case of ignorance instead of a problem with NULL's behaviour.

NULL requires the use of state operators like IS and IS NOT.

This is the expected and the correct behaviour. You will find the exact same behaviour when dealing with OO languages and NULL objects (though you can use the = and != in many OO languages - but then this still indicate state comparison and and not value comparison in such a case).

--
Billy