Null Understanding

Defining Null

Contrary to the first sentence of the Null SQL entry on Wikipedia, null does not mean 'no value' (the page on Three Valued Logic (3VL) gets it right). If it it did, 'select * from Address where Zip = NULL' would return the same results as 'select * from Address where Zip is null'. Strictly speaking, when a value is null it means that there may be a value or there may not be a value; we do not know the value. Null is the unknown-value-placeholder.

To Null, or Not to Null

Jihad! Nulls create a nice bifurcation in the database world. Basically, if you think nulls are bad, you get lumped in with the theorists. If you think nulls are good, well, a programmer or a practitioner (it gets a little muddy--null?). The truth is, null values and logic do have their place. It may not be a comfortable place (and not somewhere I want to go), but they have come to be a necessity.

Nulls are handled differently than known values. Consequently, the presence of null values often creates complexity. If the value is missing, we sometimes want to include it in our set, and sometimes not... Isn't that a key point to 3VL?

The important point is to use nulls as they were intended; to reflect a value in an unknown state. Do not use the null for non-values or missing values--that serves to perpetuate the misunderstanding.

Personally, I am on C.J. Date's side--don't use them. Even so, there are times when a design is conducive to null logic.

Codd later proposed a Four Value Logic for the Relational Model. By that time (and in part because), nulls were being misused by the practitioners. I wonder if the 4VL had survived if we would still misuse nulls or if the whole debate would be null and void...




Tags: null, sql