What is the difference between IFNULL and COALESCE in MySQL?
MysqlSqlSql ServerMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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'
Solution 2 - Mysql
COALESCE
Pros of -
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'sISNULL
are limited versions ofCOALESCE
that can work with two arguments only.
COALESCE
Cons of -
Per Transact SQL documentation,
COALESCE
is just a syntax sugar forCASE
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 ofCOALESCE
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 ofCOALESCE
(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 evaluateexpr1
twice). - Usage within
GROUP BY
or with query expressions in c-treeACE. - Etc.
Solution 3 - Mysql
Differences in SQL-Server:
-
There is no
IFNULL()
function but a similarISNULL()
-
ISNULL
takes only 2 parameters whereas COALESCE takes variable number of parameters -
COALESCE
is based on the ANSI SQL standard whereasISNULL
is a proprietary TSQL function -
Validations for
ISNULL
andCOALESCE
is also different. For example,NULL
value forISNULL
is converted to int, whereas forCOAELSCE
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 theCASE
expression rules and returns type of value with highest precedence.
Solution 4 - Mysql
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
.
Solution 5 - Mysql
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)