How to Use the COALESCE() Function in SQL (2023)

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.


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.

(Video) Coalesce function in sql server Part 16

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.

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

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 familyFROM stock

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

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:

(Video) Use of Coalesce and ISNULL in SQL Server | SQL Server Interview Question

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

It returns:

Hello, how are you Peter ?

However, if we use a NULL …

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

… we get:


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_brandFROM stock

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

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_subcategoryFROM 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:

(Video) SQL tutorial 74: SQL COALESCE Function In oracle Database Manish Sharma

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 thresholdFROM 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

pork ribs400130130

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 stockGROUP 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:

foodmeatcow meat570
foodmeatpork meat400
foodvegetablesleaf vegetables280

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 …

(Video) SQL Server Interview Question | Coalesce Function in SQL | SQL Coalesce function


… 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 stockGROUP BY ROLLUP(family, category, subcategory)ORDER BY family, category, subcategory

The result is:

foodmeatCow meat570
foodmeatPork meat400
foodmeatAll Sub-Categories970
foodvegetablesLeaf vegetables280
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!



How do you use the coalesce function in SQL? ›

The SQL COALESCE function can be syntactically represented using the CASE expression. For example, as we know, the Coalesce function returns the first non-NULL values. SELECT COALESCE (expression1, expression2, expression3) FROM TABLENAME; The above Coalesce SQL statement can be rewritten using the CASE statement.

What does the Coalesce () function do? ›

The COALESCE function returns the first non-NULL value from a series of expressions. The expressions are evaluated in the order in which they are specified, and the result of the function is the first value that is not null. The result of the COALESCE function returns NULL only if all the arguments are null.

How many arguments can coalesce take? ›

The COALESCE scalar function takes two or more arguments and returns the first nonnull argument, starting from the left in the expression list. The COALESCE function returns the value of one of the expressions in the list.

How do you use coalesce to replace NULL values? ›

The COALESCE() function returns the first non-null arguments from the given list of expressions. If the expression is null, the function returns a null. The function can also replace null entries with a given default value. The default value is returned if the expression passed into the COALESCE() function is null.

What is coalesce () function in SQL Server? ›

Definition and Usage. The COALESCE() function returns the first non-null value in a list.

How do I coalesce all columns in SQL? ›

The coalesce in MySQL can be used to return first not null value. If there are multiple columns, and all columns have NULL value then it returns NULL otherwise it will return first not null value. The syntax is as follows. SELECT COALESCE(yourColumnName1,yourColumnName2,yourColumnName3,.......


2. PostgreSQL: Coalesce | Course | 2019
(Marsha Baddeley)
4. Oracle COALESCE Function
(Database Star)
5. Coalesce In SQL Server | COALESCE (Transact-SQL)
(develop with Ahmad Mohey)
6. The use of Coalesce function in SQL | The example of Coalesce function in SAP HANA SQL
Top Articles
Latest Posts
Article information

Author: Velia Krajcik

Last Updated: 03/06/2023

Views: 5800

Rating: 4.3 / 5 (74 voted)

Reviews: 81% of readers found this page helpful

Author information

Name: Velia Krajcik

Birthday: 1996-07-27

Address: 520 Balistreri Mount, South Armand, OR 60528

Phone: +466880739437

Job: Future Retail Associate

Hobby: Polo, Scouting, Worldbuilding, Cosplaying, Photography, Rowing, Nordic skating

Introduction: My name is Velia Krajcik, I am a handsome, clean, lucky, gleaming, magnificent, proud, glorious person who loves writing and wants to share my knowledge and understanding with you.