NULL in the Oracle Database


SQL’s NULL frequently causes confusion. Although the basic idea of NULLto represent missing data—is rather simple, there are some peculiarities. You have to use 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 NULL as 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 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
Try online at SQL Fiddlewhat is NULL?
--------------
0 is not null
'' IS NULL???

To add to the confusion, there is even a case when the Oracle database treats NULL as empty string:

SELECT dummy
     , dummy || ''
     , dummy || NULL
  FROM dual
Try online at SQL FiddleD D D
- - -
X X X

Concatenating the DUMMY column (always containing 'X') with NULL should return 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, the Oracle database just stores NULL.

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.

If you like my way of explaining things, you’ll love my book.

About the Author

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/

?Recent questions at
Ask.Use-The-Index-Luke.com

0
votes
1
answer
128
views

PostgreSQL Scripts: Performance Testing and Scalability problem and question

Nov 12 at 14:53 Markus Winand ♦♦ 936
testing postgresql scalability
0
votes
1
answer
497
views

PostgreSQL Bitmap Heap Scan on index is very slow but Index Only Scan is fast

Oct 31 at 11:31 Markus Winand ♦♦ 936
index postgresql postgres sql
3
votes
2
answers
551
views

pagination with nulls

Oct 29 at 22:39 Rocky 46
pagination