SQL Server COALESCE() Function

In SQL Server, the COALESCE() function is used to return the value of the first non-null expression in a list of parameters. If all parameters are NULL, then NULL is returned.

Syntax

The syntax for the COALESCE() function is as follows:

COALESCE ( expression1, expression2, ... expression_n )

Where expression1, expression2, …, expression_n are the expressions to be checked.

Use Cases

The COALESCE() function is particularly useful in the following scenarios:

• When you need to retrieve the first non-NULL value from multiple columns or expressions.
• When you need to combine multiple values into a single value.

Examples

Suppose we have a table containing records of students’ grades, including Name, Chinese score, Maths score, and English score. Sometimes, we need to query the total score of each student, and treat any missing subject score as 0. We can use the COALESCE() function to achieve this requirement.

Example 1: Calculate the total score of each student

Suppose we have the following records of students’ grades:

To calculate the total score of each student, we can use the following SQL statement:

SELECT Name, COALESCE(Chinese, 0) + COALESCE(Maths, 0) + COALESCE(English, 0) AS Total
FROM student_scores;

Executing the above SQL statement, we will get the following results:

Example 2: Select non-null values from multiple columns

Suppose we have a table containing product information, including the name, description, and price of each product. Some products do not have a description, but we want to display “no description” in the query results if there is no description.

SELECT name, COALESCE(description, 'no desc') AS description, price
FROM products;

Here is an example output:

Example 3: Replace NULL values with other values

Suppose we have a table containing customer order information, including order ID, customer name, and order date. Some orders do not have a date, but we want to display “unknown” as the date for these orders.

SELECT order_id, customer_name, COALESCE(order_date, 'unknown') AS order_date
FROM orders;

Here is an example output:

COALESCE versus ISNULL in SQL Server

In SQL Server, ISNULL(check_expression, replacement_value) is often used for two-argument checks. However, ISNULL returns the data type and length of the first argument, which can silently truncate longer replacement strings. COALESCE uses SQL Server’s data type precedence rules to determine the return type, avoiding unexpected truncation:

DECLARE @code varchar(3) = NULL;

-- ISNULL truncates 'UNKNOWN' to 3 characters ('UNK'):
SELECT ISNULL(@code, 'UNKNOWN') AS isnull_result;

-- COALESCE promotes to varchar(7) and preserves the full string:
SELECT COALESCE(@code, 'UNKNOWN') AS coalesce_result;

Evaluation of input expressions

SQL Server rewrites COALESCE() as a CASE expression. This means an input expression can be evaluated more than once; a subquery argument is evaluated twice. If data can change between evaluations under READ COMMITTED, the result can differ. When a stable subquery result is required, use SNAPSHOT isolation or compute the subquery once in a subselect, as described in Microsoft’s COALESCE evaluation notes.

Conclusion

The COALESCE() function selects the first non-null value from multiple expressions and uses standard data type precedence. In SQL Server, input expressions may be evaluated more than once. To compare NULL replacement functions across MySQL, PostgreSQL, SQLite, SQL Server, and Oracle, see the SQL NULL Handling by Database guide.