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

Hugo

Thanks for the full reply. You'll see that I've snipped much of our preceding conversation. I hope you'll also see that nothing relevant to the points I've addressed has been snipped. Let's just move on now shall we?...

Hugo Kornelis wrote:

> On 22 Nov 2005 15:34:44 -0800, michael_at_preece.net wrote:
>

[snip]

> >> >Julian M wrote:
> >> >
> >> >> NULL in SQL can seem tricky, but it is simple enough if viewed in terms
> >> >> of what is intended by the ANSI SQL92/99 standards.
> >> >> See drafts:
> >> >> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
> >> >> http://www.ncb.ernet.in/education/modules/dbms/SQL99/
> >> >>
> >> >
> >> >Here's your problem...
> >> >
> >> >"A special value that is used to indicate the absence of any data
> >> >value."
> >> >
> >> >That's it right there. It doesn't make sense. The special value has to
> >> >be present doesn't it? So something is present. What is it? Is it an
> >> >indication that whatever it is is absent? Nope. If something is present
> >> >it's present. If it's absent it's absent.
> >This only makes sense if it
> >is interpreted to mean that it, whatever it is, is empty, or void, of
> >any value.
>
> You're still trying to put more meaning into it than NULL actually has.

No. I'm pointing out that the source of all of the confusion that plainly exists in SQL-land regarding exactly what NULL is and how it should be used is very likely due to an inadequate definition in the standard. It all seems to stem from that single sentence in the SQL-92 standard.

> The only thing that NULL means is "there's no value here". Period. I'm
> sorry if that doesn't make sense to you - but that's just the way it is.

That makes perfect sense to me. What's more - I'm in total 100% agreement. The confusion only arises when it is interpreted to mean the value is absent. Can you see the difference that exists between something being present and yet having no value and something being absent? That's the whole reason for NULL - to represent something being present and yet having no value.

>
> > Absence is the problem word. If I want something to be
> >absent I'll delete it from the database.
>
> Maybe this scratches the reason why you can't see the sense in NULL: you
> try to view SQL's NULL from a Pick mindset. In Pick, you can (if I
> understood the explanations correctly) delete a single cell (if "cell"
> is the correct term in a Pick context).

SQL's NULL seems to be different things to different people. I'm just one of them. You are correct though - I do, naturally enough, see it from a Pick mindset.

If we think of a database as a city center with loads of high-rise buildings, then, in Pick, a single high-rise can have a different number of cubicles in each "bathroom", and a different number of bathrooms on each floor. It would appear that in an SQL high-rise, each floor must have the same number of bathrooms, each with the same number of cubicles. In both Pick and SQL, any of the cubicles can have a "vacant" sign showing. In Pick the cubicles marked "vacant" are actually there. In SQL, it seems, some of the cubicles marked "vacant" might not be there at all.

> That is not possible in an SQL
> table, which has to be rectangular: you can delete an entire row or drop
> an entire column, but you can't delete a single cell. Setting a NULL
> value in a column in an SQL table is equivalent to deleting a cell in a
> Pick database.
>

OK.

In Pick there are times when we need to record that "there's no value here" and, although we *could* delete the single cell, that would not be the right thing to do - so it is necessary to store NULL instead. That is different to recording that "the value is absent - and therefore unknown" - that would be a deleted "cell".

In SQL, it seems, not only do you need a special value to repesent the fact that "there's no value here", but you also need a way of representing the fact that a value would not exist at all if it could, physically, have been deleted. This is because, although the RM is very much about separating the logical from the physical, the limitations inherent in the column/row structure (both physical and logical) prevent a single cell from being able to be deleted. I'm sure I'll be corrected if I'm wrong on this.

> > If I want to record that
> >something does not have any data value I'll record a null.
>
> Now you are losing me. Can you give me a concrete example of something
> that is present without data value?
>

OK. I have a mug on my desk. It's empty. Excuse me a moment...

Now there's no mug on my desk.

Just a moment...

Now it's a mug of tea.

Sorry - the mug wasn't made of concrete. Will china clay do? And I guess data isn't everybody's cup of tea. <g>

[snip]

>
> It is also clear that you didn't really want to be educated about how
> NULL in SQL should be interpreted, as you tried us believe in your first
> message - you merely wanted to "prove" that SQL-relational is
> "ridiculous".

[snip]

> As I already stated in several messages, your first post in this thread
> gave me the impression that you sincerely wanted to be helped to figure
> out how NULLs in SQL work. Things like "I've been accustomed to thinking
> ...", "Now, as I read up on SQL, I find that ...", a number of questions
> and "Sorry - head is spinning." gave me that impression. I thought I
> could help clarify things; that's why I replied.
>
> Of course, if your first post had already contained the texts "Poor
> fools. Poor deluded fools. The sooner SQL-relational is seen for what it
> is - ridiculous - the better." and "Strong enough you reckon?", I would
> have known better than to reply. But unfortunately, you were clever
> enough to withhold those comments until later.
>

I'll admit I didn't like what I was reading. It was, incidentally, the definition of NULL in the documentation for M$ SQL-Server. I'll admit to thinking it was flawed. I put the question here because I couldn't believe anything could be so obviously flawed, as I initially understood it, and yet taken seriously. There had to be something missing from my understanding. I have learnt from the discussion. I now believe I see the situation as it is. It is a confused situation. NULL is taken to mean multiple things. I see NULL clearly as signifying that "there's no value here" and consider that to be different and at odds with the various other interpretations attributed to it - including, and especially, "unknown". Naturally, and truthfully, enough - I believe my interpretation to be correct. I am 100% convinced that NULL means "there's no value here". In Pick, which essentially type-less and where everything is a string, it equates to an empty string. I accept it is not possible to use an empty string in SQL because it is "typed" - and NULL is inherently type-less, so it has to be represented by a special value.

[snip]

> >The presence of the special value is necessary (in some DBMSs) to
> >indicate a fact. The fact is that we have no value. Unknown, on the
> >other hand, equates to an absence of any facts. Can you see that
> >"unknown" is therefore not equal to "no value"?
>
> This is what I've been saying in all my messages: NULL is not the same
> as unknown.

It's clear - and gratifying - that we agree on this.

> NULL is a marker for an absent value.

I agree that a marker is necessary to *represent* NULL in almost all models. I disagree that it *is* the marker itself. I believe the marker itself can be absolutely anything. I believe NULL is what is represented by the marker. What is represented by the marker is that there is "no value here". If you need a marker to represent that the value is absent, because you are forbidden or unable to delete it and make it actually absent, that's something else entirely, and not to be confused with marking something as having no value. A value that is absent might have a value - we don't know, because it's not here for us to check. This is where it is very easy for people to confuse NULL with unknown - because the value is interpreted to be absent and therefore unknowable. Anything that is present can be compared to any other value that is present. A NULL can therefore be compared with another NULL and found to be equal - because they both contain no value. A value that is absent cannot be compared with anything - and this leads to the situation where I find myself reading some documentation that tries to tell me that comparing NULL with anything at all - including another NULL - will result in "unknown". *That* I find ridiculous - although I think I understand now how and why the confusion arose.

> >The standard would have been better had it
> >defined NULL as "No value. An empty set. A special value may be used to
> >indicate its presence.".
>
> No. NULL is not "no value"; it is a value - "a special value".
>
> NULL is also not an empty set. It is a scalar. Sets and scalars can't be
> swapped around at will in SQL.

I have to admit I don't know what this means. Can you explain the essential difference between sets and scalars - in so far as it is relevant to this discussion.

[snip]  

> Best, Hugo

Regards
Mike. Received on Thu Nov 24 2005 - 00:57:55 CET