Re: null? not null?

On Sun, 08 Sep 1996 21:10:32 +0200, Struyve Stephane <ssteph_at_glo.be> wrote:

>A NULL value can normally be interpreted as 'I do not know what it is'

A NULL is an undefined value for a column. Any datatype can be a NULL. It is indicated by an additional byte stored with the column in the table if the column can contain null values (this is the default unless you specify not null). A value of -1 in the "NULL" field indicates that the column has no value for that row. When Oracle is told to "null" a column with data in it, it simply sets the null byte. It does nothing with the data in the column.

>So the following statements all give "no rows selected":
>
>select 'x' from dual where 4 in (1, 2, 3, null);
>
>select 'x' from dual where 4 not in (1, 2, 3, null);
>
>But, there's one exception... When we talk about empty strings. Since an empty string
>and a null string has the same representation, Oracle does not know the difference.
>
>So, following statement gives something:
>
>select 'x' from dual where '' is null;

This statement returns
'
-
x

That's real meaningful. (Yes, I actually ran it.)

>
>Next statement returns nothing :
>
>select 'x' from dual where '' = '';
>
>Something like
>select 'x' from dual where '' || '' = ||;
>gives "no rows selected".
>
Actually, the above is not executable. The || by itself is meaningless and causes an incomplete expression.

>And something like
>select 'x' from dual where '' || 'abc' = 'abc';
>gives you a row.
Actually if gives you:
'
-
x

Sheesh.

>
>In contrast,
>select 'x' from dual where '' + 3 = 3;
>gives you no row, something we expected since Oracle converts the '' to null and
>null + something is null, which gives FALSE (in fact NULL) when compared to 3.
>
>
>So, for strings their is an exception, but a normal exception if you ask me.
>
>The empty string would be very useless if it had the same comportement as a null.
>
>Steph.
Received on Sun Sep 08 1996 - 00:00:00 CEST