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.
>

Don't know about wisdom. Looks like initially it was a programming technic decision: all fields with 0 length are null. That's true for numbers and char columns as well. May be that's why char columns have length descriptor, which doesn't make sense otherwise.
By the way, Oracle issued a notice with 8.1, repeated with 9.0 and 9.2, that empty string might not be null in future releases. I doubt it though, it's gonna invalidate billions worth of software.

> How do I join by an empty string, for instance? Consider:
>
> SELECT ...
> FROM t1, t2
> WHERE t1.col = t2.col;
>
> where t1.col and t2.col are both empty. NVL(colname,'') won't help
> since '' IS NULL.
>
> Thanks
> W. Roesler
Received on Thu Aug 22 2002 - 09:57:47 CDT