Re: Why are empty VARCHAR2S always NULL?

Hi All,

For some unknown (null ?) reason, every time I have this discussion with someone, voices in my head say 'planet earth is blue and there's nothing I can do ..." Weird eh.

I agree completely with Niall. I actually think NULL is quite a reasonable term, but perhaps if it was renamed NOTHING or NADA or ZIP or VACUUM or indeed UNKNOWN, then there would be far less confusion about this.

When logically viewed as an unknown, then all logical comparisons make sense. When someone can't (or won't) see it as an unknown, then confusion and problems arise.

Regarding the empty string argument, again I think confusion is caused because other databases and other languages treat defined and undefined variables as being different. However, an empty string (i.e.. with nothing in it) logically doesn't have a value and hence again logically is treated in my tiny weeny view quite reasonably in Oracle.

To me, there has always been a quite reasonable solution to the argument, "but I want to join or interpret an unknown value as something other than a NULL". And it's a solution that is perfectly acceptable in 99.9% of cases. Simply set it to a space. It takes up only one character, in 99.9% of cases is perfectly acceptable as a value that is differentiated from other values and can be logically joined and manipulated to your hearts content.

I do see a time when humans will be able to transport themselves back to forth in time and see future events (like Crystal Palace winning the Premier League) before such events actually occur. And I can also see Oracle software keeping up with such progress in technologies and having a FLASHFORWARD capability.

Then NULLs would be treated differently and joins being quite possible in that although the value of the last_won_premier_division column for the Crystal Palace row is currently NULL, we will be able to determine that in the future it will have a value (2256) and hence be able to quite successfully join it with other tables.

Although I would love to be around to see Crystal Palace finally win something, I hate to think what discussions of NULLs would be like then ;)

Cheers

Richard

Niall Litchfield wrote:
>
> <ctcgag_at_hotmail.com> wrote in message
> news:20020821133838.353$Ug_at_newsreader.com...
> > > What
> > > representation would you use for unknown.
> >
> > Null.
>
> The 4 characters 'NULL' or an empty string or what? I may be missing
> something here (not being a cs major an all) but what do you put in the
> oracle character datatype - or how do you modify it - to represent null if
> not an empty string.
>
> >>
> >> What you mean is how do I join two unknown values.
>
> >No, what he means is 'how do I join by an empty string?'. Why do you find
> >this so hard to believe?
>
> In part because of the examples.
>
> I want to list parts that have not shipped to customers without a customer
> number.
> I want to match parts against suppliers without a supplier number.
>
> Its all a bit 'when did you stop beating your wife?'
>
> --
> 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
> ******************************************
Received on Wed Aug 21 2002 - 19:27:22 CDT