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
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
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.
|pork ribs||NULL||pork meat||meat||food||Kilos||400||130|
This table stores product data records for a marketplace and includes the columns
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 ribs||pork meat||meat||food|
|lettuce||Leaf vegetables||No Category||food|
|hamburger||No Subcategory||No Category||No 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
|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:
|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
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
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
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
ROLLUP clause assumes a hierarchy among the columns
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 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 …
… are the result of the execution of
GROUP BY family, category. This is the reason for having a NULL under the
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:
|food||All Categories||All Subcategories||1980|
|All Families||All Categories||All Subcategories||1980|
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
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,.......