RE: NULL value EQUALS to EMPTY string?!

From: Olaf Naumann <ONaumann_at_netcologne.de>
Date: Mon, 7 Feb 2000 16:25:23 +0100
Message-ID: <87mqp7$b2l$1@news.netcologne.de>

the problem is null is null = null returns false since nulls are not concidered equal

SQL> select 42 from dual where null=null;

no rows selected

olaf

---

Hi!

It's notorious that table cells may have the special value null (or unknown).
This value is different from the number 0, and it is also different from the empty string ''.

But...

SQL> create table test2(
  2 n number,
  3 c char(5),
  4 v varchar2(5),
  5 d date);

Table created.

SQL> insert into test2 values(1, null, null, null);

1 row created.

SQL> insert into test2 values(2, '', '', '');

1 row created.

SQL> commit;

SQL> select * from test2;

         N C V D
---------- ----- ----- ---------

         1
         2

SQL> select * from test2 where c is null;

         N C V D
---------- ----- ----- ---------

         1
         2

SQL> select * from test2 where v is null;

         N C V D
---------- ----- ----- ---------

         1
         2

SQL> select * from test2 where d is null;

         N C V D
---------- ----- ----- ---------

         1
         2

SQL> select * from test2 where c='';

no rows selected

SQL> select * from test2 where v='';

no rows selected

SQL> select * from test2 where d='';

no rows selected

SQL> insert into test2 values(null, null, null, null);

1 row created.

SQL> commit;

SQL> select * from test2 where n is null;

         N C V D
---------- ----- ----- ---------

SQL> select * from test2 where n='';

no rows selected

SQL> So, NULL is equal to '' for strings
and is not equal to '' for numbers!

Have you any comments?
Is it possible to insert NULL value into char, varchar2?

Klim.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Feb 07 2000 - 09:25:23 CST