2 COALESCE and ISNULL

COALESCE and ISNULL are both for NULL evaluation. In many ways they serve the same purpose but they are also difference.

Use [AdventureWorks2014]

GO

-- Result for ISNULL and COALESCE are the same as below

select FirstName, 

    ISNULL(MiddleName,'Not Available') AS 'IsNULL_MiddleName',

    COALESCE(MiddleName, 'Not Available') AS 'COALESCE_MiddleName'

FROM Person.Person

--- http://msdn.microsoft.com/en-us/library/ms190349.aspx

SELECT Name, Class, Color, ProductNumber,

COALESCE(Class, Color, ProductNumber) AS FirstNotNull

FROM Production.Product;

The first example we can see they both return the same.

From 2nd Query, the COALESCE can take multiple columns and it will return the first not Not NULL

Reference

http://msdn.microsoft.com/en-us/library/ms190349.aspx