In SQL Server, the COALESCE expression is used to handle NULL values. If the expression is null, then returns a default value. During the evaluation of the expression, the user-defined values take the place of the NULL values. A non-null result is always returned from an argument list that has been evaluated using SQL COALESCE expression.

In order to prevent having your results ruined by null values, it's imperative that you learn how to deal with missing values in your datasets. You should know how they affect computations, queries, reports, and data-set planning.

Properties of the SQL Coalesce

The expressions must all use the same data types.
It can have more than one expression.
Using SQL's Coalesce expression is a shortcut for using the Case expression.
When an integer and a character expression are used together, the result is always an integer.

Syntax

The syntax for the COALESCE expression is as follows:

COALESCE(expression1, expression2, ..., expressionN)

Here, each expression is evaluated in the order they are provided, and the first non-NULL expression is returned. If all expressions evaluate to NULL, then the COALESCE expression returns NULL.

Examples

Let's look at some examples to understand the usage of COALESCE:

1. Providing a default value:

SELECT COALESCE(column_name, 'Default Value') AS result
FROM table_name;

In this example, if the column_name is NULL, the COALESCE expression will return the string 'Default Value' as the result.

2. Consolidating multiple columns:

SELECT COALESCE(column1, column2, column3) AS result
FROM table_name;

Here, if column1 is NULL, COALESCE will check column2 for a non-NULL value. If column2 is also NULL, it will check column3. The first non-NULL value found will be returned as the result.

3. Handling NULL in mathematical calculations:

SELECT COALESCE(column1, 0) + COALESCE(column2, 0) AS result
FROM table_name;

In this example, if either column1 or column2 is NULL, COALESCE will replace the NULL value with 0 before performing the addition.

COALESCE can be used with any data type, including strings, numbers, dates, and even more complex expressions. It is a versatile tool that simplifies data manipulation by providing a concise way to handle NULL values.

It's worth noting that COALESCE is not limited to SQL Server and is supported by many other database management systems, making it a portable solution for working with NULL values across different platforms.