SQL TSQL 中的 COALESCE 函数

COALESCE Function in TSQL

提问by Lloyd Banks

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

有人能解释一下 TSQL 中的 COALESCE 函数是如何工作的吗？语法如下

COALESCE(x, y)

合并(x, y)

The MSDN document on this function is pretty vague

关于此功能的 MSDN 文档非常模糊

采纳答案by Bill Melius

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.

有人告诉我 COALESCE 的成本低于 ISNULL，但研究并未表明这一点。ISNULL 只接受两个参数，一个被评估为 NULL 的字段，以及如果它被评估为 NULL 时你想要的结果。COALESCE 将接受任意数量的参数，并返回遇到的第一个非 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/

这里有更详尽的细节描述 http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

回答by MatBailie

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.

它只是一个一个地遍历所有参数，并返回第一个参数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.

这就像ISNULL()但是对于多个参数，而不仅仅是两个。

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

它也是ANSI-SQL，哪里ISNULL()不是。

回答by John Waclawski

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

这是我看待 COALESCE 的方式......希望它是有道理的......

In a simplistic form….

以一种简单的形式......

Coalesce(FieldName, 'Empty')

Coalesce(FieldName, 'Empty')

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

所以这转化为……如果“FieldName”为 NULL，则用“EMPTY”一词填充字段值。

Now for mutliple values...

现在对于多个值...

Coalesce(FieldName1, FieldName2, Value2, Value3)

合并（字段名称1，字段名称2，值2，值3）

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

如果Fieldname1中的值为null，则用Fieldname2中的值填充，如果FieldName2为NULL，则用Value2填充，以此类推。

This piece of test code for the AdventureWorks2012sample database works perfectly & gives a good visual explanation of how COALESCEworks:

这段AdventureWorks2012示例数据库的测试代码完美运行，并对COALESCE 的工作原理给出了很好的直观解释：

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

回答by CindyPsych

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/

除了替换 ISNULL 之外，还有更多要合并的内容。我完全同意 coalesce 的官方“文档”含糊不清且无益。这篇文章很有帮助。 http://www.mssqltips.com/sqlservertip/1521/the-many-uses-of-coalesce-in-sql-server/

回答by Neeraj Bansal

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.

它将返回 addressId 和 contactId 都为 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.

• coalesce(null, 1, 2, 3) 将返回 1。
• coalesce(null, null) 将返回 null。

回答by Eddie Kumar

Simplest definition of the Coalesce() functioncould be:

Coalesce() 函数的最简单定义可能是：

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

Coalesce() 函数计算所有传递的参数，然后返回未计算为 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:

注意：除了计算为 NULL 的参数外，所有其他 (NOT-NULL) 参数必须具有相同的数据类型或必须具有匹配类型（可以“隐式自动转换”为兼容的数据类型），请参阅示例以下：

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

HTH

回答by Xiao

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