Re: Why are empty VARCHAR2S always NULL?

"Wolfram Roesler" <wr_at_grp.de> wrote in message news:Xns927195BD69F7Awrgrpde_at_62.153.159.134...
> "Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in
> news:ajvnht$rq8$1_at_babylon.agtel.net:
>
> > This is not a bug, this is design decision taken by Oracle long time
> > ago and it is unlikely to change. You have to live with it - empty
> > varchar2 in Oracle was NULL, is NULL and probably will be NULL for the
> > life of Oracle database software.
> >
>
> Is there any wisdom behind that design decision, or is it merely a
> bug that had to be declared a feature for compatibility reasons?
> I find it very counter-intuitive that, for example, ''='' and
> ''!='X' are false.

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.

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.

>
> How do I join by an empty string, for instance? Consider:

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

HTH

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************