Back to articles list December 7, 2017 - 7 minutes read The SQL Coalesce Function: Handling Null Values Ignacio L. Bisso Ignacio is a database consultant from Buenos Aires, Argentina. He’s worked for 15 years as a database consultant for IT companies like Informix and IBM. These days, he teaches databases at Sarmiento University and works as a PostgreSQL independent SQL consultant. A proud father of four kids with 54 years in his backpack, Ignacio plays soccer every Saturday afternoon, enjoying every match as if it’s his last one. Tags: AVG date arithmetic SQL basics sql timestamps 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. 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. 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; 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 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; 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 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; 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 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 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 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. Tags: AVG date arithmetic SQL basics sql timestamps You may also like How to Remove Junk Characters in SQL Sometimes, we’ll find unwanted characters inside our string data. How to remove junk characters in SQL? Our article explains it! Read more 15 Best SQL Articles for Beginners Published in 2017 If you're looking for articles on SQL for beginners, take a look at my comprehensive list of best SQL articles from 2017! Read more How to Tackle SQL NULLs: COALESCE function Let's introduce the COALESCE postgresql function. It's inevitable that some data in the database has no value. Find out what then with MySQL. Read more NULL Values and the GROUP BY Clause We've already covered how to use the GROUP BY clause but how does SQL's GROUP BY clause work when NULL values are involved? Find out! Read more Understanding the Use of NULL in SQL Three-Valued Logic NULLs are necessary in databases, learning to use them is fundamental to SQL success. However, NULLs should be handled with care – see how! Read more Useful SQL Patterns: Matching Nulls by Masking Nulls Today, in the first post of the SQL patterns series, we will consider the match by null pattern. It’ll help you deal with tables containing null values. Read more A Non-Technical Introduction to Learning SQL on Your Lunch Break Do you think learning SQL will help you in your career? You are right. SQL is one of the easiest computer languages to learn. These days many non-IT employees have SQL skills and use them to extend their professional capacity. Moreover, more and more companies are encouraging their employees in non-IT areas (like sales, advertising, and finances) to learn and use SQL. Read more SQL Basics Cheat Sheet This 2-page SQL Basics Cheat Sheet will be a great value for beginners as well as for professionals. Download it in PDF or PNG format. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.