Re: So what's null then if it's not nothing?

<michael_at_preece.net> wrote in message news:1132207394.336133.241390_at_g44g2000cwa.googlegroups.com...
> I've been accustomed to thinking of things either having a value or
> not. If something has no value then, to me, its value is null. Its
> value is an empty string (whatever "it" is). Different to having a
> value of zero. Different to anything with a value. Now, as I read up on
> SQL, I find that null is supposed to mean "unknown". I can't easily
> accept that. Does SQL's definition of null (unknown) include the null
> I'm familiar with (no value)? That doesn't make sense. If we know
> something has no value then its not an unknown value is it? I can't
> imagine having to write code where the "if a=b then result=true else
> result=false" construct won't work - according to what I'm reading, if
> either a or b is null then I should be setting result to unknown
> instead. Just can't get my head 'round that. Shouldn't things be a lot
> simpler? If something has an unknown value then at least we know
> whether it's null (as in an empty string) or not. To me, "unknown" can
> be compared with an empty string to see if it's null or not. Sorry -
> head is spinning.
>
> Mike.
>

It's no wonder your head is spinning. Even the experts disagree about the true meaning of NULL. Let's see if we can make some sense out of it.

The best interpratation of NULL is "no value present", which is the same as your first interpretation.

The problems begin with the inferences the reader draws from the absence of data in a certain place.
Ultimately, in each case, it's a communications question between the writer of the row that contains the NULL
and the reader that reads the NULL. The absence of data can mean whatever the two of them agree that it means.

Like you, I have a problem with the assertion that NULL is like "unknown". "Unknown" asserts that a value exists, even though it is not known. I don't believe that every NULL asserts, or should assert, such a thing. Let's take a place (I would call it a field, but I don't want to draw fire from Joe Celko) where data might be missing, because there is no such data in the subject matter world.

Let's take a cell whose meaning is: "Spouse's First Name". And let's imagine a context where such a cell might exist in connection with a single person. A NULL in such a cell does NOT mean unknown. The value isn't there because there is no such value. That doesn't mean "unknown".

The theorists in c.d.t are going to come out in droves and try to teach me that, properly normalized, the circumstance I describe cannot occur. Save it, theorists. We've been there before. You're right, but you miss the point. Let's move on.

It's a bad idea to equate the empty character string with NULL. You see, if I look at string concatenation, the empty string has the nice property that,

   A || EMPTY = EMPTY || A = A for every string A.

This should remind you of zero, not of NULL. This is actually useful if you begin to use VARCHAR for certain parsing problems.

It gets worse. Oracle engineers, in their wisdom, chose to use the same representation for the empty character string and for the NULL marker in VARCHAR columns. Bad idea. The Oracle documentation will warn you against coding that depends on this confusion, but who reads documentation? Bad idea. When Oracle finally comes out with a version where isnull (EMPTY) is false, a lot of code is going to break.

Oracle Rdb, by the way, makes no such mistake, unlike Oracle RDBMS.

Finally, read up on Sherlock Holmes and the curious incident of the dog in the night. It provides some insight here.

I know this doesn't really answer you, but I hope it helps. Received on Thu Nov 17 2005 - 15:09:26 CET