Re: null? not null?

A NULL value can normally be interpreted as 'I do not know what it is'. 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;

Next statement returns nothing :

select 'x' from dual where '' = '';

Something like
select 'x' from dual where '' || '' = ||; gives "no rows selected".

And something like
select 'x' from dual where '' || 'abc' = 'abc'; gives you a row.

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