Re: 1 very small question for a fundamental concept.

Hi!

When using null values in Oracle, please consider using NVL function, which tells you how to behave when null "value" occurs. e.g.
.....NVL(TOTO,1)<>1 ....

BR,
DJ

p.s. I am just an ordinary oracle user, not proffesionalist !

<Kenneth Koenraadt> wrote in message
news:3f7c7b9a.4493841_at_news.inet.tele.dk...
> On 2 Oct 2003 12:04:39 -0700, xav_x_at_noos.fr (Xavier Autret) wrote:
>
> >Hello Oracle gurus!
> >
> >==>As always it s about null values<==
> >
> >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?
>
> Because null is *incomparable* to anything, not even another null.
>
> It is simply not possible to decide if null <> 1 or even null = null
> or null != null.
>
> That is why we have the 2 unary operators : "is null" and "is not
> null".
>
>
> Proof :
>
> SQL> select count(*) from dual where null = null;
>
> COUNT(*)
> ----------
> 0
> SQL> select count(*) from dual where null != null;
>
> COUNT(*)
> ----------
> 0
>
> SQL> select count(*) from dual where null is null;
>
> COUNT(*)
> ----------
> 1
> SQL> select count(*) from dual where null is not null;
>
> COUNT(*)
> ----------
> 0
>
> >Am I obliged to specify the statement "or TOTO is null" ?
> If you want the rows with null in TOTO, yes.
>
> >"1 <> null" condition is true or not?
>
> NOT! as with 1 > null , 1 < null and even 1+null = null.
>
> Any comparison operation (except "is null" and "is not null") will
> return false when one of the operands is null.
>
> "Proof" :
> SQL> select count(*) from dual where 1 > null or 1 < null or 1 + null
> = null;
>
> COUNT(*)
> ----------
> 0
>
> >
> >does that behaviour can be circumvented?
>
> It is the correct and expected behavior.
> >
> >Thks!
> >
> >Xav
>
>
> - Kenneth Koenraadt
Received on Fri Oct 03 2003 - 00:52:55 CDT