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

218757 次浏览
小开

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.

小开
最佳答案

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/

小开

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/

小开

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
小开

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.
小开

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

小开 
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
小开

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.


提交答案