Jeff Moden (4/12/2014)
I know this is an old post and that the subject is controversial, but a blank means you know it is blank. NULL actually isn't a value. It's a condition. It means that you don't know what the value is.I also think it's a problem that Excel has no default method of telling you that a cell is blank or that it is empty, which shouldn't be confused with NULL because empty cells are known to be empty whereas a NULL would mean that you don't know if the cell has a value (even if the value is "blank" as in spaces) or is truly empty. 😛
Ok I know this isn't really an active topic but I prefer to think that NULL means the cell or column has no value, rather than not knowing what the value is. For instance, say you left join one table to another, and in the other table you list a column. When there is no match in that other table on the column you are left joining, the arbitratry column in the other table you are trying to list is NULL, and its not that you don't KNOW if there is a value, its rather that you are SURE there is NO value.
If on the other hand, we describe NULL has "we just don't know", this could casually imply that arbitrarily replacing a NULL'ed column with random information constitutes a valid operation and that all programs would be required to provide correct answers even when presented with random input. Ie., "we just don't know" very easily morphs into "it could be anything", whereas I like the certainty of "there is no value stored in this column of this row."
I absolutely acknowlege in advance the citations out there for the "we just don't know" business, but I'm just posting an opposing viewpoint. I also acknowledge that one of the reasons that a column can be null is that we don't know what it is, but then this precludes app developers from using a much more reasonable "unknown" selection which in this case now codifies (and stores) an indicator that we really for real don't know what the value is.
I really like Hugo Kornelis' take on the topic:
And saving the best (read: most controversial) for the last, NULL is also definitely not meant to signify “unknown”. Again, a NULL in a table might result from the value being unknown at data entry time (e.g., when we forget to ask a customer for his or her birthday), but there might be other reasons as well. Unfortunately, many text books insist on explaining the behaviour of NULL in expressions by describing NULL as unknown, rather than missing or absent, causing this misconception about NULL to be the most widespread and the hardest to combat.