Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why are empty VARCHAR2S always NULL?

Re: Why are empty VARCHAR2S always NULL?

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Wed, 21 Aug 2002 19:30:37 +0400
Message-ID: <ak0bn4$689$1@babylon.agtel.net>


Well, at times I see people questioning sole existence of NULL in databases. :) "Since NULL does not represent anything and is clearly not datum, what's the use of storing it in the DATAbase? What's the use of an unknown value to a human - and databases are ultimately designed to serve humans? If you ask database a question and receive "NULL - I don't know" in response - what's the use of such database? I could give the same answer in no time and with no effort!" :)

As of your particular case, if your part is not yet shipped, it is unknown to whom it was shipped. Actually, the part shouldn't have made it to 'shipped' table until it was actually shipped, at which point customer id of recipient is known. If I were implementing something like this, I would implement status flag with values of 'shipment pending','shipped','canceled', etc., and customer id would always be known for ordered part. Otherwise, how would I track who ordered the part initially and to whom it should be shipped?

Though I tend to agree with you that 'empty' is perfectly valid and known value for character data and shouldn't have been implemented as NULL, that's what we have in Oracle and we have to live with it. It is confusing, and it makes things a bit harder to implement, but nothing's perfect in this world...

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Wolfram Roesler" <wr_at_grp.de> wrote in message news:Xns9271AB88C729Awrgrpde_at_62.153.159.134...

> "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 - 10:30:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US