Re: IS (NULL = 1) equal to NOT(NULL = 1)?

jtdennis_at_atl.mindspring.com (John Dennis) wrote:

>Of course not right? Oracle says it is!
 

>I have the following *simple* code:
 

>set serveroutput on
>BEGIN
>DECLARE
 
>N NUMBER;
 
>BEGIN
 
>N := NULL;
 
>IF (N = 1)
>THEN
> dbms_output.put_line('TRUE');
>ELSE
> dbms_output.put_line('FALSE');
>END IF;
 
>/* Notice the expression has been "notted" */
 

>IF NOT(N = 1)
>THEN
> dbms_output.put_line('TRUE');
>ELSE
> dbms_output.put_line('FALSE');
>END IF;
 
>END;
>END;
>/
 

>This code returns:
 

>FALSE
>FALSE
 
>Now I've been doing this for a while. I know that 1 is not equal to
>NULL. I thought this meant (1 = NULL) = FALSE which might be true.
>However if it is true then certainly NOT(1 = NULL) would then be TRUE.
>NOT of *any* expression is the opposite boolean value. But not here.
 

>Anyway, if anyone could point out my error here please do so. I
>wouldn't even mind a little "... any rookie that knows anything know
>should know that null, etc..." stuff. Hmmm, well maybe not.
 

>Virtually,
 

>John Dennis

NULL is not equal to anything and even NULL = NULL will not evaluate to TRUE. Similarily NOT(NULL = NULL) or (NULL != NULL) will not evaluate to FALSE. Similarily NULL/NULL (ie division of 2 NULL VALUES) will not evaluate to 1. In all cases the answer is NULL which is why the only valid comparision operator for expressions is "<identifier> IS NULL" or "<identifier> IS NOT NULL". NULL by definition means unknown and one unknown value is certainly not equal to another.

        The best explanation I have found is in S. Feuerstein's ORACLE PL/SQL PROGRAMMING (O'Reilly & Associates).

        Hope this helps. Received on Wed Aug 21 1996 - 00:00:00 CEST