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.