Re: NULL v. null string

In article <3B4F0CB3.88967719_at_tm1.at>, Stuhlpfarrer says...
>
>the empty string '' is the same as NULL

in oracle, yes.

>but the = comparison does not work with NULL

correct.

>it will always return no if NULL

false, it returns UNKNOWN

ops$tkyte_at_ORA8I.WORLD> select * from dual where null=null; no rows selected

ops$tkyte_at_ORA8I.WORLD> select * from dual where NOT(null=null); no rows selected

if NULL=NULL returned false, then NOT(null=null) would be true but its not, it is itself "null" or unknown.

>is compared with any other value or NULL
>therefor you have to use "is" if you have to compare a null
>
>true:
>'' is null
>'' is ''
>null is null
>
>false:
>'' = null
>'' = ''
>null = null
>
>Chuck Hamilton wrote:
>
>> If NULL is not the same as a null string, then why does this block of
>> code display 'YES'.
>>
>> begin
>> if '' is null then
>> dbms_output.put_line('yes');
>> else
>> dbms_output.put_Line('no');
>> end if;
>> end;
>>
>> Same this with this. If null string is a known value and NULL is an
>> unknown value, why does this display no?
>>
>> begin
>> if '' = '' then
>> dbms_output.put_line('yes');
>> else
>> dbms_output.put_Line('no');
>> end if;
>> end;
>> --
>> Chuck Hamilton
>> chuck_hamilton_at_yahoo.com
>>
>> "Do not be deceived, God is not mocked;
>> for whatever a man sows, this he will also
>> reap." (Gal 6:7 NASB)
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp