Tuesday, September 30, 2014

1 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

1 comments:

  1. Massive post. Really good-looking blog. A lot of blogs, I observe these days don't really present anything that I'm interested in custom essay writing service .but I'm most definitely interest in this one. I am in reality happy with article quality and direction. This post is mark on in helpful how some thought apply to any script point. Thanks a lot for protection enormous things. I am enormously a good deal thankful used designed for this place.

    ReplyDelete

 

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