Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: 1 very small question for a fundamental concept.

Re: 1 very small question for a fundamental concept.

From: <Kenneth>
Date: Thu, 02 Oct 2003 19:41:49 GMT
Message-ID: <3f7c7b9a.4493841@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

Received on Thu Oct 02 2003 - 14:41:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US