This question already has an answer here:
这个问题在这里已有答案：
- SQL - Difference between COALESCE and ISNULL? [duplicate] 4 answers
- SQL - COALESCE和ISNULL之间的区别？ [重复] 4个答案
I read the comparison between ISNULL and COALESCE on msdn.
Microsoft's explain
我在msdn上阅读了ISNULL和COALESCE之间的比较。微软的解释
The part that
那个部分
"Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int whereas for COALESCE, you must provide a data type."
“ISNULL和COALESCE的验证也不同。例如，ISNULL的NULL值转换为int，而对于COALESCE，您必须提供数据类型。”
confused me. Someone could give me an example to make it more clear?
使我困惑。有人可以举个例子来说明一点吗？
Thanks for help
感谢帮助
1 个解决方案
#1
3
It is valid query and will return INT:
它是有效查询并将返回INT：
SELECT ISNULL(NULL, NULL);
It will fail:
它会失败：
SELECT COALESCE(NULL, NULL);
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.
COALESCE的至少一个参数必须是不是NULL常量的表达式。
It is valid query and will return INT:
它是有效查询并将返回INT：
SELECT COALESCE(CAST(NULL AS INT), NULL);
You can check metadata for resultsets:
您可以检查结果集的元数据：
DEMO
SELECT 'ISNULL',name, system_type_name
FROM sys.dm_exec_describe_first_result_set ('SELECT ISNULL(NULL, NULL)', NULL,0)
UNION ALL
SELECT 'COALESCE', name, system_type_name
FROM sys.dm_exec_describe_first_result_set (N'SELECT COALESCE(CAST(NULL as FLOAT), NULL);', NULL,0)
#1
3
It is valid query and will return INT:
它是有效查询并将返回INT：
SELECT ISNULL(NULL, NULL);
It will fail:
它会失败：
SELECT COALESCE(NULL, NULL);
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.
COALESCE的至少一个参数必须是不是NULL常量的表达式。
It is valid query and will return INT:
它是有效查询并将返回INT：
SELECT COALESCE(CAST(NULL AS INT), NULL);
You can check metadata for resultsets:
您可以检查结果集的元数据：
DEMO
SELECT 'ISNULL',name, system_type_name
FROM sys.dm_exec_describe_first_result_set ('SELECT ISNULL(NULL, NULL)', NULL,0)
UNION ALL
SELECT 'COALESCE', name, system_type_name
FROM sys.dm_exec_describe_first_result_set (N'SELECT COALESCE(CAST(NULL as FLOAT), NULL);', NULL,0)