COALESCE function in Apache Impala

The NULL values are substituted with the user-defined value during the expression evaluation process using the NVL(), COALESCE(), and ISNULL() functions. The COALESCE function evaluates the arguments in the specified order and always returns the first non-null value from the argument list. The main difference between NVL() and COALESCE() is that COALESCE() returns the first non-null value from the list of expressions, whereas NVL() only takes two parameters and returns the first if it is not null and the second otherwise.

Let’s look at how we can use the COALESCE() function in our daily operations.

Assume there is an emp table with columns for the employee’s first-name, middle-name, and last-name. Note that a few persons have no middlename. The following query will be used in most cases for generating a report that combines the three part names into one.

SELECT first_name + middle_name + last_name As EmpName
FROM Employees;

Result:

EmployeeFullName
Robert Finn Hill
Bruce M. Wills
Maria Andrew Brown
NULL

The last row is empty since the employee has no middle name. When the null is concatenated with the first-name and last-name, NULL is returned. To avoid NULL, we use the COALESCE, NVL(), or ISNULL functions to give a substitute value.

The query will be:
SELECT first_name + COALESCE(middle_name,”) + last_name As EmpName
FROM Employees;

Result:
EmployeeFullName
Robert Finn Hill
Bruce M. Wills
Maria Andrew Brown
Ashley Miller

Hope you find this article helpful.

Please subscribe for more interesting updates.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s