From: Brian Selzer <>
Date: Sun, 30 Dec 2007 14:23:20 -0500
Message-ID: <JeSdj.2231$>

"stevedtrm" <> wrote in message
>> > If everyone is clear NULLS shouldn't be used, why the debate as to what
>> > to do about them ?
>> Because SQL allows NULL and even promotes the idea that NULL solves some
>> problem instead of introducing many.
> So everyone is agreed that NULLs shouldn't appear anywhere, and its
> just a matter of time before NULLS become a legacy problem and a
> relational language supercedes SQL?

No, Steve, not everyone is agreed.

> Are the two solutions I suggested before the widely accepted as
> resolutions to the two problems NULLs were introduced to eradicate?
>>Missing information? Then surely there should just be no tuple?

A row may contain a lot more information that would otherwise be useful. Would you tell a potential customer that you won't serve them because you don't know everything about them? I think not! Haven't you been asked for your e-mail address at a retailer? What would you think if the cashier told you to get lost for refusing to supply it?

>>To indicate that there can be no value? Why not a seperate table with
>>a boolean value in the non-key column?

In my opinion, null should only ever be used to indicate "There should be a value here, but it hasn't been supplied." That there should be a value or that there must be a value are structural issues, and therefore should not be subject to interpretation. "No value here" invites interpretation, because it begs the question, "Should there have even been a value?"

Structural information is lost by separating a nullable column into its own table: the fact that there should always be a value, even if it hasn't been supplied is lost. That's not to say that it isn't possible to eliminate nulls altoghether, it's just that you need several separate tables instead of one: one table for the values that must always be present, one table for each nullable column (or set of columns in those cases where whenever one value is supplied, another value must also be supplied) that indicates whether or not there should be a value (or set of values), and one table with a column (or set of columns) for those values that just happen to have been supplied.

Note also that the spectre of 3VL disappears when null indicates only "there should be a value here, but it hasn't been supplied." Since there should be a value, and only one value, a null can simply be replaced by an exclusive disjunction that encompasses all of the values in the attribute's domain. So there is no need for 3VL, 2VL suffices. (For infinite domains, a quantifier could be used that means instead of "there is...," "there is exactly one....") Received on Sun Dec 30 2007 - 20:23:20 CET

Original text of this message