Re: NULLs

yong321_at_yahoo.com wrote:
> 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.

That's because Oracle does not distinguish between an empty string and NULL. This is a deviation from the '92 standard.

>
> 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 - 20:40:28 CST