Many of the functions that are present in Apache Hive and Impala are close to the functions of conventional frameworks such as Oracle SQL*Plus and MySQL. Functions that assist in treating null values like NVL and NVL2 behaves the same way they did in Oracle.
Syntax: NVL(type a, type ifNull)
Purpose: Alias for the ISNULL() function. Returns the first argument if the first argument is not NULL. Returns the second argument if the first argument is NULL.
This is equivalent to the NVL() function in Oracle Database or IFNULL() in MySQL. This returns the same as the first argument value. This function was added in Impala 1.1 version.
Syntax: NVL2(type a, type ifNotNull, type ifNull)
Purpose: Returns the second argument, ifNotNull, if the first argument is not NULL. Returns the third argument, ifNull, if the first argument is NULL.
This is equivalent to the NVL2() function in Oracle Database. This function returns same as the first argument value. This function has been added in CDH 5.12.0 / Impala 2.9.0
Big Data and SQL 