Oracle Null

These are not necessarily unexplainable idiosyncrasies. Rather, this is a list 
of Null usage cases that may surprise me personally. Note: Null value is 
spelled "Null" in here.


(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


(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 

Oracle SQL Reference says "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."

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.


(5) Unknown OR True returns True, Unknown AND 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'