Why should I avoid NULLs in my database?
2748 Since 25th November, 2003
Select and Copy the Code
Joe Celko said it best: "NULLs confuse people..." (SQL For Smarties, ISBN 1558605762). McGoveran and Date add:
"NULLs...are far more trouble than they are worth and should be avoided; they display very strange and inconsistent
behavior and can be a rich source of error and confusion." (Guide to Sybase and SQL Server, ISBN 020155710X).
My sentiments exactly. Of course, I don't expect to convince you by flashing a few quotes from very reputable
authors in front of you. Let's talk for a minute about what exactly NULLs do that cause this type of reaction. The first
problem is that the definition of NULL is "unknown." So, one problem is determining whether one value is (not)
equal to another value, when one or both values are NULL. This trickles down to many problems for a database
engine and any associated applications. The following list details some of those problems:
@li they are interpreted differently depending on compatibility level and ANSI settings;
For example, let's consider two values, x and y, that are both NULL. Since the definition of NULL is
unknown, then you can't say x = y. However, with the ANSI setting ANSI_NULLs, this can be
different. When this setting is FALSE, x = y ... however, when TRUE, x <> y. Confusing, no?
@li the storage engine has to do extra processing for each row to determine if the NULLable column is in fact
NULL -- this extra bit field affects storage and indexing, and obviously has performance implications for
@li they produce weird results when using calculations, comparisons, sorting and grouping;
@li they create problems with aggregates and joins, such as different answers for COUNT(*) vs.
@li they produce unpredictable results in statistics computations, particularly WITH ROLLUP and WITH CUBE;
@li applications must add extra logic to handle inserting and retrieving results, which may or may not include
@li they cause unpredictable results with NOT EXISTS and NOT IN subqueries (working backwards, SQL
determines that NULL columns belong or do not belong to the result set, usually for the wrong reasons);
@li no language that supports embedded SQL has native support for NULL SQL values.