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 cor­res­pond­ing CASE statement.

What is SQL COALESCE()?

In SQL, the keyword NULL rep­res­ents data that has no value. This situation arises, for example, when a specific value in a table is unknown and the cor­res­pond­ing field is left empty. This doesn’t ne­ces­sar­ily mean the field has no value, but rather that the value is unknown. While NULL values aren’t in­her­ently prob­lem­at­ic, they can cause confusion if they ac­cu­mu­late in a database. The SQL COALESCE() function helps by out­put­ting the first value in a list that is not NULL.

Syntax and re­quire­ments

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 func­tion­al­ity 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 com­bin­a­tion

The function becomes even more helpful when used in com­bin­a­tion 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 in­com­plete. 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 ap­pro­pri­ate 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 cor­res­pond­ing 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.