What is the difference between ISNULL and COALESCE in a survey?

Question

I’m having second thoughts about using the ISNULL and COALESCE.

Currently creating a query in SQL Server, I was left with doubt about ISNULL and COALESCE, I did some research and I was able to find out the difference between them. Only I came up with some doubts I couldn’t find.

I realized the following query:

Doubts: When reading on some websites I saw that the ISNULL OR COALESCE there is some difference in the time of select on bench only I couldn’t figure out which one, because the two have the same behavior when performing the following query?

SELECT  'A' + ISNULL(NULLIF('abc', 'abc'), ' ') + 'A' AS Using_ISNULL,
Resultado: A A

SELECT  'A' + COALESCE(NULLIF('abc', 'abc'), ' ') + 'A' AS Using_ISNULL
Resultado: A A

Only on some websites they said ISNULL or COALESCE when string would look like this Ex: If Column Has Size VARCHAR(50)

SELECT  'A' + COALESCE OU ISNULL(NULLIF('abc', 'abc'), ' ') + 'A' AS Using_ISNULL
Resultado: A(ESPAÇO 48 "")A

that’s what I could understand, no other site showed examples for better understanding, I wanted to know the difference between the two in the bank question?

It got a little difficult to describe my doubt.

Answer 1

The main difference in functionality is that the COALESCE accepted n arguments, returning the first with no value NULL between them. The ISNULL accepts only two arguments, one possibly NULL, and another to return if the first is NULL.

For example, this can only be done with COALESCE:

SELECT COALESCE(col1, col2, col3, col4) AS valor;

This selects the first value that is not null between the four columns passed. I believe you already know this difference. In your example you only pass one argument, so under this aspect there is no difference between using one function or the other. But it is always good to remember that COALESCE is part of the standard SQL language, while the ISNULL doesn’t make.

Another difference (which is what matters to your question): the ISNULL returns the type of its first argument, while the COALESCE returns the type of higher precedence among the past. The strange results you show in the screenshot are a combination of this with the effect of NULLIF, which, according to the documentation (emphasis added):

NULLIF Returns the first Expression if the two Expressions are not Equal. If the Expressions are Equal, NULLIF Returns a null value of the type of the first Expression.

That is, the NULL that it returns is typed (never imagined it) based on the type of the first argument. In all examples, it would be a CHAR(3), VARCHAR(3) or something like that. It forces the cast of the second argument of ISNULL for the same type. For the value number, the type is incompatible and the result is *.