Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: "= null" & "is null"
Norris wrote:
>
> When using other than "is null" or "is not null", Oracle does not return
> error but it seems to me that it always returns false like "1 = 2".
>
> Thomas Kyte <tkyte_at_us.oracle.com> wrote:
> : A copy of this was sent to Norris <johnnie_at_cooper.com.hk>
> : (if that email address didn't require changing)
> : On 26 Aug 1999 11:40:22 GMT, you wrote:
>
> :>What is the difference between = null and is null?
> :>
> :>--
>
> : the major difference is that "is null" will alway evaluate to either TRUE or
> : FALSE whereas "= null" will NEVER evaluate to TRUE OR FALSE (it is unknown).
>
> : Consider:
>
> : tkyte_at_8.0> select * from dual where null is null;
>
> : D
> : -
> : X
>
> : tkyte_at_8.0> select * from dual where null = null;
>
> : no rows selected
>
> : tkyte_at_8.0> select * from dual where null != null;
>
> : no rows selected
>
> : It is almost always an error (cannot think of a case where it is not an error)
> : to say " = null"
>
> : --
> : 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
>
> --
> --
> http://www.washington.edu/pine/faq/
It doesn't return false, it returns null...
Thus in PL/SQL the following conditions are NOT the same
if x < y then
do something;
else
do something else;
end if;
if x >= y then
do somethine else;
else
do something
end if;
since if x or y is null, you will ALWAYS drop through to the else clause.
Cheers
C
--
"Some days you're the pigeon, and some days you're the statue." Received on Thu Aug 26 1999 - 04:26:16 CDT