Re: When is a NULL Not a NULL ?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

« "All in all Nulls are a pain and SQL and the products only make it worse.
I would always *try* to avoid them, Nulls that is (g), with careful database
design. The way they are implemented from product to product and from
version to version could easily vary. This, markers idea, was a mistake by
Codd, IMHO, and the vendors and ANSI jumped on it and we seem to be stuck
with it for now." »

This is a big statement and we often read something similar to this, written
in one way or another. However, there is absolutely no example, proof or
demonstration behind it to back it or to substantiate it. When you want to
write a statement about something, you must come with a least a little more
in order to support it.

I could give you a lot of examples where the use of NULL leads to a clearer
code, with less fuss and easier to read. However, I won't because this is a
case where personal experience is more important than dogmatic statements.
NULL is like any other tools, you must learn how to use it properly and if
you want don't know about it or don't want to use it, that's your problem,
not the other's.

In my case, I like to have more tools in my toolbox, not less: when the only
tool you have in your toolbox is an hammer, everything else looks like a
nail and inversely, when all you have to do is to hit a nail, every tool
begins to look like a hammer.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Craig Alexander Morrison" <cam@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:OGUv56JsGHA.4612@xxxxxxxxxxxxxxxxxxxxxxx
When you said that NULLs "represent" unknown
you are speaking only of a convention among SQL users.

No I am not Unknown or Missing information is a topic of debate in R and
that is where I am coming from.

To quote from me 12 years ago:

"All in all Nulls are a pain and SQL and the products only make it worse.
I would always *try* to avoid them, Nulls that is (g), with careful
database design. The way they are implemented from product to product and
from version to version could easily vary. This, markers idea, was a
mistake by Codd, IMHO, and the vendors and ANSI jumped on it and we seem
to be stuck with it for now.

The work of Date (Default Values) with Pascal (Triggered Procedures),
McGovern (Catalog and DB Design) is the latest thinking on the replacement
for Nulls so that missing information can be better represented."



--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@xxxxxxx> wrote in message
news:1153909048.138880.293790@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Craig Alexander Morrison wrote:

Unknown does not equal Unknown.

Not so in math, logic and everyday experience (X = X). My point was
simply that SQL's NULL is not a good model for the reality and logic of
something that is unknown. When you said that NULLs "represent" unknown
you are speaking only of a convention among SQL users. It's a very
unfortunate convention because it leads to so many mistakes of the kind
mentioned.

Null is not a value

Exactly so. Which is why it's inaccurate (or at least potentially
misleading) to say that "NULL represents unknown".

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--





.

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: Practical considerations of dealing with two meanings of NULLs
    ... base relations, NULLs will inevitably ... treat NULLs like gotos; I avoid them, but I don't get hung up if I ... I don't know what a constraint variant ... assuming that SQL products advocated NULLs before class-inheritance ...
    (comp.databases.theory)
  • Re: Why does 1:3 relationsihp require another table?
    ... Pro SQL Server 2000 Database Design ... > there can be a relation with NULLs in its tuples (hence 1NF models with ... > can only conclude that you do not believe Louis and Lewis are correctly ...
    (microsoft.public.sqlserver.programming)
  • Re: So whats null then if its not nothing?
    ... Does SQL's definition of null (unknown) include the null ... > whether it's null (as in an empty string) or not. ... Part of the problem with undertsanding nulls is that conceptually SQL ...
    (comp.databases.theory)
  • Re: So whats null then if its not nothing?
    ... Does SQL's definition of null (unknown) include the null ... > whether it's null (as in an empty string) or not. ... Part of the problem with undertsanding nulls is that conceptually SQL ...
    (comp.databases.theory)