I found a use for the SQL COALESCE() Function

I’ve heard about the COALESCE() function, but never had a use for it. As defined by W3 Schools:

The COALESCE() function returns the first non-null value in a list.

I recently had a situation where it was needed.

Why Use COALESCE() ?

Let’s assume I have the following dataset of “Points Per Employee,” which comes from an external source that is imported into my database:

Original dataset from an external source

My application allows users to override the points with a custom value. Let’s suppose some employees are manually given a different value:

New values for certain employees, in a separate table

To get an employee’s value, we’ll do a LEFT JOIN

We can’t do an INNER JOIN because not all employees are in the override table.

SELECT 
  E.employee_id, E.employee_name,
  E.points AS points_original, O.points AS points_new
FROM
  Employees E
LEFT JOIN 
  Override O ON
      O.employee_id = E.employee_id

You’ll get this:

Combined values showing original and new values — let’s pick ONE to show though

Where COALESCE() comes in

We only want one value per employee. If there’s a value in the “new” field, defined from a manual override, then we want that. Otherwise we’ll default to the value generated from the external source:

SELECT 
  E.employee_id, E.employee_name,
  COALESCE(O.points, E.points) AS points_final
FROM
  Employees E
LEFT JOIN 
  Override O ON
      O.employee_id = E.employee_id

SQL COALESCE() example

You can see that those with a manual override (Parker, Stout, Vektor) have the new value, whereas everyone else retained the original value.

One Last Thing: Checking for NULLs

If there wasn’t a value at all, you can check for a NULL.

Add another parameter with the default value to return, or use the function ISNULL() :

COALESCE(O.points, E.points , 'N/A') AS final_points

or

ISNULL( COALESCE(O.points, E.points) , 'N/A' ) AS final_points