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

Question

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

Navigation

#1 by (25 votes)

28

I'm not sure how to use ISNULL and COALESCE .

Currently when I create a query in SQL Server, I have doubts about ISNULL and COALESCE , I did some research and I was able to discover the difference of both. Only that I came up with some doubts that I could not find.

I made the following query :

Questions: WhenIreadonsomesites,IsawthatISNULLORCOALESCEhasanydifferenceinthetimeofselectinbank butIcouldnotfigureoutwhichone,becausethetwohavethesamebehaviorwhenperformingthefollowingquery?

SELECT'A'+ISNULL(NULLIF('abc','abc'),'')+'A'ASUsing_ISNULL,Resultado:AASELECT'A'+COALESCE(NULLIF('abc','abc'),'')+'A'ASUsing_ISNULLResultado:AA

OnlysomesitessaidISNULLorCOALESCEwhenstringwouldlooklikethisEx:IfthecolumnhasthesizeVARCHAR(50)

SELECT'A'+COALESCEOUISNULL(NULLIF('abc','abc'),'')+'A'ASUsing_ISNULLResultado:A(ESPAÇO48"")A

This was what I could understand, but no site showed examples for better understanding, I wanted to know What is the difference of both in the question in bank?

It was a bit difficult to describe my doubt.

sql sql-server tsql

asked by anonymous 25.03.2015 / 15:56

1 answer

Why is it important to inform users of the cookie policy? What is the signature of a method?

score 25 · Accepted Answer

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

For example, this can only be done with COALESCE :

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

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

Another difference (which is what matters to your question): ISNULL returns the type of its first argument, while COALESCE returns the highest precedence type among the passed. The strange results you see 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 to null value of the type of the first expression .

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