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”
Ah, NULLs. One of my favourite topics.
Great article. Thanks!
Comments are closed.