SQL COALESCE function vs CASE and ISNULL

 

    Coalesce function has been with the SQL Server since 2008 version. It works just like CASE statement and ISNULL function. It evaluates the arguments in order and return the current value of the first expression that initially does not have NULL value.

COALESCE (expression, [, expressions])

Coalesce function returns the first expression in the expression list which is not NULL. Let's look at the following example to understand it better. All we are saying here is, return RetailPrice's value if it is not null, If RetailsPrice is null then return InvoicePrice if its value is not null. If that fails too then return 0.

We can convert the previous COALESCE example to CASE statement, and it will work the same exact way.COALESCE is really CASE statements shortcut.

        What about the ISNULL function, It does something very similar to example too. Only difference is  ISNULL function tests only 1 value, If the test value is NULL then it returns whatever you decide for it to return. If Retail's price is null then it returns 0

      I am sure you are asking yourself right now. Which one is better or which one is faster than the other one? There are a lot of blogs out there comparing these functions performances. In my experience, there is no significant performance difference among them. If you need to compare only one value, I would go with ISNULL() function. If you need to compare more than one expression you can use COALESCE since you need to write less code. There is nothing wrong with using CASE statement either.