Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: 1 very small question for a fundamental concept.
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