If I had to summarise my position on allowing NULL values, then I think of them similar to how I think of a loaded gun - not directly harmful itself, but a there is a reasonable chance of it going wrong and resulting in serious damages. If you need to use them, and you have a clear reason why, then use them. But otherwise avoid them as much as possible.
Allowing NULL values in a column can be bad for all kind of direct and indirect reasons.
- Oracle does not store NULL values in normal B-Tree indexes, which limits when the indexes can be used on columns that allow NULL values
- Such an index cannot be used to satisfy an "IS NULL" query constraint
- The index also cannot be used to satisfy a "column != value" query constraint
- Foreign keys that allow NULL are "optional", but again have index and optimizer side effects.
- Outer joins may become necessary, which again limit the choices available to the Optimizer.
- Many programming languages have no concept of NULL, being a value outside of the normal value range by definition.
- Either NULL values require special programming, or require some kind of shadow boolean variable to indicate if it is really NULL (no real value in the main variable).
- Many programmers struggle with the difference between NULL and empty or blank, which can all mean different things within a programming language e.g. a zero length string, or the number zero.
cannot be using in normal equality and range tests. As it is outside
the normal value range it cannot be greater than or less than a real
value. And it cannot be equal to any other value in a column, including
another NULL. You must use the special constraint "
IS NULL" to test for a NULL value, and never "
- Calculations involving a NULL value evaluate to NULL i.e. unknown.