intro
Let's learn how to handle NULL values like a pro in real-world scenarios thanks to the SQL Server COALESCE function.
Handling NULL values can be quite challenging. Ignoring them may lead to incomplete data on the frontend and handling them at the application level can introduce performance overhead and data inconsistencies. Certain database management systems include functions that let you return the first non-NULL value from a set of expressions, and that is exactly what the SQL Server COALESCE function is all about!
In this guide, you will find out everything you need to know about COALESCE in SQL Server and see how it can help you retrieve NULL-safe result sets.
What Is COALESCE in SQL Server?
In SQL Server, COALESCE is a built-in function that returns the first non-NULL value from a list of expressions. It accepts a list of arguments, evaluating them in order and returning the value of the first expression that does not evaluate to NULL.
Note that COALESCE is part of the ANSI SQL standard, which means that it is widely supported by several SQL database management systems. This function is one of the most common allies for dealing with NULL values in SQL Server.
How to Use the COALESCE SQL Server Function
Here is the syntax of the COALESCE SQL Server function:
1
COALESCE(expression_1 [, ... , expression_n])
Where expression_1, ... , expression_n] is a list of valid SQL expressions separated by commas.
The COALESCE function in SQL Server accepts from 1 to n arguments. If you try to call the function without arguments, you will get a syntax error.
COALESCE evaluates the list of arguments from left to right, returning the first non-NULL value it encounters. Take a look at the example below:
1
SELECT COALESCE(NULL, 'two', NULL, 'four');
The result will be:
1
'two'
If all arguments are evaluated to NULL, the SQL Server COALESCE function returns NULL. At the same time, if you try to call COALESCE in SQL Server with all NULL constants:
1
SELECT COALESCE(NULL, NULL, NULL);
The query will fail with the following error:
1
[Code: 4127, SQL State: S1000] At least one of the arguments to COALESCE must be an expression that is not the NULL constant.
So, at least one COALESCE argument must be an expression that is not the NULL constant.
SQL Server COALESCE: Main Use Cases
Now that you know how to use the COALESCE SQL Server function, it is time to explore some popular use cases.
Note: The sample query below will be executed in DbVisualizer, a fully-featured database client that fully supports SQL Server and dozens of other databases, but any other database client will do.
Replace NULL Values With Placeholders
The most common SQL Server COALESCE use case involves using the function to provide default values for values that might evaluate to NULL.
For example, suppose you want to retrieve all information about the employees in your database. The Address column can have NULL values, and you want to avoid showing blank data in a report. The solution is to replace NULL addresses with a more readable “N/A” string using COALESCE as in the following query:
1
SELECT
2
Id,
3
Name,
4
Surname,
5
Address,
6
COALESCE(Address, 'N/A') AS StringAddress
7
FROM Employee;
The result will be:
All the missing addresses have been replaced with “N/A” strings as desired.
Sorting Data Ignoring NULL Values
When you sort data that includes NULL values in SQL Server, you may not get the expected result set. For example, consider this sample query:
1
SELECT Id, Name, Price, Discount
2
FROM Product
3
ORDER BY Discount ASC, Name ASC;
The result will be:
As you can see, products with NULL discounts go first, followed by products with non-NULL values. This could be a problem because a missing discount is semantically equivalent to a 0% discount.
To avoid this problem and get the sorting right, you can use the COALESCE function in SQL Server as follows:
1
SELECT Id, Name, Price, Discount
2
FROM Product
3
ORDER BY COALESCE(Discount, 0) ASC, Name ASC;
This time, the new result set will be:
Wonderful! The products have been sorted in a semantically correct order.
Dealing With NULL Values in Math Operations
Databases can produce unexpected behavior when adding, dividing, or multiplying by NULL values. To avoid that, you can use the SQL Server COALESCE function to replace the NULLs with consistent values.
Take a look at the query below:
1
SELECT
2
Name,
3
Price,
4
Discount,
5
Price * (1 - COALESCE(Discount, 0)/100) AS FinalPrice
6
FROM Product;
As shown in the following image, COALESCE ensures consistent results even when the value of the Discount column is NULL.
Without COALESCE, the query would return:
This is not what you want, as FinalPrice should not be NULL. See the difference?
Best Practices of the COALESCE Function in SQL Server
Below is a list of best practices for using COALESCE in SQL Server:
Conclusion
In this guide, you saw what the SQL Server COALESCE function is and how it works. You now know that it is a powerful utility for handling NULL values in SQL Server, ensuring that you get the first non-NULL value out of a list of expressions.
To better appreciate its capabilities, you need a tool that allows you to visually explore query results. This is where a comprehensive database client like DbVisualizer comes in. In addition to supporting several DBMSs, this tool offers advanced query optimization capabilities, ERD-like schema generation, and much more. Try DbVisualizer for free today!
FAQ
What are some alternatives to the COALESCE function in SQL Server?
Some alternatives to the COALESCE function in SQL Server include:
What is the difference between the SQL Server ISNULL vs COALESCE functions?
In SQL Server, ISNULL accepts two arguments and replaces a NULL value with the specified replacement value. Instead, COALESCE evaluates multiple expressions and returns the first non-NULL value. Also, COALESCE conforms to the ANSI SQL standard, whereas ISNULL is specific to SQL Server. This is why the PostgreSQL ISNULL function does not exist.
Do other databases support the COALESCE function?
Yes, since the COALESCE function is part of the ANSI SQL standard, it is available in several databases. These include MySQL, PostgreSQL, SQL Server, Oracle, and SQLite. Learn more in our complete tutorial on the SQL COALESCE function.
What is the performance impact of the SQL Server COALESCE function?
The SQL Server COALESCE function can impact the performance of a query. That is especially true when it contains multiple arguments, as evaluating them all to find the first non-NULL value takes time. To avoid performance bottlenecks, it is important to keep the COALESCE SQL Server arguments to a few and try to pass the non-NULL value in the first arguments.
What is the difference between CASE and COALESCE in SQL Server?
In SQL Server, CASE is a special operator to handle complex conditional logic. In detail, it returns values based on multiple conditions. On the other hand, COALESCE is used specifically to return the first non-NULL value from a list of expressions. While CASE can replicate COALESCE functionality, the other way around is not always true. Expand on this comparison on the official documentation.
