NULL frequently causes confusion. Although the
basic idea of
NULL—to represent missing
data—is rather simple, there are some peculiarities. You have to
IS NULL instead of
= NULL, for example.
Moreover the Oracle database has additional
NULL oddities, on
the one hand because it does not always handle
required by the standard and on the other hand because it has a very
“special” handling of
NULL in indexes.
The SQL standard does not define
NULL as a value but
rather as a placeholder for a missing or unknown value. Consequently, no
value can be
NULL. Instead the Oracle database treats an
empty string as
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???
To add to the confusion, there is even a case when the Oracle
NULL as empty string:
SELECT dummy , dummy || '' , dummy || NULL FROM dual
D D D - - - X X X
DUMMY column (always containing
NULL should return
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, the Oracle database just stores
This peculiarity is not only strange; it is also dangerous.
Additionally the Oracle database's
NULL oddity does not stop
here—it continues with indexing.