Re: Why are empty VARCHAR2S always NULL?

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in news:3d639ab8$0$8507$ed9e5944_at_reading.news.pipex.net:

> Maybe I'm just sheltered but I can't see any reason why anyone would
> regard using the absence of any entry as signifying NULL a bug. What
> representation would you use for unknown.

NULL is unknown, but '' is a known empty string. At least that's what I'd expect it to be.

We have, for example, a table with parts we shipped. That table contains a "customer id" column. Some parts have not yet been shipped, so that column is empty. It's intentionally left empty; it's not intended to be NULL because its contents is not unknown. I can search for "customer = 'Oracle'" or "customer LIKE 'ABC%'" but I have to use "customer IS NULL" to search for parts that have not yet been shipped. Ok so far, but the problem came up when someone wanted to search for all parts not shipped to a particular group of customers. He tried "customer NOT LIKE 'Pattern'" and was very surprised that he didn't get any of the not yet shipped parts. Ternary logic is hard to explain to non-databasers.

Another problem is that we get NULL without noticing. We let users enter data and store it in the database, something like this:

        update table set column='data';

and expect to be able to retrieve it later with:

        select * from table where column='data';

This works fine except when data happens to be empty, we have to make sure we create a proper IS NULL clause even though we didn't "set column=NULL". Quite confusing, tedious, and error-prone.

> As for counter-intuitive well I sort of agree. Thats because we've got
> so used to TRU/FALSE logic that tri-value logic throws us. If you
> think of NULL as an unknown vale then you can rewrite your two
> comparisons as
>
> 1. Is this unknown value equal to this second unknown value? Clearly
> this can't be said to be either true or false but unknown.
> 2. Is this unknown value something other than 'X'? Again how can I
> possibly say true or false to this.

... but if you think of '' as a known (yet empty) value? I'm not doubting the use of NULL, but I want NULL and an empty string as two distinct things.

> What you mean is how do I join two unknown values. This is a
> meaningless join.

Joining by NULL certainly is meaningless but, given the application above, joining by empty columns is not. Imagine a second table which also has (possibly empty) customer ids which is to be joined against the parts table for some reason.

Regards
W. Roesler Received on Wed Aug 21 2002 - 09:47:48 CDT