The SQL Coalesce Function: Handling Null Values

coalesce, NULL, sql null, sql coalesce, coalesce oracle, coalesce vs isnull, coalesce db2, coalesce teradata, coalesce example, coalesce vs nvl, coalesce in hive, coalesce mysql

Though the COALESCE function may seem complex, it’s actually very straightforward and useful. In this short article, we’ll look at several examples of how the COALESCE function can be used to work with NULL values in SQL.

The Need for Coalesce

Before we dive into the 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

In this article, 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.

driver_id fine_value reason offense_level offense_rate date_and_time unpaid_fines
16877165 150.00 Speeding Band B 1.5 2011/26/26 08:03 AM  
18314338 500.00 Red Light Band C 2.5 2011/26/26 08:07 AM 5405.14
26177418 150.00 Speeding Band B 1.5 2011/26/25 11:07 AM 200.00
41681615 100.00 Stop Sign     2011/26/26 09:43 AM 0.00
41681615 100.00 Stop Sign Band A 1 2011/26/27 10:23 AM 0.00

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?

driver_id fine_amount total_due
16877165 150.00 Speeding
18314338 500.00 5905.14
26177418 150.00 350.00
41681615 100.00 100.00
41681615 100.00 100.00

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.

Coalesce to the Rescue

To fix this issue, we’ll use the 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;
driver_id fine_amount total_due
16877165 150.00 150.00
18314338 500.00 5905.14
26177418 150.00 350.00
41681615 100.00 100.00
41681615 100.00 100.00

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, 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;
driver_id fine_amount unpaid_fines total_due total_due_after_30_days
16877165 150.00 0.00 150.00 225.00
18314338 500.00 5405.14 5905.14 6405.14
26177418 150.00 200.00 350.00 425.00
41681615 100.00 0.00 100.00
41681615 100.00 0.00 100.00 100.00

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 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;
driver_id fine_amount unpaid_fines total_due total_due after 30 days
16877165 150.00 0.00 150.00 225.00
18314338 500.00 5405.14 5905.14 6405.14
26177418 150.00 200.00 350.00 425.00
41681615 100.00 0.00 100.00 100.00
41681615 100.00 0.00 100.00 100.00

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
driver_id reason_and_severity total_due
16877165 Speeding – Band B 150.00
18314338 Red Light – Band C 5905.14
26177418 Speeding – Band B 350.00
41681615 100.00
41681615 Stop Sign – Band A 100.00

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 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
driver_id reason_and_severity total_due
16877165 Speeding – Band B 150.00
18314338 Red Light – Band C 5905.14
26177418 Speeding – Band B 350.00
41681615 Stop Sign – No Band 100.00
41681615 Stop Sign – Band A 100.00

Conclusion

Not all database managers have access to the 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 Vertabelo Academy Standard SQL Functions course, where we cover many essential SQL operations and functions.

DBA @ Consorcio SIU

GET ACCESS TO EXPERT SQL CONTENT!