Back to articles list Articles Cookbook
8 minutes read

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, let’s suppose we have a table with data for persons. It has the fields first_name, last_name, and marital_status. When we don’t know the marital_status for a given person, SQL allows us to assign a NULL value for this field. However the NULL value doesn’t mean that the person doesn’t have a marital_status; it only means “we don’t know that value”.

In other words, SQL uses NULL values to represent the absence of value. However, NULLs 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 suggest checking out 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.

first_namelast_namemarital_status
CharlesLeclercsingle
FernandoAlonsomarried
GeorgeGraueNULL

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

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

In the above query, 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.

Get to Know the Example Data

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

productbrandsubcategorycategoryfamilyunitsquantity_availableminimum_to_have
pork ribsNULLpork meatmeatfoodKilos400130
tomatoesMr RedNULLvegetablesfoodKilos280100
lettuceNULLLeaf vegetablesNULLfoodKilos280125
bananasBig BrasilNULLvegetablesfoodKilos450150
hamburgerMaxBurgcow meatmeatfoodBox245100
hamburgerRoyalBurgcow meatmeatfoodBox125NULL
hamburgerSuperBurgaNULLNULLNULLBox20080

This table stores product data records for a marketplace and includes the columns product, brand, subcategory, category, family, units, quantity_available (the current stock of this product), and minimum_to_have (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.

Example SQL Queries Using COALESCE() Function

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

Example 1: Use COALESCE() to 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:

productsubcategorycategoryfamily
pork ribspork meatmeatfood
tomatoesNo Subcategoryvegetablesfood
lettuceLeaf vegetablesNo Categoryfood
bananasNo Subcategoryvegetablesfood
hamburgercow meatmeatfood
hamburgercow meatmeatfood
hamburgerNo SubcategoryNo CategoryNo Family

Example 2: Use COALESCE() When 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. Here’s a simple text concatenation:

SELECT 'Hello, how are you ' || 'Peter ' || '?'  AS example

It returns:

example
Hello, how are you Peter ?

However, if we use a NULL …

SELECT 'Hello, how are you ' || null || '?'  AS example

… we get:

example
NULL

The result is NULL because every text string concatenation involving a NULL value returns a NULL value. 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:

product_brand
pork ribs, brand: --
tomatoes, brand: Mr Red
lettuce, brand: --
bananas, brand: Big Brazil
hamburger, brand: MaxBurg
hamburger, brand: RoyalBurg
hamburger, brand: SuperBurga

Example 3: Use 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:

product_and_subcategory
pork ribs - pork meat
tomatoes - vegetables
lettuce - leaf vegetables
Bananas - vegetables
hamburger - cow meat
hamburger - cow meat
hamburger - no product description

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

Example 4: Use COALESCE() to Replace NULL with a Calculated Value

The SQL 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_to_have) that requires a new order to the supplier. However, some records could have a NULL value in the minimum_to_have column; in this case, we can define that the threshold will be 50% of the column quantity_available. The query to compute the estimation of the threshold is:

SELECT product,
  quantity_available,
  minimum_to_have,
  COALESCE(minimum_to_have, quantity_available * 0.5) AS threshold
FROM   stock

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

productquantity_availableminimum_to_havethreshold
pork ribs400130130
tomatoes280NULL140
lettuce280125125
bananas450150150
hamburger245100100
hamburger125100100
hamburger2008080

Example 5: Use COALESCE() with the ROLLUP Clause

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_available) 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:

familycategorysubcategoryquantity_in_stock
foodmeatcow meat570
foodmeatpork meat400
foodmeatNULL970
foodvegetablesleaf vegetables280
foodvegetablesnon leaf vegetables730
foodvegetablesNULL1010
foodNULLNULL1980
NULLNULLNULL1980

You can see some NULLs 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 …

foodmeatNULL770
foodvegetablesNULL1010

… 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_available) as quantity_in_stock 
FROM stock
GROUP BY ROLLUP(family, category, subcategory)
ORDER BY family, category, subcategory

The result is:

familycategorysubcategoryquantity_in_stock
foodmeatCow meat570
foodmeatPork meat400
foodmeatAll Sub-Categories970
foodvegetablesLeaf vegetables280
foodvegetablesNon leaf vegetables730
foodvegetablesAll Subcategories1010
foodAll CategoriesAll Subcategories1980
All FamiliesAll CategoriesAll Subcategories1980

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.

Take Advantage of  COALESCE() To Process NULLs

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.

Before ending, I have another recommendation for you. Our free Standard SQL Functions Cheat Sheet lets you quickly find details about SQL’s built-in functions, aggregate functions, and more. I use it almost every day when working with SQL. You can also try out our Standard SQL Functions course, where you can learn and practice numeric, text, date, and NULL-handling SQL functions. Increase your skills and increase your assets!