Re: 3 value logic. Why is SQL so special?

From: Volker Hetzer <firstname.lastname_at_ieee.org>
Date: Mon, 18 Sep 2006 22:54:10 +0200
Message-ID: <een11i$6dj$1@nntp.fujitsu-siemens.com>

Gene Wirchenko schrieb:

> Volker Hetzer <firstname.lastname_at_ieee.org> wrote:
> 

>> Gene Wirchenko schrieb:
>>> Volker Hetzer <firstname.lastname_at_ieee.org> wrote:
>>>
>>> [snip]
>> Pity.
>>
>>>> What I don't understand is that everyone who decries nulls, somewhere
>>>> works with default values. Ok, big deal, NULL is an universally applicable
>>>> default value that cannot be confused with real data.
>>>> What am I missing?
>>> That it can be confused with real data.
>> How?
>> Every interface worth its name has either a special flag or allows the mapping
>> to a certain value upon reading it out of the database. And if not, then, at
>> least in oracle, I can create a simple boolean (C convention) flag in the select
>> list: select expr, nvl2(expr,0,1) Expr_Is_Null ...
>
> This means that there is no just getting the value. There is no just getting the value with default values either, because a) I have to differentiate between value and non-value anyway, with default

    values I just mix up application defaults with database values. Btw, the example I gave     was the most complex one for when default values don't work for the application.     Normally, if I'm concerned about NULLs I select nvl(expr,<DEFAULT>)... for the     client app and select expr for database internal work (like insert into ... select ...) b) as soon as I've got an outer join, I've got NULLs anyway. And even if NULL would

    be replaced by a bit of SQL syntax, every application, table, view, coder, company     could have its own default value, which might even change over time. Which is hard     to maintain and to code.

> Now, you
> have to check everything for NULL. Quite the mess. Otherwise I'd have to check for the default value. Worse, the default value may be different depending on what application accesses the data (0, -1, INT_MIN, INT_MAX, ''), so it IMHO makes sense to store an universally valid default. Which NULL happens to be.

>>> For one thing, NULL is
>>> *NOT* a value. It is the absence of a value.
>> Conceptually, so is any other default value. Only, with NULL, the database agrees.
>
That's why I wrote "conceptually". Both express a "not filled in", albeit one with database support and one without it.

> 

>> With other default values I have to code it all in the sql.
>
> Yes, you set the default.
But with NULL I don't. Or, only at the end of everything, when retrieving final results and client specific.

By the way, before I exit this thread (I'm not that proficient in theory), has anything changed between Date/Codd's original arguments and today?

Lots of Greetings!
Volker

-- 
For email replies, please substitute the obvious.

Received on Mon Sep 18 2006 - 15:54:10 CDT