About
SQL Null in the Oracle context.
By default all columns allow NULL.
Nulls are stored in the database and require 1 byte to store the length of the column (zero).
Null is not aggregated and selected on filters. If you are calculating an average grade for a student, null values will be ignored in the calculation using standard SQL, giving correct results. (the average of 5, null, 6, 7 is 6 not 4.5)
Article Related
What is NULL ?
Data Type
create view nullview
as
select null "null" from dual;
describe nullview;
Name Null? Type
-------------- -------------------------------------
null VARCHAR2
Empty String is NULL
- NULL = ''
column null format a4
select '' as "null" from dual
null
----
Null
- Second proof with NVL
select nvl('','That''s null') as "Is That Null ?" from dual;
Is That Null ?
---------------
That's null
How to write a predicate that takes null into account
How to write a predicate that takes null into account
- The state
create table IHateNull (id number not null, val1 number null, val2 number null);
insert into IHateNull values (1,2,NULL);
insert into IHateNull values (1,NULL,1);
insert into IHateNull values (1,0,0);
insert into IHateNull values (1,1,1);
- All records where ( val1 + val2 ) <> 0
select * from IHateNull where ( val1 + val2 ) <> 0;
ID VAL1 VAL2
---------- ---------- ----------
1 1 1
- should be written with:
select * from IHateNull where ( nvl(val1,0) + nvl(val2,0) ) <> 0;
ID VAL1 VAL2
---------- ---------- ----------
1 2
1 1
1 1 1
Count
Count from a Null Column must not count the NULL value and then can not use a b-tree index to count them.
select count(NullColumn) from mytable;
The following statement can use a b-tree index.
select count(NotNullColumn) from mytable;
Comparison (SYS_OP_MAP_NONNULL)
SYS_OP_MAP_NONNULL(X) = SYS_OP_MAP_NONNULL(Y)