SELECT IFNULL(NULL, 'Replaces the NULL')
--> Replaces the NULL
SELECT COALESCE(NULL, NULL, 'Replaces the NULL')
--> Replaces the NULL
在这两个子句中,主要区别在于参数传递。对于IFNULL
,它是两个参数,对于COALESCE
,它是多个参数。那么,除此之外,我们这两者之间还有其他区别吗?
它在MS SQL中有何不同?
两者之间的主要区别在于IFNULL
函数接受两个参数,如果不是NULL
则返回第一个参数,如果第一个参数NULL
则返回第二个参数。
COALESCE
函数可以接受两个或多个参数并返回第一个非 NULL 参数,或者如果所有参数都为 null,则返回NULL
,例如:
SELECT IFNULL('some value', 'some other value');
-> returns 'some value'
SELECT IFNULL(NULL,'some other value');
-> returns 'some other value'
SELECT COALESCE(NULL, 'some other value');
-> returns 'some other value' - equivalent of the IFNULL function
SELECT COALESCE(NULL, 'some value', 'some other value');
-> returns 'some value'
SELECT COALESCE(NULL, NULL, NULL, NULL, 'first non-null value');
-> returns 'first non-null value'
更新:MSSQL 执行更严格的类型和参数检查。此外,它没有IFNULL
函数,而是ISNULL
函数,它需要知道参数的类型。 因此:
SELECT ISNULL(NULL, NULL);
-> results in an error
SELECT ISNULL(NULL, CAST(NULL as VARCHAR));
-> returns NULL
此外COALESCE
MSSQL 中的函数要求至少有一个参数为非空,因此:
SELECT COALESCE(NULL, NULL, NULL, NULL, NULL);
-> results in an error
SELECT COALESCE(NULL, NULL, NULL, NULL, 'first non-null value');
-> returns 'first non-null value'
COALESCE
的优点
-
COALESCE
是SQL标准函数。虽然
IFNULL
是特定于MySQL的,而在MSSQL(ISNULL
(中的等价物是特定于MSSQL的。 -
COALESCE
可以使用两个或多个参数(实际上,它可以处理单个参数,但在这种情况下毫无用处:COALESCE(a)
≡a
(。而MySQL的
IFNULL
和MSSQL的ISNULL
是COALESCE
的有限版本,只能使用两个参数。
COALESCE
的缺点
-
根据 Transact SQL 文档,
COALESCE
只是CASE
的语法糖,并且可以多次评估其参数。更详细:COALESCE(a1, a2, …, aN)
≡CASE WHEN (a1 IS NOT NULL) THEN a1 WHEN (a2 IS NOT NULL) THEN a2 ELSE aN END
.这大大降低了 MSSQL 中COALESCE
的有用性。另一方面,MSSQL 中的
ISNULL
是一个普通函数,从不多次计算其参数。COALESCE
MySQL和PostgreSQL都不会多次评估其参数。 -
在这个时候,我不知道SQL标准如何定义
COALESCE
。正如我们从上一点中看到的,RDBMS中的实际实现各不相同:有些(例如MSSQL(
COALESCE
多次评估其参数,有些(例如MySQL,PostgreSQL(则不然。c-treeACE声称它的
COALESCE
实现是SQL-92兼容的,他说:"这个函数在GROUP BY子句中是不允许的。此函数的参数不能是查询表达式。我不知道这些限制是否真的在SQL标准范围内;大多数COALESCE
的实际实现(例如MySQL,PostgreSQL(都没有这样的限制。IFNULL
/ISNULL
,作为正常功能,也没有这样的限制。
恢复
除非你在特定RDBMS中遇到特定的COALESCE
限制,否则我建议始终使用COALESCE
作为更标准和更通用的。
例外情况是:
- 长计算表达式或 MSSQL 中具有副作用的表达式(根据文档,
COALESCE(expr1, …)
可能会计算两次expr1
(。 - 在
GROUP BY
中使用或与 c-treeACE 中的查询表达式一起使用。 - 等。
SQL 服务器的差异:
-
没有
IFNULL()
功能,但有类似的ISNULL()
-
ISNULL
仅采用 2 个参数,而 COALESCE 采用可变数量的参数 -
COALESCE
基于ANSI SQL标准,而ISNULL
是专有的TSQL函数
。 ISNULL
和COALESCE
的验证也不同。例如,ISNULL
NULL
值转换为 int,而对于COAELSCE
,您必须提供类型。前任:ISNULL(NULL,NULL)
: 是整数。COALESCE(NULL,NULL)
:将抛出错误。COALESCE(CAST(NULL as int),NULL)
: 有效并返回 int。
结果表达式的数据类型确定 –
ISNULL
使用第一个参数类型,COALESCE
遵循CASE
表达式规则并返回具有最高优先级的值类型。
ifnull
只能替换第一个参数的空值。而coalesce
可以用另一个值替换任何值。使用标准 SQL 中的coalesce
,您可以让许多参数转换许多值。
根据下面的注释编辑示例。
示例:coalesce(null, null, null, 'b*', null, 'null*')
返回 'b*',并且无法与 ifnull
执行。
此 db2 SQL 不适用于 COALESE,我不会看到检索到的任何行。由于我使用了IFNULL,因此它按预期工作
select a.mbitno ,a.mbstqt,ifnull(b.apr,0)
from
(
select mmstcd,mbstat,mbfaci,mbwhlo,mbitno,mbstqt,MBALQT from libl.mitbal inner join libl.mitmas on
mmcono=mbcono and mmitno=mbitno
where mbcono=200 and mbstat in ('20','50') and mmstcd>0
)
as a left join
(
select mlfaci,mlwhlo,mlitno,mlstas,sum(mlstqt) as APR from libl.mitloc where mlcono=200 and mlstas='2'
group by mlfaci,mlwhlo,mlitno,mlstas
)
b on b.mlfaci=a.mbfaci and b.mlwhlo=a.mbwhlo and b.mlitno=a.mbitno
where a.mbitno in 'GWF0240XPEC' and a.mbstqt>0 and a.mbstqt<>ifnull(b.apr,0)