Play with NULLIF

  • Bhuvnesh

    SSC Guru

    Points: 59344

    Comments posted to this topic are about the item Play with NULLIF

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Oleg Netchaev

    SSCertifiable

    Points: 5268

    Cool question! Thank you. I think that nullif is one of the useful functions which is somewhat overlooked by many.

    Oleg

  • thecosmictrickster@gmail.com

    SSChampion

    Points: 10386

    Good question but just raises another for me - when would you use NULLIF? It may be useful, but I am struggling to see where you would use it. Why would you want something to return NULL if two values are equal? Is it just easier than using a CASE statement?



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • Niths

    Old Hand

    Points: 340

    Really challenging question Bhuvanesh.. 🙂 But still am not clear with the output.. u are comparing 2 expressions in ur query... one is int with value 0 and the other is null... both are of different value.. then how the output is null? null can be output only if the 2 values taken for comparison are equal rite??!!! can u plz explain this?

    ------------------------
    ~Niths~
    Hard Work never Fails 🙂

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4237

    Scott Duncan-251680 (5/2/2010)


    Good question but just raises another for me - when would you use NULLIF? It may be useful, but I am struggling to see where you would use it.

    The most common use case, in my experience, is avoiding division by 0 errors when calculating percentages, proportions, etc:

    SELECT SomeAmount * 100.0 / NullIf(SomeTotalAmount, 0) AS SomePercentage

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • Bhuvnesh

    SSC Guru

    Points: 59344

    Niths (5/2/2010)


    But still am not clear with the output.. u are comparing 2 expressions in ur query... one is int with value 0 and the other is null... both are of different value.. then how the output is null?

    in this question ' ' (blank) will be treated as INT type means 0

    .So 0 equal to 0 will give expected result

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • sharath.chalamgari

    SSCertifiable

    Points: 5680

    i think mostly we will be using this to avoid divide by ZERO error

  • iceman26

    SSC-Addicted

    Points: 428

    Didn't even know such a function existed.

    You learn something new everyday. Now I can go home 😀

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Scott Duncan-251680 (5/2/2010)


    Good question but just raises another for me - when would you use NULLIF? It may be useful, but I am struggling to see where you would use it. Why would you want something to return NULL if two values are equal? Is it just easier than using a CASE statement?

    Agreed - very good question. NULLIF is, in my opinion, one of the most under-apprecaited functions in SQL.

    Others have already pointed out that you can use it to avoid divide by zero errors. Another use is when a character column that is nullable has erroneously been populated with a mixture of NULLL and blank strings (instead of NULL). Now, if a report should list 'n/a' to represent the missing strings, you can use

    COALESCE(NULLIF(ColumnName, ''), 'n/a')

    The NULLIF changes empty strings to NULL (and keeps existing NULLs as they are); the COALESCE then replaces them all with 'n/a'.

    A third usse is for comparing string columns that are nullable with the requirement that two NULL values should be considered equal. WHERE Column1 = Column2 will miss the NULL pairs. The usual way to work around this is to use

    WHERE Column1 = Column2 OR (Column1 IS NULL AND Column2 IS NULL)

    This gets awkward if there are other requirements as well, because you need extra parentheses to seperate the OR from the AND, like this:

    WHERE Column18 > 27

    AND (Column1 = Column2 OR (Column1 IS NULL AND Column2 IS NULL))

    Using ISNULL can work around this - although the result is admittedly not trivial to understand:

    WHERE Column18 > 27

    AND NULLIF(Column1, Column2) IS NULL

    AND NULLIF(Column2, Column1) IS NULL

    The first NULLIF will return NULL if both columns are equal or Column1 is NULL; the second is NULL if both are equal or Column2 IS NULL. So they are only both NULL if the columns are equal or both are NULL.

    As to your last question - it's not just easier than a CASE expression (not statement!), it is in fact the same. NULLIF(expr1, expr2) is defined as shorthand for CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Niths

    Old Hand

    Points: 340

    Thanks a lot Bhuvnesh!! 🙂

    ------------------------
    ~Niths~
    Hard Work never Fails 🙂

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    Great question, thanks.

    Hugo, thanks for the additional information and insight.

  • Koen Verbeeck

    SSC Guru

    Points: 258924

    Great question. Learned two new things today:

    * nullif

    * how SQL converts an empty string to a numeric datatype.

    Nice one!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Cliff Jones

    SSChampion

    Points: 10517

    Thanks Hugo for the information, very useful.

  • Trey Staker

    SSCarpal Tunnel

    Points: 4736

    Good question, Thanks. I also learned a lot from the discussion, thanks Hugo for the explanation.

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • rjv_rnjn

    SSCrazy

    Points: 2963

    Good question. Thanks Hugo for the detailed explanation.

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply