Oracle Database - Null value (Missing Value)

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)

SYS_OP_MAP_NONNULL

Reference


Powered by ComboStrap