Null value Concept in Oracle

•A column in a row contains Null value in oracle is treated as special one which indicates the column in that row contains no value.

•Null is not same as zero as zero has a value but null represent no value.

•Setting a null value is appropriate when the actual value is not known or when a value would not be meaningful.

•Currently oracle Database treats a character value with a length of zero as null which is exampled in section below. But according to oracle this behavior may change in future.

•If we do any arithmetic operation of a NULL value then it evaluates to null.

•All operators (except concatenation) return null when given a null operand.

•All scalar functions (except REPLACE, NVL, and CONCAT) return null when given a null argument.

•Most aggregate functions ignore nulls. For example in a column I have 11,null,12 values. Now if I do an average of the column it will ignore the null value and will result average with other column as (11+12)/2.

SQL> select avg(sal) from sal;
AVG(SAL)
----------
11.5
•To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL.

•A null cannot be equal or unequal to any value or to another null as it represents lack of data. Though in DECODE function and two null appeared in compound function treated as same.

•If Nulls are used in Conditions the condition that evaluates to UNKNOWN acts almost like FALSE.
Like if value of a is NULL and if we use where a=NULL then it evaluates to unknown and returns no rows(act like false).

The following example will make you clear.

SQL> create table test_null(a number, b varchar2(10));
Table created.

SQL> insert into test_null (a) values(1);
1 row created.

SQL> insert into test_null values(2, null);
1 row created.

SQL> insert into test_null values(3,'');
1 row created.

SQL> select * from test_null;
A B
---------- ----------
1
2
3
So in three ways we inserted null.

SQL> select * from test_null where b=NULL;
no rows selected
Though all b columns hold null values but use of b=NULL return no rows as NULL!=NULL.