[sql] COALESCE Function in TSQL

Can someone explain how the COALESCE function in TSQL works? The syntax is as follows

COALESCE(x, y)

The MSDN document on this function is pretty vague

This question is related to sql sql-server sql-server-2008 tsql

The answer is


Here is a simple query containing coalesce -

select * from person where coalesce(addressId, ContactId) is null.

It will return the persons where both addressId and contactId are null.

coalesce function

  • takes least two arguments.
  • arguments must be of integer type.
  • return the first non-null argument.

e.g.

  • coalesce(null, 1, 2, 3) will return 1.
  • coalesce(null, null) will return null.

There is a lot more to coalesce than just a replacement for ISNULL. I completely agree that the official "documentation" of coalesce is vague and unhelpful. This article helps a lot. http://www.mssqltips.com/sqlservertip/1521/the-many-uses-of-coalesce-in-sql-server/


declare @store table (store_id varchar(300))
insert into @store 
values ('aa'),('bb'),('cc')
declare @str varchar (4000)
select @str = concat(@str+',',store_id) from @store
select @str

Simplest definition of the Coalesce() function could be:

Coalesce() function evaluates all passed arguments then returns the value of the first instance of the argument that did not evaluate to a NULL.

Note: it evaluates ALL parameters, i.e. does not skip evaluation of the argument(s) on the right side of the returned/NOT NULL parameter.

Syntax:

Coalesce(arg1, arg2, argN...)

Beware: Apart from the arguments that evaluate to NULL, all other (NOT-NULL) arguments must either be of same datatype or must be of matching-types (that can be "implicitly auto-converted" into a compatible datatype), see examples below:

PRINT COALESCE(NULL, ('str-'+'1'), 'x')  --returns 'str-1, works as all args (excluding NULLs) are of same VARCHAR type.
--PRINT COALESCE(NULL, 'text', '3', 3)    --ERROR: passed args are NOT matching type / can't be implicitly converted.
PRINT COALESCE(NULL, 3, 7.0/2, 1.99)      --returns 3.0, works fine as implicit conversion into FLOAT type takes place.
PRINT COALESCE(NULL, '1995-01-31', 'str') --returns '2018-11-16', works fine as implicit conversion into VARCHAR occurs.

DECLARE @dt DATE = getdate()
PRINT COALESCE(NULL, @dt, '1995-01-31')  --returns today's date, works fine as implicit conversion into DATE type occurs.

--DATE comes before VARCHAR (works):
PRINT COALESCE(NULL, @dt, 'str')      --returns '2018-11-16', works fine as implicit conversion of Date into VARCHAR occurs.

--VARCHAR comes before DATE (does NOT work):
PRINT COALESCE(NULL, 'str', @dt)      --ERROR: passed args are NOT matching type, can't auto-cast 'str' into Date type.

HTH


I'm not sure why you think the documentation is vague.

It simply goes through all the parameters one by one, and returns the first that is NOT NULL.

COALESCE(NULL, NULL, NULL, 1, 2, 3)
=> 1


COALESCE(1, 2, 3, 4, 5, NULL)
=> 1


COALESCE(NULL, NULL, NULL, 3, 2, NULL)
=> 3


COALESCE(6, 5, 4, 3, 2, NULL)
=> 6


COALESCE(NULL, NULL, NULL, NULL, NULL, NULL)
=> NULL

It accepts pretty much any number of parameters, but they should be the same data-type. (If they're not the same data-type, they get implicitly cast to an appropriate data-type using data-type order of precedence.)

It's like ISNULL() but for multiple parameters, rather than just two.

It's also ANSI-SQL, where-as ISNULL() isn't.


Here is the way I look at COALESCE...and hopefully it makes sense...

In a simplistic form….

Coalesce(FieldName, 'Empty')

So this translates to…If "FieldName" is NULL, populate the field value with the word "EMPTY".

Now for mutliple values...

Coalesce(FieldName1, FieldName2, Value2, Value3)

If the value in Fieldname1 is null, fill it with the value in Fieldname2, if FieldName2 is NULL, fill it with Value2, etc.

This piece of test code for the AdventureWorks2012 sample database works perfectly & gives a good visual explanation of how COALESCE works:

SELECT Name, Class, Color, ProductNumber,
COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2008

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Get last 30 day records from today date in SQL Server How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904) SQL Server Service not available in service list after installation of SQL Server Management Studio How to delete large data of table in SQL without log?

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL