2012-04-08NULL in the Oracle Database

SQL’s NULL causes so much confusion. The basic idea of NULL—to represent missing data—is rather simple, but there are some peculiarities. You have to use IS NULL instead of = NULL, for example. But with the Oracle Database, there are even more oddities. On the one hand, because it does not always handle NULL as required by the SQL standard. On the other hand, because it has a very “special” handling of NULL in indexes.

According to the SQL standard, NULL is not a value—it marks the absence of a value. Consequently, no value can be NULL. But the Oracle Database treats an empty string as NULL:

   SELECT     '0 IS NULL???' AS "what is NULL?" FROM dual
    WHERE      0 IS NULL
UNION ALL
   SELECT    '0 is not null' FROM dual
    WHERE     0 IS NOT NULL
UNION ALL
   SELECT ''''' IS NULL???'  FROM dual
    WHERE    '' IS NULL
UNION ALL
   SELECT ''''' is not null' FROM dual 
    WHERE    '' IS NOT NULL;

what is NULL?
--------------
0 is not null
'' IS NULL???

The concept of NULL is used in many programming languages. No matter where you look, an empty string is never NULL. Except, in the Oracle Database. It is, in fact, impossible to store an empty string in a VARCHAR2 field. If you try, it just stores NULL.

To complete the confusion, there is even a case when the Oracle Database treats NULL as empty string:

SELECT dummy
     , dummy || ''
     , dummy || NULL
  FROM dual;

D D D
- - -
X X X

The concatenation of the DUMMY column (always containing 'X') with NULL should return NULL.

This peculiarity is not only strange, but dangerous. But the Oracle Database’s NULL oddity continues with indexing.