Re: '' = null ?

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.

--
David Fitzjarrell
Oracle Certified DBA


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