Table of Contents
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
- All arguments must be of same datatype
- SQL Server Coalesce function can have more than one argument
- The SQL Server Coalesce function is similar to SQL Case statement, it is short form of SQL Case statement
- 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.
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.
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.
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 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
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.
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
custid custname emailid contact city
101 riya firstname.lastname@example.org Surat
102 meena email@example.com 30123456 Surat
103 kiya firstname.lastname@example.org Surat
104 reenu email@example.com 89898989 Vadodara
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
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
Using Coalesce function
SELECT emailid + ' ' + COALESCE (contact, ' ') AS Expr1 FROM tblcust
- 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
notNULLin contact number or
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
custnameand marge all
custnamewho 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.
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.
@stris 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
SELECT dbo.concate(city) AS city FROM tblcust GROUP BY city, emailid ORDER BY city
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.