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.


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.


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:

Name Chinese Maths English
A 78 90 85
B 83 NULL 92
C 92 87 NULL

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:

Name Total
A 253
B 175
C 179

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:

name description price
product1 this is a 10.99
product2 no desc 20.99
product3 this is c 30.99
product4 no desc 40.99
product5 this is e 50.99

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:

order_id customer_name order_date
1 John 2022-01-01
2 Mary 2022-01-02
3 Bob unknown
4 Alice 2022-01-04
5 Tom unknown


The COALESCE() function is a very useful function that can select the first non-null value from multiple expressions and replace NULL values with other values. Using the COALESCE() function can simplify SQL queries, making the code more concise and easier to maintain.