1 2 3 Previous Next 43 Replies Latest reply: Dec 7, 2007 8:48 AM by 494018 RSS

    Using <> or != on a NULL

    582145
      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
          NVL() ?
          • 2. Re: Using <> or != on a NULL
            Laurent Schneider
            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
              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
                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
                  cd_2
                  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.
                    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
                      cd_2
                      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
                        523861
                        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.
                          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
                            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
                              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
                                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
                                  cd_2
                                  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.
                                    @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