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.
| first_name | last_name | marital_status |
|---|---|---|
| Charles | Leclerc | single |
| Fernando | Alonso | married |
| George | Graue | NULL |
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.
| product | brand | subcategory | category | family | units | quantity | minimum |
|---|---|---|---|---|---|---|---|
| pork ribs | NULL | pork meat | meat | food | Kilos | 400 | 130 |
| tomatoes | Mr Red | NULL | vegetables | food | Kilos | 280 | 100 |
| lettuce | NULL | Leaf vegetables | NULL | food | Kilos | 280 | 125 |
| bananas | Big Brasil | NULL | vegetables | food | Kilos | 450 | 150 |
| hamburger | MaxBurg | cow meat | meat | food | Box | 245 | 100 |
| hamburger | RoyalBurg | cow meat | meat | food | Box | 125 | NULL |
| hamburger | SuperBurga | NULL | NULL | NULL | Box | 200 | 80 |
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 familyFROM stock |
We’re using the COALESCE() function to replace NULL values with a text. You can see the result below:
| product | subcategory | category | family |
|---|---|---|---|
| pork ribs | pork meat | meat | food |
| tomatoes | No Subcategory | vegetables | food |
| lettuce | Leaf vegetables | No Category | food |
| bananas | No Subcategory | vegetables | food |
| hamburger | cow meat | meat | food |
| hamburger | cow meat | meat | food |
| hamburger | No Subcategory | No Category | No Family |
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_brandFROM 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: 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:
| 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: 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 thresholdFROM 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:
| product | quantity | minimum | threshold |
|---|---|---|---|
| pork ribs | 400 | 130 | 130 |
| tomatoes | 280 | NULL | 140 |
| lettuce | 280 | 125 | 125 |
| bananas | 450 | 150 | 150 |
| hamburger | 245 | 100 | 100 |
| hamburger | 125 | 100 | 100 |
| hamburger | 200 | 80 | 80 |
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 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:
| family | category | subcategory | quantity_in_stock |
|---|---|---|---|
| food | meat | cow meat | 570 |
| food | meat | pork meat | 400 |
| food | meat | NULL | 970 |
| food | vegetables | leaf vegetables | 280 |
| food | vegetables | non leaf vegetables | 730 |
| food | vegetables | NULL | 1010 |
| food | NULL | NULL | 1980 |
| NULL | NULL | NULL | 1980 |
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 …
| food | meat | NULL | 770 |
|---|---|---|---|
| food | vegetables | NULL | 1010 |
… 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 stockGROUP BY ROLLUP(family, category, subcategory)ORDER BY family, category, subcategory |
The result is:
| family | category | subcategory | quantity_in_stock |
|---|---|---|---|
| food | meat | Cow meat | 570 |
| food | meat | Pork meat | 400 |
| food | meat | All Sub-Categories | 970 |
| food | vegetables | Leaf vegetables | 280 |
| food | vegetables | Non leaf vegetables | 730 |
| food | vegetables | All Subcategories | 1010 |
| 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 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!
