The first time I saw the term COALESCE in a SQL manual, I thought it was a scientific function for some complex calculation. However, later I found that it is a straightforward and handy function. In this article, I will show several examples to explain how to use the COALESCE function.
THE REASON BEHIND COALESCE: THE NULL VALUE
Before go deep with COALESCE function, the reader needs to understand how the NULL value works in some expressions. A NULL value in a table column means that there is no value for this column, in other words: NULL value indicates the absence of value.
Then, when we have a math, date, string or boolean expression involving a NULL value, the resulting value of the complete expression is also NULL. Why ? because if we have an expression where one component is not known, the only thing we can say about the result is: unknown.
For example, the following operations return:
10 + NULL returns NULL
10 * NULL returns NULL
‘world’ || NULL returns NULL
NULL + interval ‘1 hour’ returns NULL
TRUE and NULL returns NULL
TRUE or NULL returns TRUE (Interesting!)
AN INTERNET PROVIDER SERVICES TABLE
The queries in this article we will use a sample table from an internet service provider company. The table will have all the services contracted by the customers.
A HAPPY CUSTOMER HAVING TO PAY NOTHING
The following query should return the monthly value to pay for each customer.
SELECT client_num, monthly_value AS “service_amount”, monthly_value + other_charges AS "total to pay" FROM services;
However, if we take a look at the query output in the next image, we will see that the first record doesn’t have a value for “total to paid” column. The reason behind the missing amount is the NULL in the other_charges column for this record. We already know that adding a NULL result in NULL too.
Then, in order to fix this issue, we will use the COALESCE function, which returns the first parameter with a not NULL value. Let’s see the fixed query:
SELECT client_num, monthly_value AS “service_amount”, monthly_value + COALESCE(other_charges,0) AS "total to pay" FROM services;
To understand in detail how the function COALESCE works, let’s focus on the column “total to pay”. For the first record The COALESCE function returns the second parameter (value 0), then we have a 0 instead of a NULL value as in the previous example. For the rest of the records, the SQL works similar to the previous example, and the COALESCE function returns the first parameter: other_charges.
A CUSTOMER WITH AN UNKNOWN SERVICE NAME
In the next example, we will see what happen with strings when a NULL value is involved in a string expression. Let’s suppose we need a list of the customer names, and the service contracted by each one. As a way to clear the service name, we will concatenate the service name with the service level with the following query:
SELECT client_num, service_name || ' - ' || service_level AS 'service contracted', FROM services;
The next image shows the result of the query.
Why the fourth record has a NULL for the “service contracted” field ?. You can check that the service name is “Internet 20Gb”. The problem here is the NULL value in the service_level column. We already mention that any string value concatenated with NULL returns NULL. This is what is happening here.
Let’s see how to fix with the COALESCE function in the next query:
SELECT client_num, service_name || ' - ' || COALESCE(service_level,'') AS 'service contracted', FROM services;
Not all database managers have the COALESCE function. However, all databases have similar functions to work with NULL values. Functions like IFNULL(), NVL() and ISNULL() among others allows to detect, replace or transform NULL values. Try it yourself!