Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: So what's null then if it's not nothing?

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

From: <>
Date: 27 Nov 2005 18:29:37 -0800
Message-ID: <>

erk wrote:

> wrote:
> > Well Jon - I'd say they're both NULL. NULL is type-less.
> So any attribute can store a "value" outside its intended domain? I
> thought you mentioned earlier that NULL is an empty set - is it
> actually of type E, where E consists of the empty set?

NULL is NULL regardless of type. Anything can be empty.

> > In the case of
> > "no value at all" for an integer variable we can't store an empty
> > string so we would store NULL.
> This makes no sense in other domains either - what about "NULL dates"?
> What sort of date manipulations (e.g. "same date next month") can one
> do on it?

Regarding dates, if a date is NULL then we can't really do much with it - like work out age or determine birthdays. All we know is that there is a NULL date present. We can, though, select all the things with a NULL date and/or all the things with non-NULL dates. All of the NULL dates will be equal. Things with no actual date and no NULL date will never be part of our selection because we simply don't have that data.

Let's say we have a historical set of data. The data originated from forms that were filled out on the internet. At some point in time we added a date question to the form. Sometimes the people filling in the form decided, for whatever reason, to leave the date field empty. So now we have some info with missing dates (the question was never asked and is therefore unknown), some with empty, or NULL, dates (the user decided not to supply us with a date) and some with actual dates. We can ask the questions : which forms had a date question on them? which of those forms have an empty (NULL) date? and which of those forms have an actual date? Forms which never had a date question on them would not be any part of a result set - because we have no date data.

> > I know a lot of educated people will
> > tell me that NULL, when compared to any other value, results in
> > Unknown. I'd agree - if I agreed that NULL was unknown - or missing -
> > or absent - or not, in fact, data. I believe that NULL is, correctly,
> > "no value at all".
> I keep reading this, over and over, and it still makes no sense. Can
> you define some semantics of a null when it occurs in a date attribute,
> a string attribute, an integer or real attribute, custom typed (e.g.
> "object") attributes? How do they "behave" in the presence of
> operations over the attribute's domain?

If I have an empty mug on my desk in front of me I can plainly see what's in it - nothing (=NULL). This is very different to it having something unknown in it. This is irrespective of the properties ascribed to the contents of a mug. If someone enquires as to the type of liquid it contains I can tell them definitely that it contains no liquid of any type. Enquiries on all of the mugs on my desk containing tea will not return my mug. Similarly for mugs containing coffee - and for mugs containing unknown liquids. If someone was collecting empty mugs on their way out to the kitchen, however, they would, hopefully, collect my mug and leave those mugs alone which are not empty and contain some unknown liquid, or are hidden under program listings or books on SQL. If the mug was not on my desk - if it was absent - or it was hidden, then I couldn't say what was in it. The contents would be unknown. I wouldn't know whether it was empty, had tea in it, or some unknown liquid. I would have no information on which to base any decision.

> > We store something on a database because we know
> > something. We can know something is empty.
> Maybe if you define "empty." Does "empty" mean the same as "no value at
> all"? "Empty" isn't any more helpful than "NULL" to me.

I feel your pain. This is, perhaps, similar to discussions that might have taken place when the Arabic concept of ZERO was introduced to people to whom it was a strange and foreign concept. You have no difficulty in dealing with the concept of a NULL string. That's a start.

> > We can't know anything about something that doesn't exist.
> So why is it in the DB?

Exactly. Missing, or absent, data should be exactly that - nowhere to be found.

> > We have no facts about it. We have no data.
> So we know the existence of something non-existent?

No. We can know the existance of something with no value, however.

> I've just read a
> large chunk of this discussion, and have seen nothing but tail-chasing
> so far. Can you define, with any precision, the semantics of a "NULL
> value"?
> - Eric

Sorry Eric. I don't believe I can do any better.

Mike. Received on Sun Nov 27 2005 - 20:29:37 CST

Original text of this message