Well , NULLIF and ISNULL may lookup similar, expacially working with several platforms at the same time. 😦
In T-SQL they are not equivalent, at all!
- NULLIF: Returns a null value if the two specified expressions are equal.
- ISNULL: Replaces NULL with the specified replacement value.
Is everything clear now? 😉
Tags: SQLServer
May 22, 2011 at 11:10 pm |
would be clearer if you provide some basic examples too
May 28, 2011 at 3:52 pm |
MSDN has clearfull samples
* ISNULL: http://msdn.microsoft.com/en-us/library/ms184325.aspx
SELECT Name, ISNULL(City, ‘_missing_city_’) FROM Users
provide some a default value whener no city has been provided
* NULLIF: http://msdn.microsoft.com/en-us/library/ms177562.aspx
SELECT ProductID, NULLIF(TotalPreviousYear, TotalCurrentYear) FROM v_ProductSellByYear
provide NULL if values are equal,
it’s usefull for extra info columns when coupled with ISNULL,
simpler SQL then CASE WHEN
hope it helps
October 19, 2012 at 9:36 am |
I am using it in conditional clause for JOIN … ON …
tblCommodityOrderItem oi with(nolock)
LEFT JOIN tblCommodity c with(nolock)
ON SUBSTRING(oi.sCode, 1, ISNULL(NULLIF(CHARINDEX(‘-‘, oi.sCode) -1, -1), LEN(oi.sCode))) = c.sCode