About Oracle NULL value

In a Database table if a column has no value, then the column is said to be NULL. It could be said as a place-holder where the database does not have any information about the value. It represents nothing, not a space, not a zero(0), nor a blank value.

1) A null value is not equal nor unequal to another null, i.e. can't use operators like "=" or "!=". Though, Oracle considers two nulls to be equal when evaluating a DECODE function. NULL values can be only tested with IS NULL and IS NOT NULL conditional operator.

hr@ORA10G> SELECT * FROM test_table ORDER BY id;
        ID COL1       COL2             COL3
---------- ---------- ---------- ----------
         1 KA         blr               100
         2            del
         3
         4 MAH                          400

hr@ORA10G> SELECT * FROM test_table WHERE col3 = NULL;
no rows selected

hr@ORA10G> SELECT * FROM test_table WHERE col3 IS NULL;
        ID COL1       COL2             COL3
---------- ---------- ---------- ----------
         2            del
         3

hr@ORA10G> SELECT * FROM test_table WHERE col3 IS NOT NULL;
        ID COL1       COL2             COL3
---------- ---------- ---------- ----------
         1 KA         blr               100
         4 MAH                          400

2) An expression containing a null value always returns a null value. For e.g. arithmetic expression containing a null value always results to null.
For e.g. 25+null will give result as null.

hr@ORA10G> select * from test_table order by id;
        ID COL1       COL2             COL3
---------- ---------- ---------- ----------
         1 KA         blr               100
         2            del
         3
         4 MAH                          400

hr@ORA10G> select col3+300 from test_table where id=2;
  COL3+300
----------


hr@ORA10G> select col3+300 from test_table where id=1;
  COL3+300
----------
       400

3) Most aggregate function like AVG(), COUNT(), ignore null values.

hr@ORA10G> select * from test_table order by id;
        ID COL1       COL2             COL3
---------- ---------- ---------- ----------
         1 KA         blr               100
         2            del
         3
         4 MAH                          400

hr@ORA10G> select avg(col3) from test_table;
 AVG(COL3)
----------
       250

hr@ORA10G> select count(col3) from test_table;
COUNT(COL3)
-----------
          2

4) The NULL values in the column sort as the highest value by default.

hr@ORA10G> select * from test_table order by col3 desc;
        ID COL1       COL2             COL3
---------- ---------- ---------- ----------
         2            del
         3
         4 MAH                          400
         1 KA         blr               100

5) The functions REPLACE, CONCAT, DECODE, NVL, NVL2 returns non-null values when invoked with a null argument. Most others scalar functions returns null with a null argument.
Both NVL() and NVL2() function replace null values with a specified value.

NVL(exp1, exp2) - This returns exp2 if exp1 is null, else return exp1. Both exp1 and exp2 should be of same datatype or it must be possible to implicitly convert exp2 to the type of the exp1.

hr@ORA10G> SELECT NVL('abc','xyz') FROM dual;
NVL
---
abc

hr@ORA10G> SELECT NVL(null,'xyz') FROM dual;
NVL
---
xyz

hr@ORA10G> SELECT NVL(123,'xyz') FROM dual;
SELECT NVL(123,'xyz') FROM dual
               *
ERROR at line 1:
ORA-01722: invalid number

The last example above is an error because 'xyz' cannot be converted to a number.

NVL2(exp1, exp2, exp3) - If exp1 is null then returns exp3, else return exp2. The datatypes of exp2 and exp3 must be compatible, or it must be possible to convert exp3 to the type of the exp2.

hr@ORA10G> SELECT NVL2('aaaa','bbbb','cccc') FROM dual;
NVL2
----
bbbb

hr@ORA10G> SELECT NVL2(null,'bbbb','cccc') FROM dual;
NVL2
----
cccc

hr@ORA10G> SELECT NVL2(null,'bbbb',1234) FROM dual;
NVL2
----
1234

hr@ORA10G> SELECT NVL2(null,1234,'cccc') FROM dual;
SELECT NVL2(null,1234,'cccc') FROM dual
                      *
ERROR at line 1:
ORA-01722: invalid number

The last example above is an error because 'cccc' cannot be converted to a number, whereas in the previous example it is possible to convert value "1234" to a varchar2 type.

Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm