– In general it is accepted that ISNULL is slightly faster than COALESCE.
– COALESCE is ANSI compliant, so COALESCE will not require any rework if you are changing your RDBMS, e.g: SQL Server to Oracle.
– COALESCE accepts more than two parameters / expressions whereas ISNULL accepts only two. So in order to compare more than two parameters using ISNULL we have to write nested expressions.
SELECT ISNULL(ISNULL(NULL, ‘MFS’), ‘Devi’) — Will return MFS and here we are compairing NULL, MFS, Devi.
So in this case COALESCE will be helpful and easier.
– ISNULL will change the return value’s data type to the data type of first argument. But COALESCE will promotes its arguments to the highest data type among compatable argument.
– It accepts two parameters and if they are equal, it returns a NULL else it returns the first parameter.
– It is similar to:
WHEN parameter1 = parameter2 THEN NULL
If a Database is polluted with N/A, blank or other values where it should contain nulls, we can use NULLIF() to replace the inconsistent values with nulls and clean the database.