SQL - Difference Between Coalesce and Isnull

SQL - Difference between COALESCE and ISNULL?

Comparing COALESCE and ISNULL
The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.
Because ISNULL is a function, it is evaluated only once. As described above,
the input values for the COALESCE expression can be evaluated multiple
times.
Data type determination of the resulting expression is
different. ISNULL uses the data type of the first parameter, COALESCE
follows the CASE expression rules and returns the data type of value
with the highest precedence.
The NULLability of the result expression is different for ISNULL and COALESCE. The
ISNULL return value is always considered NOT NULLable (assuming the return value is a
non-nullable one) whereas COALESCE with non-null parameters is
considered to be NULL. So the expressions ISNULL(NULL, 1) and
COALESCE(NULL, 1) although equivalent have different nullability
values. This makes a difference if you are using these expressions in
computed columns, creating key constraints or making the return value
of a scalar UDF deterministic so that it can be indexed as shown in
the following example.

> USE tempdb; 
> GO

> -- This statement fails because the PRIMARY KEY cannot accept NULL values
> -- and the nullability of the COALESCE expression for col2 
> -- evaluates to NULL. 

> CREATE TABLE #Demo  (  col1 integer NULL,  col2 AS COALESCE(col1, 0) PRIMARY KEY,  col3 AS ISNULL(col1, 0)  ); 
> 
> -- This statement succeeds because the nullability of the 
> -- ISNULL function evaluates AS NOT NULL.
> 
> CREATE TABLE #Demo  (  col1 integer NULL,  col2 AS COALESCE(col1, 0), 
> col3 AS ISNULL(col1, 0) PRIMARY KEY  );

Validations for ISNULL and
COALESCE are also different. For example, a NULL value for ISNULL is
converted to int whereas for COALESCE, you must provide a data type.
ISNULL takes only 2 parameters whereas COALESCE takes a variable
number of parameters.

Source: BOL

coalesce VS isnull

This is from https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql

Comparing COALESCE and ISNULL
The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.

Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equivalent, have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed.

Difference between ISNULL and COALESCE in T-SQL

It is valid query and will return INT:

SELECT ISNULL(NULL, NULL);

It will fail:

SELECT COALESCE(NULL, NULL);

At least one of the arguments to COALESCE must be an expression that
is not the NULL constant.

It is valid query and will return INT:

 SELECT COALESCE(CAST(NULL AS INT), NULL);

You can check metadata for resultsets:

DEMO

SELECT 'ISNULL',name, system_type_name
FROM sys.dm_exec_describe_first_result_set ('SELECT ISNULL(NULL, NULL)', NULL,0)
UNION ALL
SELECT 'COALESCE', name, system_type_name
FROM sys.dm_exec_describe_first_result_set (N'SELECT COALESCE(CAST(NULL as FLOAT), NULL);', NULL,0)

What is the difference between IFNULL and COALESCE in 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'

what is the difference between Coalesce and nullif

They are more or less unrelated.

coalesce() takes a list of values and returns the first non-null value (or null if all values are null).

nullif() takes two values and returns the first value, except it returns null if the values are equal.

If the two were converted to case statements, they would be:

coalesce:

case
    when value1 is not null then value1
    when value2 is not null then value2
    ... etc
    else null
end

nullif:

case when value1 = value2 then null else value1 end

Using ISNULL vs using COALESCE for checking a specific condition?

This problem reported on Microsoft Connect reveals some differences between COALESCE and ISNULL:

an early part of our processing rewrites COALESCE( expression1, expression2 ) as CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END. In [this example]:
COALESCE ( ( SELECT Nullable
             FROM Demo
             WHERE SomeCol = 1 ), 1 )
we generate:
SELECT CASE
          WHEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) IS NOT NULL
          THEN (SELECT Nullable FROM Demo WHERE SomeCol = 1)
          ELSE 1
       END
Later stages of query processing don't understand that the two subqueries were originally the same expression, so they execute the subquery twice...

One workaround, though I hate to suggest it, is to change COALESCE to ISNULL, since the latter doesn't duplicate the subquery.

Unexpected behavior of binary conversions (COALESCE vs. ISNULL)

This is not a bug. They're documented to handle data type precedence differently. COALESCE determines the data type of the output based on examining all of the arguments, while ISNULL has a more simplistic approach of inspecting only the first argument. (Both still need to contain values which are all compatible, meaning they are all possible to convert to the determined output type.)

From the COALESCE topic:

Returns the data type of expression with the highest data type precedence.

The ISNULL topic does not make this distinction in the same way, but implicitly states that the first expression determines the type:

replacement_value must be of a type that is implicitly convertible to the type of check_expression.

I have a similar example (and describe several other differences between COALESCE and ISNULL) here. Basically:

DECLARE @int int, @datetime datetime;
SELECT COALESCE(@int, CURRENT_TIMESTAMP);

-- works because datetime has a higher precedence than the chosen output type, int

2020-08-20 09:39:41.763

GO
DECLARE @int int, @datetime datetime;
SELECT ISNULL(@int, CURRENT_TIMESTAMP);

-- fails because int, the first (and chosen) output type, has a lower precedence than datetime
Msg 257, Level 16, State 3
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

Coalesce(), ISNULL() clarification

The biggest difference is that ISNULL is proprietary, while COALESCE is part of SQL standard. Certification course may be teaching to maximum portability of knowledge, so when you have several choices, the course prefers a standard way of solving the problem.

The other difference that may be important in this situation is the data type determination. ISNULL uses the type of the first argument, while COALESCE follows the same rules as CASE, and picks the type with higher precedence. This may be important when Daily_Rate is stored in a column with narrower range.

For completeness, here is a list of other differences between the two (taken from Microsoft SQL Server Blog):

The NULLability of result expression is different,
Validations for ISNULL and COALESCE is different, because NULL value for ISNULL is converted to int, but triggers an error with COALESCE
ISNULL takes only two parameters whereas COALESCE takes variable number of parameters
You may get different query plans for the two functions.

EDIT : From the way the answer is worded I think that the authors want you to use ISNULL in situations when the second argument is guaranteed to be non-NULL, e.g. a non-nullable field, or a constant. While generally this idea is sound, their choice of question to test it is not ideal: the issue is that the problem guarantees that the value of the second ISNULL parameter is non-NULL in situations when it matters, making the two choices logically equivalent.

COALESCE and REPLACE Function

You can use the isnull function to replace null values:

replace(isnull(clients.CLIENT_ID_2, ''),'-','') as PatientSSN

The coalesce function can also be used in the same way. The only difference between isnull and coalesce is that isnull is specially intended to replace a null value so it only takes two parameter.

You can also use a case, then it would be:

case
  when clients.client_id_2 is null then ''
  else replace(clients.CLIENT_ID_2,'-','')
end as PatientSSN,

SQL - Difference Between Coalesce and Isnull