Post reply

NULLIF and 0

  • March 25, 2021 at 12:00 am

    #3855125

    Comments posted to this topic are about the item NULLIF and 0

  • March 25, 2021 at 12:26 pm

    #3861992

    ... Huh.

  • March 25, 2021 at 4:56 pm

    #3862081

    Fun question, although the explanation could've been clearer.  For anybody still confused:

    For NULLIF, the first expression determines the data type.  So in this case, everything is being implicitly converted to int since the 0's and 1's are interpreted as int data type.

    CAST('0' AS int) and CAST('' AS int) both return 0, so the 4 expressions are equivalent to:

    SELECT NULLIF(0,0)

    SELECT NULLIF(0,NULL)

    SELECT NULLIF(0,0)

    SELECT NULLIF(1,0)

    The NULLIF function returns NULL if the 2 arguments are equal, otherwise it just returns the first argument.  The first and third expressions are clearly equivalent expressions, so return NULL.  Since (0 = NULL) does not evaluate to true (it evaluates to NULL), the 2nd and 4th expressions return the first argument instead of a NULL.

    NULLIF is one of those functions that people who aren't familiar with it look at it and say "Why would anybody ever want to do this?"  However, the use case I like it for is avoiding divide-by-zero situations.  Rather than having conditional logic, you can divide by NULLIF(@var, 0) so that instead of dividing by 0 and throwing an error, it divides by NULL and returns NULL.  From there, you can wrap the entire expression in an ISNULL if you want, or just recognize that a NULL value in your results could indicate dividing by zero.

  • March 25, 2021 at 6:07 pm

    #3862109

    Good explanation, thanks.

  • March 26, 2021 at 12:59 am

    #3862252

    Thank you, NBSteve,

    Two points:

    • First - in fact NULLIF was more for a confusion. The punch point is that SQL Server converts empty string to 0 if implicit/explicit conversion is to an integer data type and it makes it "dangerous". Consider the example below. The join condition implicitly converts the empty string to 0 and the query returns 3 rows "instead of 2" as the "common sense" dictates 😉

    CREATE TABLE #Int (id int, val char(10));
    CREATE TABLE #Char (id char(10), val char(10));
    INSERT #Int VALUES (0, 'A'), (1, 'B'), (2, 'C');
    INSERT #Char VALUES ('', 'A'), ('1', 'B'), ('2', 'C');

    SELECT *
    FROM #Int
    JOIN #Char ON #Int.id = #Char.id

    • Second - NULLIF has another valuable (IMO) application. If you want to have NULLs everywhere when you have "non-defined" value (as empty string or 0) - NULLIF(<something>, '') will do the job.

    • This reply was modified 3 years, 8 months ago by  Peter Petrov.
    • This reply was modified 3 years, 8 months ago by  Peter Petrov. Reason: Typo

Viewing 5 posts - 1 through 4 (of 4 total)

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