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

On 17 Nov 2005 22:56:55 -0800, Marshall Spight wrote:

(snip)
>The question also arises as to what exactly SQL's null "is."
>Is it unknown or empty? The answer, alas, is that it depends
>on the context, a disastrously bad state of affairs.

Hi Marshall,

According to the ANSI specification, null has one and only one meaning: it's a marker to represent the absence of a value.

So it's neither unknown, nor empty.

> If you have
>a table with a nullable int column and two rows, one null, and
>you select sum(column) from table, you'll see that the nulls are
>treated as empty.

No. Check the definition of the SUM aggregate function in the ANSI specification. SUM is defined as the sum of all non-NULL values in the set. So the result of a SUM says nothing about how NULL values are handled, since they are by definition excluded from the operation.

>If you have a row with two int columns, one
>null, and you select column1 + column2, you'll see that null
>is treated as unknown.

Again no. It's treated as a missing value.

I'll ask you to tell me the sum of two numbers. The first is 42.

What is the answer?

As long as I don't tell you the second number (it's absent in this message), you won't be able to tell me the answer (it'll be absent in your reply). If we were talking SQL instead of English, I'd have to represent the abesnce of the second number as NULL. And you'd have to represent the absence of any answer as NULL in your reply.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)