About nulls in the database

1
Share this on .. Tweet about this on Twitter0Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

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.

Share this on .. Tweet about this on Twitter0Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

About Author

Aino Andriessen is principal consultant and expertise lead 'Continuous Delivery'. His focus is on Oracle Fusion Middleware ADF and SOA development, Continuous Delivery, architecture, improving the software development proces and quality management. He is a frequent presenter at Oracle Open World, ODTUG Kaleidoscope, UKOUG Technology Conference and OUGN Vårseminar. He writes articles and publishes at the AMIS technology blog (http://technology.amis.nl/blog/).