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:

Conclusion

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.