They server two completely different functions.
ISNULL(value) is a boolean operator that returns 1 or 0 depending on whether or not the value passed in is null. It can be used in if and case statements to determine logic flow. Example:
SELECT OrderID,
CASE
WHEN ISNULL(AmountDue) THEN "Paid in full"
WHEN DATE(DueDate) < date(NOW()) THEN "Order is past due"
ELSE CONCAT("Order is due on ", CONVERT(VARCHAR, DueDate, 121))
END
FROM OrderDetails;
IFNULL(value1, value2) is used to handle a situation where you want one value, unless it is null, in which case you want a second value. Example:
select IFNULL(MiddleName, "No Middle Name") from customers
They server two completely different functions.
ISNULL(value) is a boolean operator that returns 1 or 0 depending on whether or not the value passed in is null. It can be used in if and case statements to determine logic flow. Example:
SELECT OrderID,
CASE
WHEN ISNULL(AmountDue) THEN "Paid in full"
WHEN DATE(DueDate) < date(NOW()) THEN "Order is past due"
ELSE CONCAT("Order is due on ", CONVERT(VARCHAR, DueDate, 121))
END
FROM OrderDetails;
IFNULL(value1, value2) is used to handle a situation where you want one value, unless it is null, in which case you want a second value. Example:
select IFNULL(MiddleName, "No Middle Name") from customers
In MySQL, ISNULL simply checks if a value is null or not. IFNULL acts like COALESCE and will return the 2nd value if the first value is null.
Are you perhaps mistaking ISNULL in SQL Server as the one in MySQL? In SQL Server ISNULL is the same as IFNULL in MySQL.
Whenever I need a different value if a column is null, I use the isnull function because, well, that's why it's there, right?
A friend of mine, however, swears by the 'if <column> is null, ..' syntax. As far as I can tell, this is the same thing, it's just a different syntax.
However, different syntax can mean that other things are happening under water, so I became curious as to what the difference was, but was unable to find it on google :/
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'
Pros of 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'sISNULLare limited versions ofCOALESCEthat can work with two arguments only.
Cons of COALESCE
Per Transact SQL documentation,
COALESCEis just a syntax sugar forCASEand 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 ofCOALESCEin 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 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 evaluateexpr1twice). - Usage within
GROUP BYor with query expressions in c-treeACE. - Etc.