SQL Server COALESCE Function - SQL Server Guides

Handling null values is a very important skill. As a database developer, you should know how to deal with null values in your table. This tutorial will explain how to use the SQL Server COALESCE function.

A COALESCE() function is the one way to deal with null values; here, you will understand what the COALESCE() function is with an example, and then you will understand the syntax of the COALESCE() function, which explains how to use it in your query.

After that, through an example, you will use the COALESCE() function on the table to learn how to replace null with other values.

Table of Contents

What is SQL Server COALESCE function?

SQL Server COALESCE function returns the first non-null value from the given list of values, which means if you have a list of values, and some of the values in the list are null, then this function ignores the null values and returns a first non-null value.

For example, if you have a list of numbers like this [null, null, 5, 3, 7], then the COALESCE() function returns the first non-null value, which is 5 in this case. It ignores the first two null values in the list.

After the first two null values in the list, the first value which is not null is 5. If you provide a list like this [null, 3, null, 5, 7], the first non-null value is 3, so the function will return this value.

The syntax is given below.

COALESCE(exp_1, exp_2, .., exp_3)

Where,

COALESCE(): The function accepts a set of values containing null values and returns the first non-null value.
exp_1, exp_2,.., exp_3: It is the expression evaluated by the COALESCE() function. It is ignored if the expression is null; otherwise, if it is the first non-null value, that expression is returned.

For example, you have a list of values like (null, ‘USA’, null, ‘New York’, ‘Canada’). To find the first non-null value from the list, you can use COALESCE(), as shown below.

SELECT 
	COALESCE(null, 'USA', null, 'New York', 'Canada') AS FristNonNullValue;

The first non-null value is ‘USA’ in the list; here, you need to understand two things: first, look at the number of null values, and second, which non-null values are returned.

Here, in the query part, COALESCE(null, ‘USA’, null, ‘New York’, ‘Canada’), the COALESCE() function starts with the first value, which is the first null value, so it ignores this value. It moves to the next value, ‘USA’, which is the non-null value, the first non-null value in the list, so the function returns this value.

So, the COALESCE() ignores all the null values, and as soon as it finds any non-null values in the list, it returns that value and doesn’t care about other non-null values. For example, we have two null values in the list, ‘New York’ and ‘Canad’.

This is how the SQL Server COALESCE() function works.

Let’s move and see how to handle missing data in a table.

Handling Null Values in Table using SQL Server COALESCE Function

The table often contains null values. When any operation on tables yields results, some decisions are made using these results. What will happen if the result contains the wrong data? Will the decision also be wrong or ineffective? Yes, it affects the decision.

If the table contains null values while computing, it can ruin the results, which leads to bad decisions. So, there should be a way to handle these null values; here, you can use the COALESCE() function.

For example, a Customer table with columns CustomerID, Email and PhoneNumber is shown below.

Handling Null Values in Customers Table using SQL Server COALESCE Function

As you can see, the table columns Email and PhoneNumber have some missing values for the customer.

Now, you are on a marketing campaign and have access to a database that stores information about customers with their email and phone numbers.

As you can see, some records are missing values in the Email and PhoneNumber columns. You have to write a query that ensures the campaign reaches them through an alternative contact method; here, you can use the COALESCE() function, as shown below.

SELECT
	CustomerID,
	COALESCE(Email, PhoneNumber) AS ContactInfo
FROM
	Customers;

Handling Null Values in Table using SQL Server COALESCE Function

As you can see in the result set, the phone number is used wherever email is missing. For example, a customer with an ID equal to 2 emails is missing, so a phone number is used instead of email.

Let’s take another example where you must perform calculations or aggregate data where NULL values can affect the result or outcome.

For example, you have an OrderDetails table with columns, OrderID, ItemPrice, and Quantity, as shown below.

Handling Null Values in OrderDetails Table using SQL Server COALESCE Function

Here in the table, some of the item prices are missing in the ItemPrice column, and you need to compute the total value of the order when some items might not have a set price.

You can use the COALESCE() function, as shown below.

SELECT
	OrderID,
	SUM(COALESCE(ItemPrice, 0) * Quantity) AS TotalOrderValue
FROM
	OrderDetails
GROUP BY
	OrderID;

Dealing with Null Values in Table using SQL Server COALESCE Function

From the output, the total value for each order is computed. For example, the total value for the order ID equal to 2 is 40.00, and for 5, it is 25.00.

But here, you need to understand how missing values are handled when computing the total value for each order.

Understand the query part, SUM(COALESCE(ItemPrice, 0) * Quantity); this part computes the total value for each order by multiplying each item’s price (ItemPrice) by its quantity (Quantity), then summing the products for all items in each order.

If ItemPrice is null, COALESCE() replaces it with 0 to ensure the computation can process without errors.

Here, in the COALESCE(ItemPrice, 0), this function returns the first non-value. It checks if ItemPrice contains a null value and then uses 0 as a fallback.

This is important for ensuring that multiplying by Qunatity doesn’t result in a null value, which would happen if ItemPrice were null. Using 0, the computation contributes nothing to the sum for items without a specified price rather than excluding them or causing an error.

This is how to use the SQL Server COALESCE function to handle the missing value in a table column.

Conclusion

In this SQL Server tutorial, you learned how to return the first non-null value from the list of values using the SQL Server COALESCE function.

Additionally, you have used the COALESCE() function on the table to handle null values in the column where you replaced the null with 0 value to ensure smooth operation without any error in the result.