Null Functions

Null Functions :

Null Functions are used to handle Null values . Following different kinds of Null Functions are available in Oracle.

1. NVL Function :

NVL stands for Null Value. This Function is used to replace the null values with any other desired value.

One important thing to remember with NVL Function is that , the value replacing the Null should be of the same datatype as that of column containing the null.

Syntax :

NVL (<COLUMN NAME> , <VALUE>)

It contains two parameters. First Parameter is the column name containing the null values and Second parameter is the value with which we want to replace the null values. In case the column contains the null values , then NVl function replaces the null value with the value given in second parameter. But if the column contains any other value then NVL functions returns the column value itself.

Example : 

Ques : Replace all  the Null values of COMM Column with 100 and return the output with name COMMISION.

Soln : SELECT  ENAME, EMPNO ,SAL, NVL(COMM, 100) AS COMMISION FROM EMP1 ;

As shown below , NVL Function will replace all the null values of COMM column with 100 and give the output under the name COMMISION which we have given as Alias.

2. NVL2  Function :

NVL2 function is also used to replace the null values with the desired values like NVL. But the main difference is , three parameters are passed to NVL2 function instead of two. Moreover , unlike NVL which has a limitation with the datatype. NVL2 can be used to replace null with any value irrespective of the datatype of the column containing the null.

Syntax :  

NVL 2( <COLUMN NAME> , <VAlUE1>, <VALUE2> );

If Column value is null, it replaces it with <VALUE2>. If Column value is not null , then it is replaced with <VALUE1>.

Example :

Ques : In the COMM Column , display Yes if the employee is getting Commission and No if the employee is not getting any commission. The output column name should be displayed as COMMISSION (Alias)

Soln : SELECT  ENAME, EMPNO , SAL,NVL2(COMM, ‘YES’,’NO’)  AS COMMISSION FROM EMP1 ;

3. Coalesce Function :

Coalesce function is used to return first not null value after comparing all the columns for each row. One important point to note is that all the columns under comparison should be of the same datatype.

Syntax:

COALESCE ( <COLUMN1> , <COLUMN2> ,…. );

It can accept two or more than two columns as parameters.

Example :

Ques : Compare SAL and COMM Column and display the first Not null value among both the columns. The output column should be displayed as RESULT ( Alias)

Soln : SELECT EMPNO , ENAME, SAL, COALESCE( SAL , COMM ) AS RESULT FROM EMP1;

Lets evaluate first row. Among the SAl and COMM columns , for first row SAL Column has first not null value             (20,000). Similarly in second row also , but in third row COMM has first Not Null value ( 1500) and so on.