Apache Hive – ISNULL vs NVL

Unlike in relational database systems like SQL Server, the ISNULL function works in a different way in Apache Hive. In SQL Server, ISNULL(col1, 0) returns 0 if the col1 value is null. Same implementation will return the below error in Apache Hive.

This is because the Apache Hive ISNULL function is a conditional assertion that returns TRUE or FALSE.

If you are looking for a ISNULL equivalent function in Apache Hive, then use NVL.

Below are the descriptions of the functions.

ISNULL:
Returns TRUE if the provided column value is NULL, false otherwise.

NVL:
Returns column value if value is not null else returns the value provided by the user.

This slideshow requires JavaScript.