How to Use the COALESCE() Function in SQL

SQL users are often faced with NULL values in their queries and need to process them properly. The COALESCE() function helps handle NULL values. Read this article to learn how to use COALESCE() in your queries.

SQL tables store data in records, and records are composed of fields. There can be situations where we don’t know the value for a specific field. For example, when we don’t know the marital status of a person, SQL allows us to assign a NULL for this field. NULL doesn’t mean that the person doesn’t have a marital_status; it only means we don’t know that value.

SQL uses NULL values to represent the absence of value. However, NULL can be tricky to handle. That’s why SQL has included the COALESCE() function, which is what we will talk about in this article.

Before starting on the technical details of NULL and COALESCE(), I recommend our interactive course on Standard SQL Functions. It contains a comprehensive review of NULL values, NULL-related functions, and other common functions in SQL.

What Does COALESCE() Do?

In SQL databases, any data type admits NULL as a valid value; that is, any column can have a NULL value, regardless of what data type it is. (Obviously, some columns will be mandatory (non-nullable), but this is set by the database designer, not the data type itself.) Let’s show a simple example using the table persons.

We can use the COALESCE() function to replace the NULL value with a simple text:

SELECT 	
  first_name, 
  last_name, 
  COALESCE(marital_status, 'Unknown')
FROM persons

The COALESCE() function is used to return the value 'Unknown' only when marital_status is NULL. When marital_status is not NULL, COALESCE() returns the value of the column marital_status. In other words, COALESCE() returns the first non-NULL argument.

Example SQL Queries Using COALESCE() Function

Example Data

For the rest of the article, we will demonstrate the COALESCE() function using the table stock, which is shown below.

This table stores product data records for a marketplace and includes the columns product, brand, subcategory, category, family, units, quantity (the current stock of this product), and minimum (the threshold when the market needs to order this product from their suppliers).

You’ll note that some of the products have a subcategory, but others do not. For example, the product “pork ribs” belongs to the subcategory “pork meat” in the category “meat” and the family “food”. The product “Tomatoes” belongs to the category “vegetables” and the family “food”; it does not have a subcategory, so there is a NULL in this field.

Now, let’s see how to use the COALESCE() function in some realistic examples.

Example 1: Replace NULL with a Label

We want to show all the products with their subcategory, category, and family. However, there are some products with a NULL in their category or subcategory. For these products, we want to display a text: 'No Category' or 'No Subcategory'. Here’s the query we’d use:

SELECT 
  product, 
  COALESCE(subcategory, 'No Subcategory') AS subcategory,
  COALESCE(category, 'No Category') AS category,
  COALESCE(family, 'No Family') AS family
FROM stock

We’re using the COALESCE() function to replace NULL values with a text. You can see the result below:

Example 2: Concatenating NULL and Strings

A frequent SQL issue related with NULL values is string concatenation. Many operations involving NULL values return a NULL as a result. If we want to concatenate two strings and one of them is NULL, the concatenation result will be NULL.

To avoid that, we can use the COALESCE() function to return an empty string (or a space) instead of a NULL. For example, suppose we want a list of the product names with the brand name. We can write the following query:

SELECT 
    product || ', brand: ' || COALESCE(brand, '--') AS product_brand
FROM stock

When a brand is NULL, we will put a '--' instead of a NULL. Notice the result:

Example 3: COALESCE with Multiple Arguments

You can use the COALESCE() function with more than two arguments. Suppose we want a report that lists products and their subcategories. If the subcategory is NULL, we want to replace the subcategory with the category. And if both subcategory and category are NULL, we want to replace them with the family of the product. Let’s see the SQL query:

SELECT 
   product ||' - '||
   COALESCE(subcategory, category, family, 'no product description')
   AS product_and_subcategory
FROM stock

We are using the COALESCE() function with four arguments; the first non-NULL argument will be returned, as we can see in the result below:

For more details on this, I suggest the article How to Tackle SQL NULLs.

Example 4: Replace NULL with a Calculated Value

The COALESCE() function can also be used to compute or estimate a value when this value is not present. For example, every product has a threshold (represented by the column minimum) that requires a new order to the supplier. However, some records could have a NULL value in the minimum column; in this case, we can define that the threshold will be 50% of the column quantity. The query to compute the estimation of the threshold is:

SELECT 
  product,
  quantity,
  minimum,
  COALESCE(minimum, quantity * 0.5) AS threshold
FROM stock

The COALESCE() function here returns minimum when the value minimum is not NULL. If minimum is NULL, then COALESCE() will return quantity * 0.5:

Example 5: COALESCE with ROLLUP

In the next example, we will use the ROLLUP clause (an extension of GROUP BY) to obtain the total quantity of products we have for each subcategory, including a subtotal of products for each category and family. Let’s see the query:

SELECT 
 family,
 category,
 subcategory,
 SUM(quantity) as quantity_in_stock 
FROM stock
GROUP BY ROLLUP(family, category, subcategory)
ORDER BY family, category, subcategory

The ROLLUP clause assumes a hierarchy among the columns family, category, and subcategory. Thus, it generates all the grouping sets that make sense considering the hierarchy: GROUP BY family, GROUP BY family, category and GROUP BY family, category, subcategory. This is the reason why ROLLUP is often used to generate subtotals and grand totals for reports.

Let’s see the results below:

You can see some NULL in the previous result. Each NULL means that this column was not present in the GROUP BY for the calculation of the quantity in stock. For example, these rows …

… are the result of the execution of GROUP BY family, category. This is the reason for having a NULL under the subcategory column.

In the next query, we will use the COALESCE() function to improve the report’s readability. We will replace those NULL values with a text clarifying the reason for the NULL:

SELECT 
 COALESCE(family,'All Families') AS family,
 COALESCE(category,'All Categories') AS category,
 COALESCE(subcategory,'All Subcategories') AS subcategory,
 SUM(quantity) as quantity_in_stock 
FROM stock
GROUP BY ROLLUP(family, category, subcategory)
ORDER BY family, category, subcategory

The result is:

In the previous result, we can observe how the NULL values are replaced by texts and the rows of the reports with the texts starting with 'All' show a subtotal in quantity_in_stock.

I would like to suggest the article The SQL COALESCE Function: Handling NULL Values if you want to learn more about this.

Where to Learn More

In this article, we demonstrated several ways to use the SQL COALESCE() function. We covered how to use COALESCE() to replace NULL values, how to compute an alternative value, and how to combine COALESCE() with the ROLLUP clause, among other examples.

If you want to practice SQL functions, including COALESCE, try out our interactive Standard SQL Functions course, where you can learn and practice numeric, text, date, and NULL-handling SQL functions. Happy learning!