Oracle Null

These are not necessarily unexplainable idiosyncrasies. Rather, this is a list 
of Null usage cases that may surprise me personally. Note: all tests are done 
in Oracle 9.2.


(1) Null doesn't count in aggregate function.
create table testnull (a number);
insert into testnull values (1);
insert into testnull values (2);
insert into testnull values (null);
select count(*) from testnull; <-- returns 3
select count(a) from testnull; <-- returns 2

create table test (name varchar2(10), value number);
insert into test values ('xx', 12);
insert into test values ('xx', null);
insert into test values ('yy', 123);
select name, count(*) from test group by name;
select name, count(value) from test group by name;

NAME       COUNT(VALUE)
---------- ------------
xx                    1 <-- would be 2 if select name, count(*) ...
yy                    1


(2) Inserted null string converted to Null.
create table testnull (a varchar2(10));
insert into testnull values (null);
insert into testnull values ('');
insert into testnull values ('' || 'Hello');
insert into testnull values (null || 'Hello');
select dump(a) from testnull;

DUMP(A)
---------------------------------------------
NULL
NULL
Typ=1 Len=5: 72,101,108,108,111
Typ=1 Len=5: 72,101,108,108,111

Similarly, setting a column to Null in updates is equivalent to setting it to a 
null string, i.e.
update testnull set a = null;
is exactly the same as
update testnull set a = '';


(3) Where can Null be compared?
select decode(null, null, 'Null equals Null in DECODE') from dual; 

DECODE(NULL,NULL,'NULLEQUA 
-------------------------- 
Null equals Null in DECODE 

Normally, Null compared with anything returns unknown. But "In a DECODE 
function, Oracle considers two nulls to be equivalent. If expr is null, then 
Oracle returns the result of the first search that is also null." (Ref. Oracle 
SQL Reference)

Another place where Null can be compared is in range partition definition, 
where MAXVALUE is greater than Null (Ref. J. Lewis "Practical Oracle8i", 
p.241). 


(4) [Related to (3)] Unique constraints.
create table test (a number);
create unique index unq_test on test (a);
insert into test values (null);
insert into test values (null); <-- No error.
You *are* able to insert another Null without getting ORA-1 (unique constraint 
violated).

create table test (a varchar2(1), b varchar2(1));
create unique index unq_test on test (a, b);
insert into test values ('A', null);
insert into test values ('A', null); <-- Get ORA-1
truncate table test;
insert into test values (null, null);
insert into test values (null, null); <-- No error
So if all columns are Null, the unique constraint will not be violated. If one 
or more columns have non-Null values, the constraint takes effect.

A unique constraint is implemented by the index. Because a row with NULL's in 
all columns is not indexed, Oracle won't detect that two rows of all NULL's in 
columns are in the table. (Incidentally, a unique constraint can be implemented 
by a non-unique index, and that won't change our conclusion here. If you create 
a regular index and then add a unique constraint, you can still insert 2 rows 
of all NULL's.)


(5) Unknown OR'ed True returns True, Unknown AND'ed False returns False.
create table test (a number, b number, c number);
insert into test values (3, 4, null);
select 'Got it' from test where b < c or a < b; <-- returns 'Got it'
select 'Got it' from test where not (b > c and a > b); <-- returns 'Got it'