Re: Weird Query

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 01 Aug 2005 23:48:59 +0200
Message-ID: <dcm5bq$hhj$00$1@news.t-online.com>

AK schrieb:
> Maxim,
> let's suppose I was wondering how much money I have on my checking
> account.
> 1. I didn't know, that's NULL.
> 2. I went to an ATM and run a query, Let's suppose I've got $100
> 3. I'd withdrawn $100 from the account and my receipt said I had $0.00
> remaining balance.
>

I can't follow. It seems to me , you try to compare apples and oranges. If you don't know about actually stored value, it doesn't mean there is no value stored. (back to Oracle - NULL and 0(Zero) differ)

> Don't tell me that after step 3 account_balance "has no value" - it
> does! The value IS known - $0.00. There is a big difference between a
> known zero value and an unknown value.
See above
>
> Similarly, there is a big *practical* difference between a known zero
> length string and an unknown string. I have given you a very simple
> real life example when it is important.
>
>

>>>to be able to

>
> distinguish these cases you need to introduce new entity , ( e.g.
> nation
> ), one of those attribute can be "middle name allowed" and create a
> relationship between that 2 entities.
> <<
> I don't need any 'zero balance allowed' column to distinguish between
> steps 1 (unknown balance) and 3 (zero balance). Besides, in many
> countries, such as United States middle names are optional, not
> mandatory. Since a middle name is a personal choise, I don't think your
> suggestion would help.
>
> BTW, other RDBMS will let me to distinguish between an empty string and
> an unknown one. MS SQL Server:
>
> create table t(i int, c varchar(10))
> insert into t values(1, '')
> insert into t values(2, null)
> select * from t where c=''
>
> i c
> ----------- ----------
> 1
>
> What do you think?
>

There is nothing to discuss about. You must choose right tool for your needs. If the ability "natively" to distinguish between NULL and empty string is of big value for your business needs, you should look on the engines that support it. As in Oracle you can not ( without additional attributes ) distinguish between NULL and empty string, you should not use Oracle. The equity of NULL and empty string in Oracle was accepted by design of this rdbms. Complain about it doesn't make any sense. You can do in Oracle really a lot of things with as little efforts as possible, but it doesn't exclude the possibility that some other engines make some things easier...
Also while i don't really see the business need to distinguish NULL and '', i am very comfortable with Oracle - if you asked me for my opinion...

And please, don't start the next religious war...

Best regards

Maxim Received on Mon Aug 01 2005 - 16:48:59 CDT