About nulls in the database

The july/august edition of Oracle Magazine has a very interesting article by Lex de Haan and Jonathan Gennick about Nulls (‘nothing to worry about’) in the database. Although I first skipped the article, it turned out to be very refreshing and informative.
Nulls are a ISO SQL standard and Oracle treats them according to that standard. Actually one cannot talk about null values because nulls are anything but values. Although we often consider Nulls to be equal to a zero, a Null is actually an unknown value and when you add some value the result is still unknown. The result of any scalar expression involving a null will itself be a null. This leads sometimes to confusing and maybe unwanted (unexpected?) results, which is illustrated with the following example that tries to summarize the salaries and commissions in the emp table:

SQL> SELECT SUM(SAL+COMM), SUM(SAL)+SUM(COMM) FROM EMP;
SUM(SAL+COMM) SUM(SAL)+SUM(COMM)
------------- ------------------
         7800              31225

The article also discusses the behaviour of Nulls in check constraints, boolean expressions, joins and in other cases, all nicely illustrated with clear examples.
So be aware of Nulls and if you have to treat Nulls as zero use something like nvl, nvl2, coalesce, nullif, case, is (not) null, exists. The authors actually suggest using coalesce instead of nvl because it can handle more values and ii’s part of the SQL standard.

One thought on “About nulls in the database

Comments are closed.

Next Post

ADF Faces - The Next Really Big thing from Oracle Development (ODTUG Wrap Up - Part II)

Facebook0TwitterLinkedinOne of the important threads through the entire ODTUG 2005 Conference last week was the future of JDeveloper and Oracle’s position in the Java/J2EE Arena in general. It turns out that our assumptions from last Tuesday about the licensing policy for Oracle JDeveloper were justified: see Mike O’Neill (Oracle Product […]