Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: "= null" & "is null"
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;
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