sql - Is there better Oracle operator to do null-safe equality check?

Question

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?

Answer 1

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.