Tom.Thomson (7/23/2010)
Ameya- Ameyask (7/23/2010)
Very good question indeed. NULL does not mean NULL.That erroneous view is a fairly natural consequence of treating NULL as a value which exists in all domains instead of as a marker quite separate from the domain of values indicating the absence of any value. This error was originally committed by Codd, but he corrected it later (and by 1986 was advocating two different NULL markers, one for "Inapplicable and therefore absent" and the other for "Applicable but absent", and a 4-valued logic with values T, F, I and A); unfortunately the SQL crew didn't pick up the correction, still less the idea of two different nulls
Hi Tom,
It is in fact A Good Thing that the SQL crew didn't pick up the correction, as it was based on an error. And you fall for the same trap that Codd fell for. This was caused by Date (a firm opponent of any NULLs in the relational model), who managed to make Codd believe that one kind of NULL is not enough - whether Date didn't see the fallacy in the argument or whether he deliberately introduced it to trap Codd, I do not know. But he did manage to get Codd to admit that there have to be two kinds of NULL, and then expanded on that to add even more kinds of NULL, bringing the whole concept of NULL ad absurdum.
The error, that Codd and you (and maybe Date) overlooked, is that including two or more different NULL markers in the relational model would violate one of its basic principles: atomicity. A single column should store a single attribute; multiple attributes combined into a single column are considered bad. And since the attributes "Birthday" and "Reason birthday is missing" are distinct, they should be represented in seperate columns (assuming both are relevant for the business). Suggested further reading: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx. (Sorry for the blatant self-promotion:-D)
and the ANSI standard explicitly states that its single NULL is a special value. A value that denotes the absence of any value is a bit of a loopy concept - presumably it denotes the absence of itself, so it would fit well into Charles Dodgson's works of fiction but not at all well into his textbooks on mathematics and logic.
The ANSI standard does not describe NULL as a value that denotes the absence of any value, but as "a special value that is used to indicate the absence of any data value". (ISO/IEC 9075-1:2003, page 5 - emphasis added by me). Not loopy at all. And to prevent this confusion, many good textbooks instead describe NULL as a marker to indicate the absence of any value.