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? 😉

Advertisements

Tags:

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

  1. x Says:

    would be clearer if you provide some basic examples too

  2. robertoschiabel Says:

    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:

    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 Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: