by Markus Winand.

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
   SELECT    '0 is not null' FROM dual
   SELECT ''''' IS NULL???'  FROM dual
    WHERE    '' IS NULL
   SELECT ''''' is not null' FROM dual 

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

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.


  1. NULL in Indexes — Every index is a partial index

  2. NOT NULL Constraints — affect index usage

  3. Emulating Partial Indexes — using function-based indexing

Previous pageNext page

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at Previously he made, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via

Buy his Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at

Hire Markus

Markus offers SQL training and consulting for developers working at companies of any size.
Learn more »

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

The Use The Index, Luke! mug

Stickers, coasters, books and coffee mugs. All you need for learning.

Shop now

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license