SQL SERVER

A Complete Guide to the SQL Server COALESCE Function

intro

Let's learn how to handle NULL values like a pro in real-world scenarios thanks to the SQL Server COALESCE function.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

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:

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

Copy
        
1 SELECT COALESCE(NULL, 'two', NULL, 'four');

The result will be:

Copy
        
1 'two'
Executing the COALESCE query in DbVisualizer
Executing the COALESCE query in DbVisualizer

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:

Copy
        
1 SELECT COALESCE(NULL, NULL, NULL);

The query will fail with the following error:

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

Copy
        
1 SELECT 2 Id, 3 Name, 4 Surname, 5 Address, 6 COALESCE(Address, 'N/A') AS StringAddress 7 FROM Employee;

The result will be:

NULLs became “N/A” strings
NULLs became “N/A” strings

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:

Copy
        
1 SELECT Id, Name, Price, Discount 2 FROM Product 3 ORDER BY Discount ASC, Name ASC;

The result will be:

Note the sorting with NULL values first
Note the sorting with NULL values first

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:

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

Note the sorting on the Discount column
Note the sorting on the Discount column

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:

Copy
        
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.

FinalPrice has been calculated correctly
FinalPrice has been calculated correctly

Without COALESCE, the query would return:

Note that FinalPrice now has a `NULL` value on some rows
Note that FinalPrice now has a `NULL` value on some rows

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:

  • Ensure all arguments are of the same or compatible data types to avoid implicit conversions and unexpected results.
  • Utilize COALESCE with aggregate functions like SUM, AVG, and MAX to effectively handle NULL values.
  • Use COALESCE to provide default values for columns that might be NULL and in calculations to avoid NULL propagation.
  • Keep in mind that COALESCE makes queries harder to read, so use it sparingly.
  • Limit the number of arguments to only what is necessary to avoid performance issues.
  • Place the most likely non-NULL values first to improve performance.
  • Do not use COALESCE with non-nullable columns.
  • Avoid passing all NULL constants to the function to avoid a syntax error.

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:

  • ISNULL: Replaces NULL with a specified replacement value.
  • CASE WHEN: Allows for more complex conditional logic to handle NULL values. Discover more in our guide on the SQL CASE statement.
  • NULLIF: Returns NULL if the two specified arguments are equal.

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.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

A Complete Guide to the SQL Server FOR XML PATH Clause

author Antonello Zanini tags SQL SERVER XML 8 min 2024-10-01
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

SQL DATEPART: Get a Part of a Date in SQL Server

author Antonello Zanini tags SQL SERVER 8 min 2024-09-19
title

The Definitive Guide to the NULL SQL Server Value

author Antonello Zanini tags SQL SERVER 7 min 2024-09-12
title

Index Creation in SQL

author Lukas Vileikis tags MySQL POSTGRESQL SQL SERVER 7 min 2024-08-15
title

SQL STUFF: Insert a String Into Another in SQL Server

author Antonello Zanini tags SQL SERVER 7 min 2024-07-18
title

SQL Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15
title

How to Compare SQL Dates

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 4 min 2024-07-11
title

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