NVL, DECODE, NVL2, COALESCE-NULL Functions in Oracle

This article will provide you a summary of the NULL functions available for handling null values in Oracle.

All examples are described with the use of following table.

CREATE TABLE TEST_NULL_VALUE (
  test_id NUMBER(4),
  value1 VARCHAR2(12),
  value2 VARCHAR2(12),
  value3 VARCHAR2(12),
  value4 VARCHAR2(12),
  value5 VARCHAR2(12)
);

INSERT INTO TEST_NULL_VALUE values (100, 'FIRST','SECOND','THIRD','FORTH','FIFTH');
INSERT INTO TEST_NULL_VALUE values (101, NULL,'SECOND','THIRD',’FORTH','FIFTH');
INSERT INTO TEST_NULL_VALUE values (102, NULL,NULL, 'THIRD', 'FORTH','FIFTH');
INSERT INTO TEST_NULL_VALUE values (103, NULL, NULL, 'THIRD', 'FORTH','FIFTH');
INSERT INTO TEST_NULL_VALUE values (104, NULL, NULL, NULL, 'FORTH','FIFTH');
COMMIT;

When we select TEST_NULL_VALUE table data we get the following result. Now we will use NULL functions to check the result of those functions.

SELECT * FROM TEST_NULL_VALUE ORDER BY TEST_ID;

   TEST_ID VALUE1       VALUE2       VALUE3       VALUE4       VALUE5
---------- ------------ ------------ ------------ ------------ ------------
       100 FIRST        SECOND       THIRD        FORTH        FIFTH
       101              SECOND       THIRD        FORTH        FIFTH
       102                           THIRD        FORTH        FIFTH
       103                           THIRD        FORTH        FIFTH
       104                                        FORTH        FIFTH

5 rows selected.

SQL>

NVL Function:

The NVL function permits you to replace all 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 then the function returns the value in the second parameter. If the first parameter is any value other than null, it returns unchanged value.
Here we are using the NVL function to replace the null values with ‘ZERO’.

SELECT TEST_ID, NVL(value1, 'ZERO') AS RESULT FROM TEST_NULL_VALUE ORDER BY TEST_ID;

   TEST_ID RESULT
---------- ------------
       100 FIRST
       101 ZERO
       102 ZERO
       103 ZERO
       104 ZERO

5 rows selected.

SQL>

Now you can see the above example where null value has been replaced with ZERO.

DECODE Function:

The DECODE function is not specifically designed for handling null values but it can be used to replace NULL values just like NVL function.

SELECT TEST_ID, DECODE(value1, NULL, 'ZERO', value1) AS RESULT FROM TEST_NULL_VALUE ORDER BY TEST_ID;

   TEST_ID RESULT
---------- ------------
       100 FIRST
       101 ZERO
       102 ZERO
       103 ZERO
       104 ZERO

5 rows selected.

SQL>

NVL2 Function:

The NVL2 function is entirely different from NVL function because it accepts three parameters.
If the first column value is not null then it returns the value of the second column. If the first column value is null then it returns the value of third column.

SELECT TEST_ID, NVL2(value1, value2, value3) AS RESULT FROM TEST_NULL_VALUE ORDER BY TEST_ID;

   TEST_ID RESULT
---------- ------------
       100 SECOND
       101 THIRD
       102 THIRD
       103 THIRD
       104

5 rows selected.

SQL>

SELECT TEST_ID, NVL2(value2, value3, value4) AS RESULT FROM TEST_NULL_VALUE ORDER BY TEST_ID;

   TEST_ID RESULT
---------- ------------
       100 THIRD
       101 THIRD
       102 FORTH
       103 FORTH
       104 FORTH

5 rows selected.

SQL>

In both the above examples columns are changed and you can see the results.

COALESCE Function:

The COALESCE function accepts two or more parameters and returns the first non null value in a list. If all parameters contain null values then it returns null.

SELECT TEST_ID, COALESCE(value1, value2, value3) AS RESULT FROM TEST_NULL_VALUE ORDER BY TEST_ID;

   TEST_ID RESULT
---------- ------------
       100 FIRST
       101 SECOND
       102 THIRD
       103 THIRD
       104

5 rows selected.

SQL>

SELECT TEST_ID, COALESCE(value2, value3, value4) AS RESULT FROM TEST_NULL_VALUE ORDER BY TEST_ID;

   TEST_ID RESULT
---------- ------------
       100 SECOND
       101 SECOND
       102 THIRD
       103 THIRD
       104 FORTH

5 rows selected.

SQL>

SELECT TEST_ID, COALESCE(value1, value2, value3, value4) AS RESULT FROM TEST_NULL_VALUE ORDER BY TEST_ID;

   TEST_ID RESULT
---------- ------------
       100 FIRST
       101 SECOND
       102 THIRD
       103 THIRD
       104 FORTH

5 rows selected.

SQL>

NOTE:

When you compare against null always, you always get result in null because NULL can’t be used with regular comparison operators like “=” or “!=”.

SELECT * FROM TEST_NULL_VALUE WHERE VALUE1 = NULL ORDER BY TEST_ID;

no rows selected

SQL>

Instead of you must use IS NULL or IS NOT NULL operators.

SELECT * FROM TEST_NULL_VALUE WHERE VALUE1 IS NULL ORDER BY TEST_ID;

   TEST_ID VALUE1       VALUE2       VALUE3       VALUE4       VALUE5
---------- ------------ ------------ ------------ ------------ ------------
       101              SECOND       THIRD        FORTH        FIFTH
       102                           THIRD        FORTH        FIFTH
       103                           THIRD        FORTH        FIFTH
       104                                        FORTH        FIFTH

4 rows selected.

SQL>