«
»

T-SQL: NULLIF and ISNULL

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:

This entry was posted on May 7, 2011 at 9:52 pm and is filed under DB & SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 Responses to “T-SQL: NULLIF and ISNULL”

  1. x Says:
    May 22, 2011 at 11:10 pm | Reply

    would be clearer if you provide some basic examples too

  2. robertoschiabel Says:
    May 28, 2011 at 3:52 pm | Reply

    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

  3. Luděk Štípal Says:
    October 19, 2012 at 9:36 am | Reply

    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

Leave a comment