What is the difference bewteen 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?

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'