This article presents undocumented Oracle function SYS_OP_MAP_NONNULL that is another way to compare NULL values in Oracle.

It’s worth to read following articles to get understanding how Oracle treats NULL value in Oracle database:

• NULL in SQL and PL/SQL
• Create table as select and NULL columns in views

Test data

CREATE TABLE test_tbl
(
  rec_num NUMBER,
  col1    NUMBER,
  col2    NUMBER
);

INSERT INTO test_tbl VALUES(1, NULL, NULL);
INSERT INTO test_tbl VALUES(2, 1,    NULL);
INSERT INTO test_tbl VALUES(3, NULL, 2);
INSERT INTO test_tbl VALUES(4, 3,    3);

COMMIT;

SELECT *
FROM TEST_TBL;

   REC_NUM       COL1       COL2
---------- ---------- ----------
         1                       
         2          1            
         3                     2 
         4          3          3

Usually to find/compare NULL values Oracle functions NVL, DECODE are used or IS NULL statement.

SELECT *
FROM TEST_TBL
WHERE nvl(col1, 100) = nvl(col2, 100);

   REC_NUM       COL1       COL2
---------- ---------- ----------
         1                       
         4          3          3

SELECT *
FROM TEST_TBL
WHERE decode(col1, NULL, 100, col1) = decode(col2, NULL, 100, col2);

   REC_NUM       COL1       COL2
---------- ---------- ----------
         1                       
         4          3          3 
SELECT *
FROM TEST_TBL
WHERE col1 = col2
  OR (col1 IS NULL AND col2 IS NULL);

   REC_NUM       COL1       COL2
---------- ---------- ----------
         1                       
         4          3          3

New function SYS_OP_MAP_NONNULL as default converts NULL value into RAW value FF

SELECT 
  SYS_OP_MAP_NONNULL(NULL), 
  SYS_OP_MAP_NONNULL('text') 
FROM dual;

SYS_OP_MAP_NONNULL(NULL) SYS_OP_MAP_NONNULL('TEXT')
------------------------ --------------------------
FF                       7465787400

CREATE TABLE TEST_TBL1
AS
SELECT 
  SYS_OP_MAP_NONNULL(NULL) ok, 
  SYS_OP_MAP_NONNULL('text') ok1 
FROM dual;
DESC test_tbl1
Name Null Type        
---- ---- ----------- 
OK        RAW(1 BYTE) 
OK1       RAW(5 BYTE)

So you can use it instead of  NVL, DECODE, IS NULL

SELECT *
FROM TEST_TBL
WHERE SYS_OP_MAP_NONNULL(col1) = SYS_OP_MAP_NONNULL(col2);

   REC_NUM       COL1       COL2
---------- ---------- ----------
         1                       
         4          3          3

Be careful when comparing different types using this function as this function maps different types to the same range of values.

Always convert to the same type before comparing different types

SELECT SYS_OP_MAP_NONNULL(a) a, SYS_OP_MAP_NONNULL(b) b
FROM ( SELECT '>\f' a, -9 b FROM DUAL )
WHERE SYS_OP_MAP_NONNULL(a) = SYS_OP_MAP_NONNULL(b);

A        B
-------- -------
3E5C6600 3E5C6600

SELECT SYS_OP_MAP_NONNULL(a) a, SYS_OP_MAP_NONNULL(b) b
FROM ( SELECT '>\f' a, TO_CHAR(-9) b FROM DUAL )
WHERE SYS_OP_MAP_NONNULL(a) = SYS_OP_MAP_NONNULL(b);

no rows selected

Have a fun 🙂

Tomasz