SQL Server COALESCE() Function

The COALESCE() function in SQL Server is a powerful tool designed to handle NULL values effectively. It evaluates a list of expressions in a specified order and returns the first non-null value encountered.

In this article, We will learn about the SQL Server COALESCE() by understanding various examples in detail.

COALESCE() Function in SQL Server

• The SQL Server COALESCE() function is used to handle NULL values.
  
• The NULL values are replaced with the user-given value during the expression value evaluation process.
  
• The SQL Server COALESCE function evaluates the expression in a definite order and always results first not null value from the defined expression list.

Syntax

SQL Server COALESCE() function syntax is:

COALESCE ( exv1, exv2..., exvN )

Explanation:

• exv1, exv2..., exvN are expression values.
• All expressions must have the same data type.
• It could have multiple expressions.

Example of SQL Server COALESCE() Function

Let us look at some examples of the COALESCE() function in SQL server.

Example 1

Let's Write a query to determine the first non-null value from a list of inputs. Given the inputs NULL, 'X', and 'Y', the query should return the first non-null value among them.

SELECT COALESCE (NULL, 'X', 'Y') 
AS RESULT ;

Output:

Example 2

Let's write a SQL query to return the first non-null value from the list. If all values are null, return null. For example, in the query SELECT COALESCE(NULL, 13, 24, 35, 46) AS RESULT.

SELECT COALESCE (NULL, 13, 24, 35, 46) 
AS RESULT ;

Output:

Example 3

Let's Write a SQL query to demonstrate the use of the `COALESCE` function by selecting the first non-null value from a series of null values, and returning a default string 'GFG' when all provided values are null.

SELECT COALESCE (NULL, NULL, NULL, NULL, NULL, 'GFG') 
AS RESULT ;

Output:

Example 4

Given a query that uses the `COALESCE` function with a mix of `NULL` values and non-null values, identify the first non-null value returned by the function and explain how `COALESCE` prioritizes values in SQL.

SELECT COALESCE (NULL, NULL, NULL, NULL, 5, 'GFG') 
AS RESULT ;

Output:

Example of COALESCE() Function SQL Server Management Studio

Here, we will run the COALESCE() Function in SQL Server Management Studio.

Query:

SELECT COALESCE 
(NULL, NULL, NULL, NULL, NULL, 'GFG', 1)

Output:

Example of SQL Server Coalesce function in a string concatenation operation

Let us suppose we have below table name "GeekName".

Query:

SELECT F_Name + ' ' +M_Name+ ' ' 
+ L_Name FullName FROM GeekName ;

Output:

Example of SQL Server COALESCE  function to handle the NULL values

The SQL statement will concatenate all three names, but no NULL values will appear in the output.

SELECT F_Name +' '+COALESCE(M_Name, '') +' '
+ L_Name   FullName  FROM GeekName ;

Output :

Important Points About SQL Server COALESCE() Function

• The COALESCE() function in SQL Server is used to handle NULL values effectively by replacing them with user-defined values during expression evaluation.
• All expressions within the COALESCE() function must have the same data type to ensure proper evaluation and return of values.
• The COALESCE() function is available in SQL Server (all supported versions), Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, and Parallel Data Warehouse.

Conclusion

The COALESCE() function simplifies the handling of NULL values in SQL Server by providing a straightforward way to ensure that queries return useful results even when dealing with incomplete or missing data. By evaluating expressions from left to right and returning the first non-null value, COALESCE() allows for more robust and reliable data retrieval.