MySQL中IFNULL和COALESCE有什么区别？

Question

SELECT IFNULL(NULL, 'Replaces the NULL')
--> Replaces the NULL

SELECT COALESCE(NULL, NULL, 'Replaces the NULL')
--> Replaces the NULL

在这两个语句中，主要区别在于参数传递。对于IFNULL函数，它有两个参数，而COALESCE函数有多个参数。除此之外，这两个函数还有其他的区别吗？

同时，请问在微软的SQL Server中，这两个函数有何不同？

- shree18

---

IFNULL 检查单个参数。 COALESCE 适用于 N 个参数。当您有未知数量的值需要检查时，COALESCE 很有用。当您选择列并且知道它可能为 null 但是想要用不同的值表示它时，IFNULL 是有用的。 因此，这两个函数是非常不同的。至于 MSSQL 的差异-谷歌可以帮助您，通过自己的研究可以更快地获取此信息。 - N.B.

@N.B.，针对“IFNULL检查单个参数。COALESCE与N个参数一起使用。”这种情况，当N = 2时，两者都适用。那么你会使用哪一个呢？ - Pacerier

Answer 1

两者的主要区别在于IFNULL函数需要两个参数，如果第一个参数不是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

而在 MSSQL 中，COALESCE 函数要求至少有一个参数不为 null，因此：

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'

- Aleks G

---

一个小细节：在MSSQL中，你不需要在COALESCE函数中至少有一个非空值。你不能只使用NULL常量，但所有的值都可以为空（例如在查询中）。所以这样是可以的：COALESCE(NULL, ValueWhichIsNULL)。演示：http://sqlfiddle.com/#!6/3f5c7/2/0 - Tim Schmelter

Answer 2

COALESCE的优点

• COALESCE是SQL标准函数。

  而IFNULL是MySQL特定的，它在MSSQL中的等效函数(ISNULL)也是MSSQL特定的。

• COALESCE可以使用两个或更多参数(实际上，它可以使用单个参数，但在这种情况下相当无用：COALESCE(a) ≡ a)。

  而MySQL的IFNULL和MSSQL的ISNULL都是COALESCE的有限版本，只能使用两个参数。

COALESCE的缺点

• 根据Transact SQL documentation，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是一个普通函数，永远不会对其参数进行多次评估。MySQL和PostgreSQL中的COALESCE也不会对其参数进行多次评估。

• 目前，我不知道SQL标准如何定义COALESCE。

  正如我们从上一个观点中看到的那样，RDBMS中的实际实现有所不同：一些（例如MSSQL）使COALESCE对其参数进行多次评估，一些（例如MySQL、PostgreSQL）则没有。声称其COALESCE实现符合SQL-92的c-treeACE表示：“此函数不允许在GROUP BY子句中使用。此函数的参数不能是查询表达式。”我不知道这些限制是否真的在SQL标准内；大多数实际实现的COALESCE（例如MySQL、PostgreSQL）没有这些限制。作为普通函数，IFNULL/ISNULL也没有这些限制。

简历

除非您在特定的RDBMS中面临COALESCE的具体限制，我建议始终使用COALESCE作为更标准和更通用的方法。

以下是例外情况：

• 在MSSQL中计算长表达式或具有副作用的表达式（因为根据文档，COALESCE（expr1，…）可能会评估两次expr1）。
• 在c-treeACE中使用GROUP BY或查询表达式。
• 等等。

- Sasha

---

可能还存在一些技术上的数据库/驱动程序特定差异。例如，MySQL的ODBC驱动程序会导致IFNULL(datetime1, datetime2)表达式返回System.DateTime，但是COALESCE(datetime1, datetime2)则返回System.Byte[]（至少在某些版本中，尽管这看起来像是一个错误）。 - Sasha

Answer 3

SQL-Server中的差异：

• 没有IFNULL()函数，但有类似的ISNULL()函数。

• ISNULL只有两个参数，而COALESCE可以有多个参数。

• COALESCE基于ANSI SQL标准，而ISNULL是专有的TSQL函数。

• ISNULL和COALESCE的验证也不同。例如，ISNULL中的NULL值会转换为int类型，而对于COALESCE，您必须提供一个类型。例如：

  • ISNULL(NULL,NULL)：返回int。

  • COALESCE(NULL,NULL)：会抛出错误。

  • COALESCE(CAST(NULL as int),NULL)：有效并返回int。

• 结果表达式的数据类型确定 - ISNULL使用第一个参数类型，COALESCE遵循CASE表达式规则，并返回具有最高优先级值的类型。

- praba

---

你错了。COALESCE(NULL, NULL)不会抛出错误 - 它将返回NULL。此外，OP正在询问IFNULL函数，而不是ISNULL - 请仔细阅读问题。哦，还有，NULL永远不会转换为int。 - Aleks G

@AleksG praba正在回答有关SQL-Server的问题，其中COALESCE(NULL,NULL)确实会引发错误：SQL-Fiddle - ypercubeᵀᴹ

他需要在回答中明确表达。OP的问题标题是“mysql中ifnull和coalesce的区别”，你看到了SQL Server吗？ - Aleks G

@AleksG 我看到一个 [sql-server] 标签和最后一个问题：“它在 MSSql 中的区别是什么”。但你是对的，回答者需要澄清答案仅适用于 SQL-Server。 - ypercubeᵀᴹ

@praba AleksG是正确的。在MySql中，COALESCE(NULL,NULL)不会抛出任何错误。 - shree18

Answer 4

ifnull 只能替换第一个参数的空值。而 coalesce 可以将任何值替换为另一个值。在标准 SQL 中，使用 coalesce 可以转换多个值并具有多个参数。

根据下面的注释编辑示例。

示例： coalesce(null, null, null, 'b*', null, 'null*') 返回 'b*'，使用 ifnull 不可能实现。

- jboi

---

关于您上面提到的RDBMS，结果在MySQL中是“b”而不是“b*”。 - shree18

好的，我已经删除了我的评论。 - ypercubeᵀᴹ

Answer 5

这个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)

- user8546027