NULL FUNCTIONS

This is a summary of few sql functions available for handling null values in ORACLE:

• NVL
• DECODE
• NVL2
• COALESCE
• NULLIF

All of the examples below require the following EMPLOYEE1 table:

ID	NAME	START_DATE	END_DATE	SALARY	CITY
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	BOMBAY
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	KOLKATA
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	CHENNAI
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	DELHI
5	E	01/01/2016 00:00:00	09/06/2016 00:00:00	12,000	NOIDA
6	F	01/01/2016 00:00:00	08/06/2016 00:00:00	15,000	BHUBANESWAR
7	G	01/01/2016 00:00:00	07/06/2016 00:00:00	11,000	PUNE
8	A	01/01/2016 00:00:00	06/06/2016 00:00:00	10,000	GOA
8	B	01/01/2016 00:00:00	06/06/2016 00:00:00	10,000	DELHI
9	H	01/01/2016 00:00:00	07/06/2016 00:00:00		HYDERABAD
10	I	01/01/2016 00:00:00	07/06/2016 00:00:00		KILKATA

NVL

The NVL function allows you to replace null values with a default value.  It requires two parameter values first value is column name and second is any default value. If the value in the first parameter is null, the function returns the value in the second parameter. If the first parameter is any value other than null, it is returned unchanged.

Syntax

The syntax for the NVL function in Oracle/PLSQL is:

NVL( string1, replace_with )

We know that SALARY in the EMPLOYEE1 table contains null in last 2 rows.Here we are using the NVL function to replace the null values with ‘ZERO’.

SELECT ID, NAME, NVL(SALARY, 0) AS SALARY FROM EMPLOYEE1 ORDER BY ID;

This would produce the following result:

ID	NAME	OUTPUT_SALARY
1	A	10,000
2	B	10,000
3	C	10,000
4	D	10,000
5	E	12,000
6	F	15,000
7	G	11,000
8	A	10,000
8	B	10,000
9	H	0
10	I	0

 

DECODE

The DECODE function is not specifically for handling null values, but it can be used in a similar way to the NVL function, as shown by the following example.

Oracle decode function is used within the Oracle database to transform data values for one value to another. 

Syntax

The syntax for the DECODE function in Oracle/PLSQL is:

DECODE( expression , search , result [, search , result]... [, default] )

Let's take a look at how the decode statement works. The Oracle decode statement was developed to allow us to transform data values at retrieval time. For example, say we have a column named SALARY in EMPLOYEE1 table, with values of 10000, 11000,12000 and 15000. When we run SQL queries, we want to transform these values into 10, 11, 12 and 15. Here is how we do this with the decode function:

SELECT ID, NAME,  DECODE (

SALARY,

'10000','10',

'11000','11','12000','12','15000','15',

'UNKNOWN'

) AS SALARY

from

DSOCIQA.EMPLOYEE1;

Note that Oracle decode starts by specifying the column name, followed by set of matched-pairs of transformation values. At the end of the decode statement we find a default value. The default value tells decode what to display if a column values is not in the paired list.

This would produce the following result:

ID	NAME	SALARY
1	A	10
2	B	10
3	C	10
4	D	10
5	E	12
6	F	15
7	G	11
8	A	10
8	B	10
9	H	UNKNOWN
10	I	UNKNOWN

NVL2

The NVL2 function accepts three parameters. If the first parameter value is not null it returns the value in the second parameter. If the first parameter value is null, it returns the third parameter.

Syntax

The syntax for the NVL2 function in Oracle/PLSQL is:

NVL2( string1, value_if_not_null, value_if_null )

Example:

SELECT ID, NVL2(SALARY, ‘APPLICABLE’, ‘NOT APPLICABLE’) AS SALARY FROM EMPLOYEE1;

This would produce the following result:

ID	SALARY
1	APPLICABLE
2	APPLICABLE
3	APPLICABLE
4	APPLICABLE
5	APPLICABLE
6	APPLICABLE
7	APPLICABLE
8	APPLICABLE
8	APPLICABLE
9	NOT_APPLICABLE
10	NOT_APPLICABLE

COALESCE

The COALESCE function was introduced in Oracle 9i. It accepts two or more parameters and returns the first non-null value in a list. If all parameters contain null values, it returns null.

Syntax

The syntax for the COALESCE function in Oracle/PLSQL is:

COALESCE( expr1, expr2, ... expr_n )

Example:

SELECT ID, COALESCE(ID, SALARY) AS output FROM EMPLOYEE1 ORDER BY ID;

This would produce the following result:

ID	OUTPUT
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
8	8

NULLIF

The NULLIF function was introduced in Oracle 9i. It accepts two parameters and returns null if both parameters are equal. If they are not equal, the first parameter value is returned.

Syntax

The syntax for the NULLIF function in Oracle/PLSQL is:

NULLIF( expr1, expr2 )

Example:

SELECT ID, NULLIF(ID, SALARY) AS output FROM EMPLOYEE1 ORDER BY id;

This would produce the following result:

ID	OUTPUT
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
8	8