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

michael@xxxxxxxxxx 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
--

.

Relevant Pages

  • Re: So whats null then if its not nothing?
    ... two nulls are not distinct; ... it means effectively that unknown is treated as ... constraints leads to tuples being rejected if false; ... The SQL unique constraint does not consider two ...
    (comp.databases.theory)
  • Re: Why does 1:3 relationsihp require another table?
    ... practically difficult if not impossible and that is why NULLs or any such ... If representation of unknown value is the idea behind the ... implementation of NULLs in SQL, then SQL avoids far too many kinds of ... By assuming support for just one kind of missing information (Unknown value ...
    (microsoft.public.sqlserver.programming)
  • Re: Use of Null
    ... an empty string for a character column or the current time for a ... for example the string "UNKNOWN" for some attribute. ... I use NULLs where I think I need them, ... invent token values for some unknowns but if those tokens also need special ...
    (microsoft.public.sqlserver.server)
  • Re: When is a NULL Not a NULL ?
    ... I would always *try* to avoid them, Nulls that is, with careful database ... you are speaking only of a convention among SQL users. ... something that is unknown. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Use of Null
    ... where it isn't that the data is UNKNOWN but that the attribute is just no ... don't have any associated NULLS. ... (Be carefull an empty string is in> some database implementations the same as a NULL, ... > in all applications and does not work for small datetime fields. ...
    (microsoft.public.sqlserver.server)
Loading