Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives

In Oracle, NULL does not equal NULL:

  EDDIE@XE> SELECT 'hi there'
    2    FROM DUAL
    3   WHERE NULL = NULL;

  no rows selected

But sometimes, if not most of the times, you do want the condition NULL = NULL to return true. For example:

  EDDIE@XE> CREATE TABLE t (
    2  col1  VARCHAR2(10),
    3  col2  VARCHAR2(10));

  Table created.

  EDDIE@XE> INSERT INTO t VALUES ('monkey', NULL);

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES ('monkey', 'chicken');

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES ('monkey', 'monkey');

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES (NULL, 'monkey');

  1 row created.

  EDDIE@XE> INSERT INTO t VALUES (NULL, NULL);

  1 row created.

  EDDIE@XE> COMMIT;

  Commit complete.

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t;

  COL1       COL2
  ---------- ----------
  monkey     I am null
  monkey     chicken
  monkey     monkey
  I am null  monkey
  I am null  I am null

Now, I want to select all the records from table t where col1 equals col2:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE col1 = col2;

  COL1       COL2
  ---------- ----------
  monkey     monkey

As expected, the row that has NULL in both col1 and col2 was not returned. You want this record returned. Here is how you can do it:

Using the undocumented function SYS_OP_MAP_NONNULL:

This function has been around for a while, but is still undocumented as of the latest Oracle database release (10gR2). Being undocumented means that it should not be used in production code, but it does not hurt to know that it exists and explore how it works. This function makes it possible to have NULL = NULL:

  EDDIE@XE> SELECT sys_op_map_nonnull (NULL)
    2    FROM DUAL;

  SY
  --
  FF

  EDDIE@XE> SELECT 'hi there'
    2    FROM DUAL
    3   WHERE sys_op_map_nonnull (NULL) = sys_op_map_nonnull (NULL);

  'HITHERE
  --------
  hi there

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE sys_op_map_nonnull(col1) = sys_op_map_nonnull(col2);

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Using simple logical operators:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE (col1 = col2 OR (col1 IS NULL AND col2 IS NULL));

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Using DECODE:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE DECODE (col1, col2, 'match', 'no match') = 'match';

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Using NVL:

  EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
    2         NVL (col2, 'I am null') col2
    3    FROM t
    4   WHERE NVL (col1, 'impossible value') = NVL (col2, 'impossible value');

  COL1       COL2
  ---------- ----------
  monkey     monkey
  I am null  I am null

Of course, in this case, you have to be absolutely, positively 100 % sure that col1 and col2 will never have a value of ‘impossible value’ in them. Moreover, instead of NVL, you can also use similar functions.

Sources and Resources:

• AskTom thread
• PSOUG Morgan’s Library

8 Comments | Filed in Oracle, Tips | Tags: function, null, sql, undocumented

---

SQL> SELECT 1 my_number
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_number
  5    FROM DUAL
  6  /
SELECT 1 my_number
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression


SQL> SELECT SYSDATE my_date
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_date
  5    FROM DUAL
  6  /
SELECT SYSDATE my_date
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

SQL> SELECT 'C' my_char
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_char
  5    FROM DUAL
  6  /

M
-
C

SQL> SELECT 1 my_number
  2    FROM DUAL
  3  UNION
  4  SELECT TO_NUMBER (NULL) my_number
  5    FROM DUAL
  6  /

 MY_NUMBER
----------
         1


SQL> SELECT SYSDATE my_date
  2    FROM DUAL
  3  UNION
  4  SELECT TO_DATE (NULL) my_date
  5    FROM DUAL
  6  /

MY_DATE
---------
15-JUN-06

SQL> SELECT 1 my_number
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_number
  5    FROM DUAL
  6  /

 MY_NUMBER
----------
         1


SQL> SELECT SYSDATE my_date
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_date
  5    FROM DUAL
  6  /

MY_DATE
---------
15-JUN-06