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

michael_at_preece.net wrote:
> 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.

SQL's null isn't equal to any value, including itself, so get used to the idea of it being something different from an empty string or a zero. It's supposed to be a token that indicates the absence of a value.

Part of the problem with undertsanding nulls is that conceptually SQL tries to use null to represent two things: both the concept of "unknown" and that of "inapplicable". As a result it does a bad job of representing both of them. SQL is also inconsistent and counter-intuitive in the way it applies its three-value logic with nulls. Unfortunately, for practical purposes it's hard to avoid dealing with nulls in SQL because they crop up everywhere. You just have to learn the nuances and live with them.

-- 
David Portas 
SQL Server MVP 
--