Re: NULLs

That's not a good example. '' is a null or empty string but not NULL. Here's a case (in 9.2.0.7.0) that says two NULLs do not violate the unique constraint:

SQL> create table test (a number);

Table created.

SQL> create unique index unq_test on test (a);

Index created.

SQL> insert into test values (null);

1 row created.

SQL> / 1 row created.

SQL> select count(*) from test where a is null;

  COUNT(*)

         2

There're some anomalies about null. If you insert both a NULL and a null string (i.e. '') into the table, a query WHERE column is NULL selects both of them.

By the way, inside DECODE, NULL can be equal to NULL. See http://groups.google.com/group/comp.databases.oracle.server/msg/e38d8c83cb6f5572

Yong Huang Received on Wed Dec 21 2005 - 16:35:18 CST