The SQL Coalesce Function: Handling Null Values

You may already know how to return null values in SQL. Now, we’re going to learn how to do the opposite. Though the SQL COALESCE function may seem complex, it’s actually very straightforward and useful. Let’s look at several examples of how the SQL COALESCE function can be used to work with NULL values in SQL.

The Need for Coalesce in SQL

Before we dive into the SQL COALESCE function in detail, you should understand how NULL values behave in expressions. Simply put, a value of NULL indicates that there is currently no value for a particular entry in a table column.

Any expression involving numbers, dates, strings, or booleans and a value of NULL will itself return NULL. This is because NULL represents an unknown value. Since any expression involving an unknown value cannot be fully determined, it must naturally return NULL!

Take a look at the following example expressions:

	2  +  NULL 			returns		 NULL

	2  *  NULL 			returns		 NULL

	'hello'  ||  NULL 		returns		 NULL

	NULL  +  interval '1 day' 	returns		 NULL

	TRUE and NULL	                returns		 NULL

Working with Transit Fines

To explain how the SQL COALESCE function works, we’ll use the guided example of transit fines. The table below stores the ID of the driver, the fine they received, the date and time when the fine was imposed, the level/degree of the offense the driver committed, and the amount of previous unpaid fines the driver has accrued.

Note that the column offense_level can have values of ‘Band A’, ‘Band B’, or ‘Band C’. An offense level of Band B indicates that the driver will have to pay 1.5 times the fine amount if they do not pay the original fine within 30 days. Band C doubles the fine amount after 30 days. An offense level of Band A does not affect the fine amount if it is not paid within 30 days.

The Mystery of the Disappearing Fine

First, we’ll consider fines within the 30-day payment period. We’ll look at extra fees that can be charged to the driver after the 30-day period passes in a later example.

In theory, the following query should return the value of each fine and the total value of any fines the driver must pay (including any previous unpaid fines).

SELECT 	driver_id,
		fine_value 			as "fine_amount", 
		fine_value + unpaid_fines 	as "total_due" 
FROM 	fines;

However, if we take a look at the query output in the next table, we’ll notice that the first record doesn’t have any value for the total_due column! What went wrong?

The reason this happens is quite simple. Since the driver with ID 16877165 had no unpaid fines, the value stored under the column unpaid_fines for this driver was, by default, treated as NULL.

As we mentioned before, any expression involving a value of NULL will return NULL. Thus, the addition of fine_value to unpaid_fines inadvertently produced NULL, which translated over to an empty cell in the result table.

SQL Coalesce to the Rescue

To fix this issue, we’ll use the SQL COALESCE function, which returns the first non-NULL value from a list of arguments that we give it. The function can take as many arguments as needed. In this case, though, we’ll pass in just two arguments to the function call: unpaid_fines and a value of 0. Here’s the query:

SELECT 	driver_id,
		fine_value 						as "fine_amount", 
		fine_value + COALESCE(unpaid_fines,0)	as "total_due" 
FROM 	fines;

Let’s now explain how and why this works.

When COALESCE(unpaid_fines, 0) is evaluated, if unpaid_fines happens to be NULL for a particular record in the table, SQL will skip that value and move on to the next argument we passed in to the function. Recall that in this context, a value of NULL indicates that a driver has no unpaid fines.

In this case, the next value we provided after unpaid_fines is 0. Since this is the first non-NULL value we encountered when moving from left to right for the first driver, the function call will simply return 0. This is precisely what we want—if a driver doesn’t have any unpaid fines, then we simply need to treat their unpaid fines as zero!

However, if a driver did have some unpaid fines, then the value stored under unpaid_fines for that particular record will not be NULL. In that case, the call to COALESCE(unpaid_fines, 0) will return the value stored under unpaid_fines, allowing us to add a nonzero unpaid amount to the driver’s current fine to get the total amount due.

Missed Payment Deadlines: Extra Charge Incurred

Now, recall we said that if the fine is not paid within 30 days, then the amount you must pay will be multiplied by the offense rate. In the upcoming query using the SQL COALESCE function, we’ll take the offense_rate into account in our calculations. Take a look:

SELECT driver_id,
	     fine_value as "fine_amount", 
				COALESCE(unpaid_fines,0) as "unpaid_fines",
	     fine_value + COALESCE(unpaid_fines,0)	as "total_due" ,
	     fine_value * ofense_rate + COALESCE(unpaid_fines,0)	as "total due_after_30_days" 
FROM  fines;

This time around, we got an empty value in the fourth row under the total_due_after_30_days column. Hopefully, you’re able to determine what we need to add to our query to make it work properly.

Just like before, we need to make a call to COALESCE. This time, we need to use SQL COALESCE when calculating the fine multiplier. This will take into account the possibility that the driver’s offense was not specified, in which case the fine should be treated as if the offense had been classified as Band A (no penalty).

Naturally, we’ll pass in a value of 1 as the second argument. Thus, if the value under offense_level happens to be NULL for a particular driver, the fine value will simply remain the same (it will be multiplied by one).

Here’s the updated query:

SELECT driver_id,
	     fine_value 	as "fine_amount", 
                COALESCE(unpaid_fines,0)	as "upaid_fines",
	     fine_value + COALESCE(unpaid_fines,0)	as "total_due" ,
	     fine_value * COALESCE(offense_level, 1) + COALESCE(unpaid_fines,0)	as "total_due_after_30_days" 
FROM  fines;

For our last example, we’ll do some string concatenation to observe what happens when one of the columns we’re working with contains a value of NULL. Here’s the query:

SELECT	driver_id,
		reason ||' - '|| offense_level  			as “reason_and_severity”,
		fine_value + COALESCE(unpaid_fines,0)	as "total_due" 
FROM		fines

If we take a look at the fourth record, we’ll again notice an empty value, this time under the reason_and_severity column. Once again, this is because we attempted to evaluate an expression involving a NULL value.

If we instead use the SQL COALESCE function, we can specify a value to fall back on if we encounter NULL. For our purposes, we’ll pass in the string ‘No Band’ as that default. Here’s the updated query, which runs as expected:

SELECT	driver_id,
		reason ||' - '|| COALESCE(offense_level, 'No Band')  	as "reason_and_severity",
		fine_value + COALESCE(unpaid_fines,0)		as "total_due" 
FROM		fines

Final Thoughts on SQL COALESCE Function

Not all database managers have access to the SQL COALESCE function, but all databases offer similar functions for working with NULL values. Functions like IFNULL(), NVL(), and ISNULL(), among others, allow you to detect, replace, or transform NULL values.

If you’d like to learn more about SQL functions, go ahead and check out our LearnSQL.com Standard SQL Functions course, where we cover many essential SQL operations and functions.