3

According to this question, the way to perform an equality check in Oracle, and I want null to be considered equal null is something like

SELECT  COUNT(1)
  FROM    TableA
WHERE 
  wrap_up_cd = val
  AND ((brn_brand_id = filter) OR (brn_brand_id IS NULL AND filter IS NULL))

This can really make my code dirty, especially if I have a lot of where like this and the where is applied to several column. Is there a better alternative for this?

Community
  • 1
  • 1
Louis Rhys
  • 34,517
  • 56
  • 153
  • 221
  • Also related http://stackoverflow.com/questions/636886/sql-not-equals-null – lc. Nov 16 '12 at 06:58
  • 1
    Too bad Oracle doesn't support the standard `IS NOT DISTINCT FROM` operator. –  Nov 16 '12 at 09:55

3 Answers3

5

Well, I'm not sure if this is better, but it might be slightly more concise to use LNNVL, a function (that you can only use in a WHERE clause) which returns TRUE if a given expression is FALSE or UNKNOWN (NULL). For example...

WITH T AS
(
    SELECT    1 AS X,    1 AS Y FROM DUAL UNION ALL
    SELECT    1 AS X,    2 AS Y FROM DUAL UNION ALL
    SELECT    1 AS X, NULL AS Y FROM DUAL UNION ALL
    SELECT NULL AS X,    1 AS Y FROM DUAL
)
SELECT
    *
FROM
    T
WHERE
    LNNVL(X <> Y);

...will return all but the row where X = 1 and Y = 2.

Community
  • 1
  • 1
Brian Camire
  • 4,685
  • 2
  • 26
  • 23
3

As an alternative you can use NVL function and designated literal which will be returned if a value is null:

-- both are not nulls
SQL> with t1(col1, col2) as(
  2    select 123, 123 from dual
  3  )
  4  select 1 res
  5    from t1
  6  where nvl(col1, -1) = nvl(col2, -1)
  7  ;

       RES
----------
         1

-- one of the values is null
SQL> with t1(col1, col2) as(
  2    select null, 123 from dual
  3  )
  4  select 1 res
  5    from t1
  6  where nvl(col1, -1) = nvl(col2, -1)
  7  ;

  no rows selected

 -- both values are nulls
 SQL> with t1(col1, col2) as(
  2    select null, null from dual
  3  )
  4  select 1 res
  5    from t1
  6  where nvl(col1, -1) = nvl(col2, -1)
  7  ;

       RES
----------
         1

As @Codo has noted in the comment, of course, above approach requires choosing a literal comparing columns will never have. If comparing columns are of number datatype(for example) and are able to accept any value, then choosing -1 of course won't be an option. To eliminate that restriction we can use decode function(for numeric or character datatypes) for that:

 with t1(col1, col2) as(
  2    select null, null from dual
  3  )
  4  select 1 res
  5    from t1
  6  where decode(col1, col2, 'same', 'different') = 'same'
  7  ;

       RES
----------
         1
Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
  • 4
    This pattern only works if you can find a value that is not used in the source data (-1 in this example). If -1 is part of the source data, the comparison can return the wrong result. – Codo Nov 16 '12 at 07:35
3

With the LNNVL function, you still have a problem when col1 and col2 (x and y in the answer) are both null. With nvl it works but it is inefficient (not understood by the optimizer) and you have to find a value that cannot appear in the data (and the optimizer should know it cannot).
For strings you can choose a value that have more characters than the maximum of the columns but it is dirty.

The true efficient way to do it is to use the (undocumented) function SYS_OP_MAP_NONNULL().

like this:

where SYS_OP_MAP_NONNULL(col1) <> SYS_OP_MAP_NONNULL(col2)

SYS_OP_MAP_NONNULL(a) is equivalent to nvl(a,'some internal value that cannot appear in the data but that is not null')

Olivier Comte
  • 61
  • 3