SQL Server COALESCE Function with Practical Examples

SQL Server COALESCE Function

The SQL Coalesce() function is a basic SQL function which is used to evaluate the list of values and return the first occurrence of non-NULL value. The SQL Server Coalesce and IsNull functions both are used to handle NULL values in SQL Server. In argument of SQL Server Coalesce function we can either specified string expression or we can give column name of table during the evaluation process column name will be replaced with its corresponding values.

SQL Server has some built-in character manipulation functions and data analysis function also, when we are working with database many times we need to navigate from NULL and missing values, also while generating reports and views we need to check for value availability in database to avoid error in such situation we would use Coalesce function.

SQL Server Coalesce function is an alternative to the IsNull() function for evaluation of NULL value and for conditional resulting value it is works similar to SQL Case statement . SQL Server Coalesce function evaluates argument in same order in which it specified within function and return first evaluated non-null value as result.

 

Properties of the SQL server Coalesce function

1. All arguments must be of same datatype
2. SQL Server Coalesce function can have more than one argument
3. The SQL Server Coalesce function is similar to SQL Case statement, it is short form of SQL Case statement
4. If SQL Server Coalesce function arguments are of integer type followed by character expression yield’s function returns integer as an output.

 

SQL server Coalesce function Syntax

COALESCE ( Expression | column_name, [ expression | column_name],..)

Expression | Column_name: are the function arguments, expression can be ant valid SQL expression or NULL or it can be SQL table column name of which function need to evaluate for NOT NULL value.

 

Return Type of SQL Server Coalesce function

SQL Server Coalesce function returns value data type is of first not null expression data type.

If all arguments in function is of NULL than function will return NULL and if all arguments in function are not null values than result is of typed as non-nullable.

 

SQL Server Coalesce function Examples

First we starts with Simple String Expression Evaluation example

SELECT COALESCE (NULL, 'Hello', 'Good Morning') AS NotNULL

• In above query we used Coalesce function to find first NotNULL value arguments, it starts evolution from first argument which is NULL so it checks for second argument that is a string ‘Hello’ so it will return ‘Hello’ as a Resulting Value.

OUTPUT:

SQL server Coalesce function example with Integer arguments

SELECT COALESCE (NULL, 10, 20, 30, 40, 50) AS NotNULL

• In previous query we have apply SQL Server Coalesce function to check for NonNULLABLE value but with integer arguments so it will return first integer value of 10 as result.

OUTPUT:

SQL Server Coalesce function example with Integer argument followed by String with multiple NULL arguments

SELECT COALESCE (NULL, NULL, NULL, 10, 'Hello') AS NotNULL

• In above example, we have given more than NULL arguments with Coalesce function, with one integer argument followed by string. The resulting value of this function will be first notNULL value that is 10.

OUTPUT:

SQL Server Coalesce function example with String argument followed by Integer argument value with multiple NULL arguments

SELECT COALESCE (NULL, NULL, NULL, 'Hello', 10) AS NotNULL

• The above query return error Message ‘Conversion fail while converting the Varchar value ‘Hello’ to datatype Int’

 

Compare COALESCE SQL with ISNULL

The use of Coalesce SQL and Isnull function are similar, both SQL functions can be used to evaluate expression value and returns first occurrence of NotNull value, and if all argument values are NULL then both functions will return NULL

Following are some differences between Coalesce and ISNULL

• IsNull evaluate only once whereas Coalesce evaluates multiple times.
• Datatype of resulting value is different in both the functions , isNull return value in first argument’s datatype whereas Coalesce the data type of value with the highest precedence.
• IsNull have limit of two arguments although we can use nested IsNull for more arguments, whereas Coalesce can take any number of arguments

 

Examples of similarity between Coalesce and IsNuLL

SELECT COALESCE (NULL, 'Hello') AS 'NotNULL Value'
SELECT ISNULL(NULL, 'Hello') AS 'NotNULL Value'

• Above both queries show similarities between Coalesce and IsNull function, both queries give same result when we execute it.

OUTPUT:

Output of above both queries are same

 

Examples of difference between Coalesce and IsNULL

SELECT COALESCE (NULL, NULL) AS 'NotNULL Value'

SELECT ISNULL(NULL, NULL) AS 'NotNULL Value'

When we execute above both queries,

• first query with Coalesce function will return error message ‘At least one of the arguments to COALESCE must be a typed NULL’
• Second query with IsNull function will return NULL as return

