Re: null? not null?

Struyve Stephane wrote:
>
> 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.

Steph,  

 Just to let you know, future releases in Oracle will not recognise ''  so must use null.

 Rakesh Received on Tue Sep 17 1996 - 00:00:00 CEST