Nulls in oracle (ie, empty outer join records) cannot be compared

From: Mike Kienenberger (mkienen..mail.com)
Date: Sat Aug 19 2006 - 21:04:54 EDT

  • Next message: Andrus Adamchik: "Re: Nulls in oracle (ie, empty outer join records) cannot be compared"

    Apparently, life gets worse. Oracle makes it very difficult to
    compare outer join record values.
    Nulls do not equal anything, nor do they "not equal" anything.

    http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html

    So if I want to compare any column from a left outer join, I have to
    explicitly handle the nulls somehow.

    For example, use nvl to provide an alterate default value whenever a
    null is found.

    ( (nvl(RTRIM(t4.IS_ACTIVE), 'N') <> 'Y')

    Should something like this be automatically done for any outer join
    implementation?

    We can't use nvl genericly, but we can add another clause:

    (x is null) or (x <> 'Y') for an inequality.
    (x is not null) and (x = 'Y') for an equality

    Apparently the behavior can vary from database to database and even
    between join and search clauses for some databases. No end of fun.

    This archive was generated by hypermail 2.0.0 : Sat Aug 19 2006 - 21:05:18 EDT