What is the difference between IFNULL and COALESCE in MySQL?

Question

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 IFNULL it's two parameters and for COALESCE it's multiple parameters. So except that, do we have any other difference between these two?

And how it differs in MS SQL?

Japonica

IFNULL checks a single argument. COALESCE works with N arguments. COALESCE is useful when you have unknown number of values that you want to check. IFNULL is useful when you select columns and know that it can be null but you want to represent it with a different value. Therefore, the two functions are vastly different. As for MSSQL difference - google can help you, you can obtain this information much quickly by doing your own research. – Exterminate

@N.B., Referring to "IFNULL checks a single argument. COALESCE works with N arguments. ", for the case where N = 2, both works. Which will you use then? – Sanson

Answer 1

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

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

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 IFNULL function but instead ISNULL function, which needs to know the types of the arguments. Therefore:

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

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

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

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'

Exsiccate

One nitpick: you don't need at least one non-null value in COALESCE in MSSQL. You cannot use only NULL constants but all values can be null(f.e. in a query). So this is ok: COALESCE(NULL, ValueWhichIsNULL). Demo: sqlfiddle.com/#!6/3f5c7/2/0 – Cowslip

Answer 2

Pros of COALESCE

• COALESCE is SQL-standard function.

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

• COALESCE can 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 IFNULL and MSSQL's ISNULL are limited versions of COALESCE that can work with two arguments only.

Cons of COALESCE

• Per Transact SQL documentation, COALESCE is just a syntax sugar for CASE and 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 COALESCE in MSSQL.

  On the other hand, ISNULL in MSSQL is a normal function and never evaluates its arguments more than once. COALESCE in 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 COALESCE to evaluate its arguments more than once, some (e.g. MySQL, PostgreSQL) — don't.

  c-treeACE, which claims it's COALESCE implementation 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.

Resume

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

The exceptions are:

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

Zoogeography

There may be also some technical database/driver-specific differences. For example ODBC driver for MySQL causes IFNULL(datetime1, datetime2) expression to return System.DateTime, but COALESCE(datetime1, datetime2) — System.Byte[] (at least in some versions, though it looks like a bug, of course). – Zoogeography

Answer 3

Differences in SQL-Server:

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

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

• COALESCE is based on the ANSI SQL standard whereas ISNULL is a proprietary TSQL function

• Validations for ISNULL and COALESCE is also different. For example, NULL value for ISNULL is converted to int, whereas for COAELSCE you 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 – ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence.

Swetiana

You are wrong. COALESCE(NULL, NULL) will not throw an error - it will return NULL. Further, the OP is asking about IFNULL function, not ISNULL - please read the question carefully. Oh, and NULL is never converted to an int. – Exsiccate

@AleksG praba is answering about SQL-Server, where COALESCE(NULL,NULL) will indeed throw an error: SQL-Fiddle – Bascule

@ypercube Then he needs to make it clear in his answer. The OP's question's title is What is the difference bewteen ifnull and coalesce in mysql? - do you see sql-server there? – Exsiccate

@AleksG I see an [sql-server] tag and the last question: "And how it differs in MSSql." But you are right, the answerer needs to clarify that the answer applies to SQL-Server only. – Bascule

@Swetiana AleksG is right.In MySql COALESCE(NULL,NULL) is not throwing any error. – Japonica

Answer 4

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

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.

Moschatel

Refering to which rdbms you have given above example.The result is "b" not "b*" in MySql – Japonica

OK, removed my comments. – Bascule

Answer 5

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

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)

Ovolo