An alternative to ISNULL() and NVL() functions in Hive

The NVL() function enables you to substitute null for a more relevant alternative in the query results. This function accepts two arguments. If the first argument is null, then it returns the second argument. If the first argument is not null, it returns the first one and will ignore the second argument. This function is available in Oracle SQL*Plus, but not in MySQL, SQL Server, and Hive.

However, as an alternative, ISNULL() and COALESCE() functions can be used to achieve the same result in MySQL and SQL Server. Since ISNULL() functions in a different way (compared to SQL Server) in Hive, COALESCE() function is the only option to achieve the desired output.

The difference between NVL() and COALESCE() is that COALESCE() will return the first non-null value from the list of expressions while NVL() only takes two parameters and returns the first if it is not null, otherwise, returns the second.

Let’s see what these three functions will do.

Oracle:
SELECT first_name + middle_name + last_name As EmpName
FROM Employees;

Result:

Employees
—————————
Robert Finn Hill
Bruce M. Wills
Maria Andrew Brown
NULL

The last row is null because there is no middle name of the employee. NULL is returned when concatenated the null with first-name and last-name. There we use NVL() function. 

SELECT first_name + NVL(middle_name, ‘ ‘) + last_name As EmpName
FROM Employees;

Result:

Employees
—————————-
Robert Finn Hill
Bruce M. Wills
Maria Andrew Brown
Ashley Miller

SQL Server:

SELECT first_name + ISNULL(middle_name,”) + last_name As EmpName
FROM Employees;

SELECT first_name + COALESCE(middle_name,”) + last_name As EmpName
FROM Employees;

Hive:

SELECT first_name + COALESCE(middle_name,”) + last_name As EmpName
FROM Employees;