This discussion is archived
1 2 3 Previous Next 43 Replies Latest reply: Dec 7, 2007 6:48 AM by 494018 RSS

Using <> or != on a NULL

582145 Newbie
Currently Being Moderated
Hi,

This is probably a very easy question..

When I try to SELECT some rows based on tableA.col1 <> tableB.col1, I get less rows than expected back. I think it's because some of the tableA.col1 are NULL and tableB.col2 have values.

Is there a way to check for equality w/out having to use IS NULL and IS NOT NULL?

Thanks,
  • 1. Re: Using <> or != on a NULL
    bencol Pro
    Currently Being Moderated
    NVL() ?
  • 2. Re: Using <> or != on a NULL
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    Is there a way to check for equality w/out having to
    use IS NULL and IS NOT NULL?
    either (the most robust)
    WHERE X!=Y 
    OR (X IS NULL AND Y IS NOT NULL) 
    OR (X IS NOT NULL AND Y IS NULL)
    or, more compact
    WHERE decode(X,Y,1,2)=2
  • 3. Re: Using <> or != on a NULL
    MichaelS Guru
    Currently Being Moderated
    Is there a way to check for equality w/out having to use IS NULL and IS NOT NULL?
    e.g.:
    select * from emp where lnnvl(comm = :comm)
  • 4. Re: Using <> or != on a NULL
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    select * from emp where lnnvl(comm = :comm)
    note that LNNVL(NULL=NULL) is true. But maybe it is wished
  • 5. Re: Using <> or != on a NULL
    60660 Journeyer
    Currently Being Moderated
    And if you need to check inside PL/SQL (where DECODE is still not an option), this option is also available (at least from 10g on):
    NVL(NULLIF(X, Y), NULLIF(Y, X)) IS NOT NULL
    C.
  • 6. Re: Using <> or != on a NULL
    Sven W. Guru
    Currently Being Moderated
    In PL/SQL you can use IF ... THEN ... ELSE instead of decode. I wouldn't understand why to use a more cryptical command instead.
  • 7. Re: Using <> or != on a NULL
    60660 Journeyer
    Currently Being Moderated
    Depending on the code, I'd probably use a simple IF THEN construct. If, however, I've to build for example an auditing trigger, I'd argue that my solution would account for less code and therefore less maintainance effort.

    So instead of writing
    IF X <> Y OR (X IS NULL AND Y IS NOT NULL) OR (X IS NOT NULL AND Y IS NULL) THEN
    I would write
    IF NVL(NULLIF(X, Y), NULLIF(Y, X)) IS NOT NULL THEN 
    If I would stick to that construct, I would assume that other developers would soon find out (and maybe) adopt that technique. To prove that this really works, here's a little demonstration:
    WITH t AS (SELECT 1 x 
                 FROM dual
                UNION 
               SELECT NULL
                 FROM dual
              ),
         t2 AS (SELECT t.x
                     , t2.x y
                  FROM t
                     , t t2
               )      
    SELECT t2.* 
         , CASE WHEN NVL(NULLIF(x, y), NULLIF(y, x)) IS NOT NULL 
                THEN '<>'
                ELSE '==' 
                END eq
      FROM t2
    ;  
    
             X          Y EQ
    ---------- ---------- --
             1          1 ==
             1            <>
                        1 <>
                          ==
    C.
  • 8. Re: Using <> or != on a NULL
    WhiteHat Expert
    Currently Being Moderated
    what on earth is wrong with just the basic:

    where nvl(x,'foo') = nvl(y,'foo')
  • 9. Re: Using <> or != on a NULL
    Sven W. Guru
    Currently Being Moderated
    This is ok, as long as you are sure that x and y are never 'foo' and are strings.
  • 10. Re: Using <> or != on a NULL
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    less code and therefore
    less maintainance effort.
    Are you serious?
    IF X <> Y OR (X IS NULL AND Y IS NOT NULL) OR (X IS NOT NULL AND Y IS NULL) THEN

    IF NVL(NULLIF(X, Y), NULLIF(Y, X)) IS NOT NULL THEN
    which one is easier to maintain?
  • 11. Re: Using <> or != on a NULL
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    at least the first one will be easier to understand because it contains only <> , OR , AND, IS NULL and IS NOT NULL. Those operators as available in Oracle since ages. Very few people know about NULLIF, and this Function encapsulation may hurt more than one developer. Just my 2 cents ;-)

    Anyway, it is still shorter, so if YOU are maintaining YOUR OWN code, than yes, it may require less maintenance
  • 12. Re: Using <> or != on a NULL
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    WITH t AS (SELECT 1 x FROM dual UNION 
               SELECT NULL FROM dual),
         t2 AS (SELECT t.x, t2.x y
                  FROM t, t t2)
    select X,Y,
    case when X=Y or nvl(X,Y) is null
         then '==' else '<>' end as eq
      FROM t2;
    or
    [pre]
    WITH t AS (SELECT 1 x FROM dual UNION
               SELECT NULL FROM dual),
         t2 AS (SELECT t.x, t2.x y
                  FROM t, t t2)
    select X,Y,decode(X,Y,'==','<>') as eq
      FROM t2;
    Furthermore , We can use "SYS_OP_MAP_NONNULL".
    But unofficial function.
    http://awads.net/wp/2006/09/19/cool-undocumented-sql-function-sys_op_map_nonnull-and-some-alternatives/
  • 13. Re: Using <> or != on a NULL
    60660 Journeyer
    Currently Being Moderated
    at least the first one will be easier to understand
    because it contains only <> , OR , AND, IS NULL and
    IS NOT NULL. Those operators as available in Oracle
    since ages. Very few people know about NULLIF, and
    this Function encapsulation may hurt more than one
    developer. Just my 2 cents ;-)
    With that argument, why ever get a new version, since developers could become confused with new commands and options, right? Those are my 0.02 EUR.

    C.
  • 14. Re: Using <> or != on a NULL
    Sven W. Guru
    Currently Being Moderated
    @CD: I really like your version, but I agree with Laurent. For most developers the the IF THEN ELSE version is better. Also consider that keyword highlighting helps to improve readability of this statement.

    You mentioned an auditing trigger. I would suggest to write a packaged function/procedure for such a trigger, that includes the comparison and then inserts in the audit table. This is not only better maintainable, but also faster (saves parsing time).
1 2 3 Previous Next