Re: empty string == NULL

I don't understand the statement that there's no way of knowing what an empty column contains. It seems obvious to me that it contains nothing, although that leads to the English-statement paradox that nothing is something. (The paradox may exist in other languages also, but it's still only a paradox due to language limitations. Does 0 represent a quantity? Is black a color? I won't say that all paradoxes are due to language limitations, but I suspect that most of them are.)

Likewise, rows containing nulls in columns being used to qualify candidates for selection should be treated the same as any other value, i.e. returning FALSE or TRUE based on whether or not you want to select rows containing nulls in those columns. This, as I understand it, is exactly how nulls are in fact handled, i.e. simply as a separate and unique 'value'. It seems to me that the primary advantage of Null is that it is typeless. That is, I can

SELECT * FROM TABLEA WHERE COLUMNA = '' only when COLUMNA is a character datatype but I can SELECT * FROM TABLEA WHERE COLUMNA IS NULL regardless of COLUMNA's datatype.

Since these statements are not synonymous per Codd, it complicates the programming but it's really no different than ensuring that COLUMNA is truncated when the user inserts nothing but spaces. (Imagine the problem selecting character data if you don't truncate spaces! It's only one extra step to check the length after truncating the spaces and change the 'value' to NULL when the length=0. That preserves the logic if Oracle should ever change how it handles 0-length strings. Sorry about calling NULL a value, but I can't think what else to call it. Non-value?)  

Jerry Gitomer wrote:
>
> The "trinary" logic of having True, False, and Null goes back to Codd's
> original article on relational databases. I suspect that the only way it will
> get changed is if it is explicitly banned in the ANSI SQL standard.
>
> If I remember Codd rationale for NULL was that there is no way of knowing what
> an empty column contains and therefore rows containing nulls in columns being
> used to qualify candidates for selection should be ignored.
>
> You can always use the NVL function to convert NULL into whatever you feel is
> appropriate -- and the fact that any two developers might not agree as to what
> is appropriate tends to prove that Codd's theoretical approach is really quite
> practical
>
> hth
>
> Andreas Steidle wrote:
>
> > From the oracle doc:
> >
> > Do not use null to represent a value of zero, because they are not
> > equivalent. (Oracle currently treats a character value with a length of zero
> > as null. ... [1])
> >
> > Is there a workround for this contardictory semantik garbage? A string is a
> > string, even if it's empty!!! Perhaps some env-var??
> > In other words: Is there a way to make Oracle remember a '' and NOT convert
> > it to NULL ???
> >
> > thanx, Andreas
> >
> > [1] However, this may not continue to be true in future releases, and Oracle
> > recommends that you do not treat empty strings the same as NULLs.
> >
> > Hope this will happen very soon as default behavoir.
>
> --
> Once I figured out how to spell DBA I became one
> -- Jerry Gitomer
Received on Mon Dec 27 1999 - 12:29:38 CST