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'

Source : http://yong321.freeshell.org/computer/OracleNull.txt