How to use SQL COALESCE() to determine non-NULL values

The SQL COALESCE() function is used to determine the first value within a list that is not NULL. The function is therefore a shortened version of a corresponding CASE statement.

What is SQL COALESCE()?

In SQL, the keyword NULL represents data that has no value. This situation arises, for example, when a specific value in a table is unknown and the corresponding field is left empty. This doesn’t necessarily mean the field has no value, but rather that the value is unknown. While NULL values aren’t inherently problematic, they can cause confusion if they accumulate in a database. The SQL COALESCE() function helps by outputting the first value in a list that is not NULL.

Syntax and requirements

The syntax of SQL COALESCE() is very simple:

COALESCE(value1, value2, ..., n)

You need at least two arguments. If all arguments have the value NULL, NULL is also output at the end.

An example of how it works

You can quickly recognise the basic functionality using a simple example. In the following code, we enter various arguments and check them using the SQL command SELECT.

SELECT COALESCE(NULL, NULL, NULL, 17, 49, NULL, 13, 15, 14, 15);

In this case, the output is ‘17’, as this is the first value that is not ZERO.

The function in combination

The function becomes even more helpful when used in combination with other actions. Let’s imagine a table called ‘Employees’. This table contains the columns ‘Name’, ‘Place of residence’, and ‘Date of birth’. It looks like this:

Two of the entries are incomplete. Although Sebastian Smith has a place of residence and Sarah Davis has a date of birth, these are not known to us at this time. The SQL COALESCE() function can help make this clear. Here is the appropriate code:

SELECT Name,
COALESCE (Place of residence, 'PLEASE INQUIRE') AS Place of residence,
COALESCE (Date of birth 'PLEASE INQUIRE') AS Date of birth
FROM Employee;

The corresponding output is:

Similar functions to SQL COALESCE()

SQL COALESCE() overlaps with a CASE statement and is simply a shortened version of this query option. The CASE code would look like this:

CASE
WHEN value1 IS NOT NULL THEN value1 
WHEN value2 IS NOT NULL THEN value2 
WHEN ... IS NOT NULL THEN ...
ELSE n
END

The ISNULL function is also similar to SQL COALESCE(). Contrary to the function discussed, ISNULL is only evaluated once and doesn’t follow the rules of CASE.