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

Home -> Community -> Usenet -> c.d.o.misc -> Re: "= null" & "is null"

Re: "= null" & "is null"

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 27 Aug 1999 10:52:47 GMT
Message-ID: <37ca6cc0.8763210@newshost.us.oracle.com>


A copy of this was sent to Norris <johnnie_at_cooper.com.hk> (if that email address didn't require changing) On 27 Aug 1999 02:22:19 GMT, you wrote:

>Thanks for the review.
>Now I know in oracle, if I use "= null", it always returns false.

No it doesn't. that statement is wrong. "x = NULL" NEVER returns FALSE, it never returns TRUE. It returns the third value "unknown".

tkyte_at_8i> declare
  2 x number default null;   3 begin

  4          if ( x = null ) then
  5                  dbms_output.put_line( 'x=null' );
  6          elsif ( x != null ) then
  7                  dbms_output.put_line( 'x!=null' );
  8          else
  9                  dbms_output.put_line( 'it is unknown');
 10          end if;

 11 end;
 12 /
it is unknown

PL/SQL procedure successfully completed.

>But the problem is if a column is allow-null by default,
>then when query on the column, I always need to add "is null"
>in the where clause since all other operators such as "< > decode" does not work normally with non-null values.
>

thats right -- if you are searching for rows with a column is NULL, you need to use "C is NULL", not "C = NULL"

>Ed Prochak <prochak_at_my-deja.com> wrote:
>> In article <7q3936$37f$2_at_imsp009a.netvigator.com>,
>> Norris <johnnie_at_cooper.com.hk> wrote:
>>> What is the difference between = null and is null?
>>>
>>> --
>>> --
>>> http://www.washington.edu/pine/faq/
>>>
>
>> Time for you to review SQL's three valued logic. To relate this
>> to your question, consider these examples:
>
>> where X is NULL
>> This condition is always either TRUE or FALSE.
>
>> where X = NULL
>> This condition is always FALSE.
>
>> Consider that second example for a moment. NULL means no value
>> set, ie the value is UNKNOWN. SOoo..
>
>> ...if X has a value then X=NULL is obviously FALSE.
>
>> ...if X is NULL then its value is UNKNOWN and X=NULL cannot be
>> determined. So it gets treated as if it is FALSE.
>
>
>> --
>> Ed Prochak
>> Magic Interface, Ltd.
>> ORACLE services
>> 440-498-3700 <<<NOTE new number
>
>
>> Sent via Deja.com http://www.deja.com/
>> Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Aug 27 1999 - 05:52:47 CDT

Original text of this message

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