Tuesday, September 30, 2014

0 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.

image

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

image

Reference

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

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates