MySQL mysql中的ifnull和coalesce有什么区别？

What is the difference bewteen ifnull and coalesce in mysql?

提问by shree18

select ifnull(null,'replaces the null')
-->replaces the null

select coalesce(null,null,'replaces the null')
-->replaces the null

In both clauses the main difference is argument passing. For ifnullit's two parameter and coalesce 2 or 3 we can pass,except that do we have any other difference between these two? And how it differs in MSSql.

在这两个子句中，主要区别在于参数传递。对于ifnull它的两个参数和合并 2 或 3 我们可以传递，除了这两者之间还有其他区别吗？以及它在 MSSql 中的不同之处。

回答by Aleks G

The main difference between the two is that IFNULLfunction takes two arguments and returns the first one if it's not NULLor the second if the first one is NULL.

两者之间的主要区别是，IFNULL函数有两个参数，返回的第一个，如果它不是NULL，或者如果第一个是第二NULL。

COALESCEfunction can take two or more parameters and returns the first non-NULL parameter, or NULLif all parameters are null, for example:

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'

UPDATE:MSSQL does stricter type and parameter checking. Further, it doesn't have IFNULLfunction but instead ISNULLfunction, which needs to know the types of the arguments. Therefore:

更新：MSSQL 进行更严格的类型和参数检查。此外，它没有IFNULL函数，而是ISNULL函数，它需要知道参数的类型。所以：

SELECT ISNULL(NULL, NULL);
-> results in an error

SELECT ISNULL(NULL, CAST(NULL as VARCHAR));
-> returns NULL

Also COALESCEfunction in MSSQL requires at least one parameter to be non-null, therefore:

COALESCEMSSQL 中的函数也要求至少一个参数为非空，因此：

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'

回答by Sasha

Pros of COALESCE

的优点 COALESCE

• COALESCEis SQL-standard function.

  While IFNULLis MySQL-specific and its equivalent in MSSQL (ISNULL) is MSSQL-specific.

• COALESCEcan work with two or more arguments(in fact, it can work with a single argument, but is pretty useless in this case: COALESCE(a)≡a).

  While MySQL's IFNULLand MSSQL's ISNULLare limited versions of COALESCEthat can work with two arguments only.

• COALESCE是 SQL 标准函数。

  虽然IFNULL是特定于 MySQL 的，它在 MSSQL 中的等价物 ( ISNULL) 是特定于 MSSQL 的。

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

  虽然 MySQLIFNULL和 MSSQLISNULL是有限版本，COALESCE但只能使用两个参数。

Cons of COALESCE

的缺点 COALESCE

• Per Transact SQL documentation, COALESCEis just a syntax sugar for CASEand can evaluate its arguments more that once. In more detail: COALESCE(a1, a2, …, aN)≡CASE WHEN (a1 IS NOT NULL) THEN a1 WHEN (a2 IS NOT NULL) THEN a2 ELSE aN END. This greatly reduces the usefulness of COALESCEin MSSQL.

  On the other hand, ISNULLin MSSQL is a normal function and never evaluates its arguments more than once. COALESCEin MySQL and PostgreSQL neither evaluates its arguments more than once.

• At this point of time, I don't know how exactly SQL-standards define COALESCE.

  As we see from previous point, actual implementations in RDBMS vary: some (e.g. MSSQL) make COALESCEto evaluate its arguments more than once, some (e.g. MySQL, PostgreSQL) — don't.

  c-treeACE, which claims it's COALESCEimplementation is SQL-92 compatible, says: "This function is not allowed in a GROUP BY clause. Arguments to this function cannot be query expressions." I don't know whether these restrictions are really within SQL-standard; most actual implementations of COALESCE(e.g. MySQL, PostgreSQL) don't have such restrictions. IFNULL/ISNULL, as normal functions, don't have such restrictions either.

• 每个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. 这大大降低了COALESCE在 MSSQL 中的用处。

  另一方面，ISNULL在 MSSQL 中是一个普通函数，并且不会多次评估其参数。COALESCE在 MySQL 和 PostgreSQL 中，不会多次评估其参数。

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

  正如我们从前一点所看到的，RDBMS 中的实际实现各不相同：有些（例如 MSSQL）会COALESCE多次评估其参数，有些（例如 MySQL、PostgreSQL）则不会。

  声称其COALESCE实现与 SQL-92 兼容的c-treeACE说：“在 GROUP BY 子句中不允许使用此函数。此函数的参数不能是查询表达式。” 我不知道这些限制是否真的符合 SQL 标准；大多数实际实现COALESCE（例如 MySQL、PostgreSQL）没有这样的限制。IFNULL/ ISNULL，作为普通函数，也没有这样的限制。

Resume

恢复

Unless you face specific restrictions of COALESCEin specific RDBMS, I'd recommend to always use COALESCEas more standard and more generic.

除非您COALESCE在特定 RDBMS 中面临特定限制，否则我建议始终使用COALESCE更标准和更通用的方式。

The exceptions are:

例外情况是：

• Long-calculated expressions or expressions with side effects in MSSQL (as, per documentation, COALESCE(expr1, …)may evaluate expr1twice).
• Usage within GROUP BYor with query expressions in c-treeACE.
• Etc.

• MSSQL 中长期计算的表达式或具有副作用的表达式（根据文档，COALESCE(expr1, …)可能会计算expr1两次）。
• GROUP BY在 c-treeACE 中的查询表达式内或与查询表达式一起使用。
• 等等。

回答by praba

Differences in SQL-Server:

SQL-Server 的差异：

• There is no IFNULL()function but a similar ISNULL()

• ISNULLtakes only 2 parameters whereas COALESCE takes variable number of parameters

• COALESCEis based on the ANSI SQL standard whereas ISNULLis a proprietary TSQL function

• Validations for ISNULLand COALESCEis also different. For example, NULLvalue for ISNULLis converted to int, whereas for COAELSCEyou have to provide a type. Ex:

  • ISNULL(NULL,NULL): is int.

  • COALESCE(NULL,NULL): will throw an error.

  • COALESCE(CAST(NULL as int),NULL): is valid and returns int.

• Data type determination of the resulting expression – ISNULLuses the first parameter type, COALESCEfollows the CASEexpression rules and returns type of value with highest precedence.

• 没有IFNULL()功能，但类似ISNULL()

• ISNULL只需要 2 个参数，而 COALESCE 需要可变数量的参数

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

• 为验证ISNULL和COALESCE也不同。例如，NULL值 forISNULL被转换为 int，而 forCOAELSCE你必须提供一个类型。前任：

  • ISNULL(NULL,NULL): 是整数。

  • COALESCE(NULL,NULL): 会报错。

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

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

回答by jboi

ifnullcan only replace a null value of the first parameter. Whereas coalescecan replace any value with another value. With coalescein standard SQL you can have many parameters transforming many values.

ifnull只能替换第一个参数的空值。而coalesce可以用另一个值替换任何值。随着coalesce标准的SQL，你可以有很多的参数改变了许多价值。

EDIT the example according to comments below.

根据下面的评论编辑示例。

Example: coalesce(null, null, null, 'b*', null, 'null*')returns 'b*' and it is not possible to do with ifnull.

示例：coalesce(null, null, null, 'b*', null, 'null*')返回 '​​b*' 并且无法使用ifnull.

回答by user8546027

This db2 SQL will not work with COALESE, I will not see any rows retrieved. Since I used IFNULL it is working as expected

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