Using the COALESCE() expression

The COALESCE() expression allows us to specify a list of values and return the first non-null value in the list. To produce meaningful data, we start by analyzing the dataset, exploring the data values, and transforming values as required. It is very important that we understand how to handle NULL values in datasets, as they can potentially affect the results of our queries, our calculations, and ultimately, the reports we are presenting to the end users. COALESCE() was primarily designed to help us with NULL value handling. We’ll walk through a few examples in this section and talk about the differences between COALESCE() and CASE.

How to use COALESCE()

Let’s go through a few simple examples to get an understanding of how it works:

SELECT COALESCE(NULL, NULL, 8,12,13) AS [Output]

The result is as follows:

Figure 4.9 – Result of the query

Just as we expected, the output value of the query...