Re: '' = null ?

In article <8jqqnb$1ij$1_at_nnrp1.deja.com>,   ddf_dba_at_my-deja.com wrote:
> In article <395E9D40.D8EA1B71_at_kc.rr.com>,
> "Corey A. Harbaugh" <charbaugh_at_kc.rr.com> wrote:
> > nope
> >
> > Norris wrote:
> >
> > > Is it true in Oracle that '' is equal to null value?
> > >
> > > For example, inserting '' value into a not null column would
 return
 error?
> > >
> > > --
> > > http://www.cooper.com.hk
> >
> >
>
> Both Tom and Corey are correct; using '' as an insert value to a NOT
> NULL column will generate an error, however using '' as a comparison
 to
> a NULL value will fail:
>
> SQL> create table z (x varchar2(1) not null);
>
> Table created.
>
> SQL> insert into z
> 2 values
> 3 ('');
> insert into z
> *
> ERROR at line 1:
> ORA-01400: cannot insert NULL into ("DAVIDF"."Z"."X")
>
> SQL>
>
> Now, try to compare '' with NULL and return the row:
>
> SQL> create table z2(x varchar2(1));
>
> Table created.
>
> SQL> insert into z2
> 2 values
> 3 ('');
>
> 1 row created.
>
> SQL> select *
> 2 from z2
> 3 where x = '';
>
> no rows selected
>
> SQL> select *
> 2 from z2
> 3 where x is null;
>
> X
> -
>
> SQL>
>
> So, in one way '' IS the same as NULL (as an inserted value) but in
> other ways (selects, updates, deletes) '' is NOT the same as NULL.
>

I'd say its the same actually.

Nothing is ever equal to NULL (nor is anything NOT equal to NULL). It is unknown.

ops$tkyte_at_8i> select * from dual where null = null;

no rows selected

ops$tkyte_at_8i> select * from dual where NOT (null=null);

no rows selected

When you say:

> SQL> select *
> 2 from z2
> 3 where x = '';

that is the same as:

> SQL> select *
> 2 from z2
> 3 where x = NULL;

which will NEVER return a row -- regardless of the value of X.

> --
> David Fitzjarrell
> Oracle Certified DBA
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.