COALESCE Function in TSQL

Question

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

score 218 · Answer 1 · edited Aug 21 '17 at 07:09

218

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.

edited Aug 21 '17 at 07:09

IsmailS

10,797
21
82
134

answered Nov 13 '12 at 18:27

MatBailie

83,401
18
103
137

4

+1 For your explanation about the data types precedence. I believe that `ISNULL` returns a value with the same datatype as the first parameter, though – Lamak Nov 13 '12 at 18:33
5

Your last code example should give the error 'At least one of the arguments to COALESCE must be a typed NULL' Source: http://www.sql-server-performance.com/2007/coalesce-argument-must-be-typed-null/ – maqk Sep 24 '13 at 10:52
2

The documentation says a lot while also managing to provide almost zero value. Good docs provide a simple example with a simple result. Coalesce immediately moves into `expressions`, comparisons with CASE, comparisons with ISNULL, and finally an example with no result. Then an overly complex example with too many details. When all we need is this answer with 5 - 6 lines of WTF this thing is and does. – P.Brian.Mackey Jun 07 '17 at 19:30
1

Details of [Data Type Precedence (Transact-SQL) - TechNet - Microsoft](https://technet.microsoft.com/en-us/library/ms190309(v=sql.110).aspx) – IsmailS Aug 21 '17 at 07:07

score 75 · Accepted Answer · answered Nov 13 '12 at 18:40

75

I've been told that COALESCE is less costly than ISNULL, but research doesn't indicate that. ISNULL takes only two parameters, the field being evaluated for NULL, and the result you want if it is evaluated as NULL. COALESCE will take any number of parameters, and return the first value encountered that isn't NULL.

There's a much more thorough description of the details here http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

answered Nov 13 '12 at 18:40

Bill Melius

1,043
7
8

6

Beware of using `ISNULL`, its not standard, less flexible, I read that it will return the datatype of the first argument always and not the datatype of the returned value like `COALESCE` does. – sprocket12 Nov 19 '13 at 15:10

score 20 · Answer 3 · answered Sep 23 '13 at 19:43

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

score 4 · Answer 4 · answered Jul 10 '13 at 13:52

4

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/

answered Jul 10 '13 at 13:52

CindyPsych

59
1

3

That article you linked to is extremely misleading (as is pointed out by several people, including the author, in the comments section). All the neat tricks he highlights can ALL be done using ISNULL in place of coalesce. – Hobo Spider Jul 31 '13 at 19:47

score 3 · Answer 5 · answered Feb 20 '17 at 10:20

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.

Eddie Kumar · Answer 6 · 2019-08-13T15:53:41.587

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

score 1 · Answer 7 · edited Apr 30 '19 at 22:41

1

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

edited Apr 30 '19 at 22:41

Anthony

3,595
2
29
38

answered Apr 30 '19 at 22:19

Xiao

21
1

1

I found this one is much simpler. – Xiao Apr 30 '19 at 22:21

score 0 · Answer 8 · answered Jul 21 '21 at 21:28

0

coalesce take n number of arguments from left to right. coalesce selects the first non null argument. similarly isnull(field,'unknown') is equivalent to coalesce(field,'unknown') with the difference that coalesce can compare multiple fields.

answered Jul 21 '21 at 21:28

Golden Lion

3,840
2
26
35

COALESCE Function in TSQL

8 Answers8

Linked

Related