All about NULL in Oracle - Part III

Aggregate Functions

Aggregate Functions like SUM , COUNT will ignore NULL vaules and processes the NON NULL values alone.

SQL> Create table TestSumNull(a Number);

Table created.

SQL> Insert into TestSumNull values(16);

1 row created.

SQL> Insert into TestSumNull values(5);

1 row created.

SQL> Insert into TestSumNull values(NULL);

1 row created.

SQL> Commit;

Commit complete.

SQL> Select * from TestSumNull;

A
----------
16
5

SQL> Select NVL(A,0) From TestSumNull;

NVL(A,0)
----------
16
5
0

SQL> Select Sum(A) , Count(A) from TestSumNull;

SUM(A) COUNT(A)
---------- ----------
21 2


you can see from above result that the COUNT function has ignored the NULL value.

SQL*PLUS Commands

There is a SQL*PLUS command SET NULL .
so the String what is given will be displayed in case a NULL is returned (Alternative for NVL in a SQL*PLUS environment alone).

SQL> Set NULL EMPTY

SQL> Column Value Format a10

SQL> Select Null "Value" From Dual;

Value
----------
EMPTY

as you can see "Empty" is displayed instead of a NULL (without using NVL)...