Re: Warning about null and open question to Oracle

In article <3rictg$r0j_at_crl2.crl.com> tactics_at_crl.com (Dewey Blaylock) writes:
>John Jones (john_at_iluvatar.tip.duke.edu) wrote:
 <snip>
>: I finally figured out that if both columns that were being compared were
>: null then they were not equal. WHY????? It seems that null would be
>: equal to null????? To any Oracle Corp. people out there ... Will this
>: ever be fixed???
>
>You are exactly correct, a NULL does not equal NULL. Null is always
>undefined. Since it has no definition it cannot be equal to another
>undefined value. This is how NULL are treated in ORACLE and I don't
>think it is something that needs to be fixed. One way to handle this
>is to wrap nvl(your_column_her,0) in your cursor select or in the comparison
>this will ensure that NULL will be handled identically.
>
>IF nvl(a,0) = nvl(b,0) then
> do something;
>END IF;
>
>gthomas_at_us.oracle.com

The only way you can really be sure is with something like this:   IF a = b or (a is null and b is null)

Oracle's way of always returning FALSE to any comparison where a column value is null indicates that they don't work much in the *real* world. If they really wanted to help, they would come up with some function to be used with comparisons like:

   If NULLS_ARE_REAL(a=b) then do_something

Such a function should treat two null values as equal, and if comparing < or >, nulls would always be less than anything else.

How about it, Oracle???

stevec_at_zimmer.csufresno.edu (Steve Cosner) Received on Tue Jun 13 1995 - 00:00:00 CEST