OUTPUT:

Example of comparing Coalesce with  Nested IsNull

SELECT COALESCE (NULL, NULL, 10) AS 'NotNULL Value'

SELECT ISNULL(NULL, ISNULL(NULL, 10)) AS 'NotNULL Value'

• Above both queries will give same result as 10, but the way to write arguments to evaluate is difference, we can give more arguments with Coalesce function whereas with IsNULL we can give only two arguments.

OUTPUT:

 

Compare COALESCE and CASE

The purpose of SQL Server Coalesce and SQL Case statement is similar to check expression value, as per the Microsoft document The Coalesce expression is a syntactic shortcut for the CASE expression.

Syntax of CASE Condition statement and Coalesce function

CASE
WHEN (expression1 is not NULL) THEN expresssion1
WHEN (expression2 is not NULL) THEN expression2
……
ELSE expression
END
SELECT COALESCE (expression1, expression2…) as ‘Result value’

 

Examples of similarity between Coalesce and CASE

Let’s take one table customer for taking example using column name as argument values to be evaluate

SELECT COALESCE (custid, custname) AS 'NOT NULL VALUE'
FROM     tblcust;
SELECT CASE 
WHEN custid IS NOT NULL THEN custid 
WHEN custname IS NOT NULL THEN custname 
END AS 'Case Results'
FROM     tblcust;

• In above two queries we check for notNULL value for two column custid and custname
• First query with Coalesce function will return list of custid as it will be the first non-empty value to be evaluated
• Second query will also return list of custid as first conditional statement returns true

OUTPUT:

 

SQL Server Coalesce function | String Concatenation

SQL Server Coalesce function is used to handle the behaviour of NULL value. There is a condition when we need to marge string values or string column values to avoid error of NULL values or empty value we can used Coalesce function.

For example, we want to display customer emailed with contact number as a single column result from customer table

SELECT emailid + ' ' + contact AS 'Email_contact'
FROM     tblcust

OUTPUT:

Using Coalesce function

SELECT emailid + ' ' + COALESCE (contact, ' ') AS Expr1
FROM     tblcust

OUTPUT:

• When we execute above two queries it will return email and contact number
• First query will return only those customer’s data of whose email and contact values are not NULL.
• Second query will return all the column values regardless of notNULL in contact number or emailid

 

SQL Server Coalesce function | Pivoting data

Coalesce function is also used to marge column value and store it into one variable as a single string with single quote to prepare a string of values.

DECLARE @col varchar(MAX);
SELECT @col = COALESCE(@col,'') +''''+custname +''''+ ',' 
FROM dbo.tblcust WHERE city='surat';

SELECT substring(@col,1,len(@col)-1)

• In above query first we declare variable to store value with DECLARE keyword
• Coalesce function is used with custname to choose notnull value of custname and marge all custname who have city as ‘Surat’
• In last line to display variable value we use SELECT statement and also to remove last space we applied substring function also.

OUTPUT:

 

SQL Server Coalesce function | Scalar user-defined function

SQL Server Coalesce function can also be used with Scalar function to make group of notNULL string with USER Defined function

Example of SQL Server Coalesce function with Scalar user-defined function

CREATE FUNCTION concate 
(
  @city varchar(100)
)
RETURNS NVARCHAR(MAX)
AS 
BEGIN
  DECLARE @str NVARCHAR(MAX);
  SELECT @str = COALESCE(@str + ', ', '') + emailid
    FROM tblcust
	WHERE city = @city
	ORDER BY city;
  RETURN (@str);
END

• In above query, first we have crated one user define function using CREATE function.
• Then @str is a variable used to store SELECT output, in select clause Coalesce function we first concat emailed values which are NOT NULL and fulfilled condition of city =’Surat’
• To see the output of above function we need to use SQL SELECT Statement

OUTPUT:

SELECT dbo.concate(city) AS city
FROM     tblcust
GROUP BY city, emailid
ORDER BY city

 

Summary

On this article, SQL Server Coalesce function is explained with examples and comparison with CASE statement and IsNull function. SQL Server Coalesce function is used to evaluate multiple values to check for NOT NULL value. It is similar to NOTNULL function but has some differences which are explained in article. Also, we have covered compared SQL Server Coalesce function with SQL CASE Statement with example and string concatenation, pivoting and user-defined function.

 

References

COALESCE (Transact-SQL)

 

Further Reading

What is the difference bewteen ifnull and coalesce in mysql?