About nulls in the database

0 0

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.

About Post Author

Aino Andriessen

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/).

aino.andriessen@amis.nl

Happy

0 0 %

Sad

0 0 %

Excited

0 0 %

Sleepy

0 0 %

Angry

0 0 %

Surprise

0 0 %

Related posts:

Putting Analytical Functions to good use – find tables with multiple foreign keys to the same referenced table Dinsdag 18 september: Oracle Open World & JavaOne 2012 Preview mini-conferenties – 12 presentaties op 1 avond Hotsos 2013 – Presentation material “Creating Structure in Unstructured Data” Het Oracle OpenWorld Preview Evenement (5 september 2013) – 15 sprekers & sessies The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper