SQL SERVER – Explanation and Comparison of NULLIF and ISNULL

Syntax: NULLIF ( expression , expression )

SQL SERVER - Explanation and Comparison of NULLIF and ISNULL null-500x259

Returns a null value if the two specified expressions are equal. NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression. NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.

Following is a good example of NULLIF and CASE from BOL:

USE AdventureWorks;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,
NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal'
FROM Production.Product
WHERE ProductID < 10;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' =
CASE
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
ELSE MakeFlag
END
FROM Production.Product
WHERE ProductID < 10;
GO

Explanation of ISNULL
Syntax:

ISNULL ( check_expression , replacement_value )

Replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

Following is a good example of ISNULL from BOL:

USE AdventureWorks;
GO
SELECT AVG(ISNULL(Weight, 50))
FROM Production.Product;
GO

Observation:
An interesting observation is NULLIF returns null if it comparison is successful, whereas it returns not null if its comparison is successful. In one way they are opposite to each other.

Puzzle:
How to create infinite loop using NULLIF and ISNULL? If this is even possible?

Sidenote: In the SQL Server world, I have seen lots of people getting confused with NULL. They think null is nothing, or null is undefined or is null infinite. Actually, none of the answer is true. Null has been just Null. You can’t compare the null with any other value. This is the reason when we compare null with any other value we get the answer as null. Additionally, when we compare null to null, we get false results because there is no way to identify NULL with any value.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQLAuthority.com News – iGoogle Gadget Published
Next Post
SQL SERVER – 2005 Row Overflow Data Explanation

Related Posts

30 Comments. Leave new

  • Pinal, your entire blog is fantastic. You deserve every bit of praise you have ever received for your work. Thank you.

    Reply
  • select 1/2 returning 0 instead of 0.5

    Reply
    • The results will use the precision of the highest precision and rounds it nearer to ZERO. Thus you get 0.
      To get the result as 0.5, you need to increase the precision of one of them. Try any of the following:
      SELECT 1.0/2
      SELECT 1/2.0
      SELECT CAST(1 AS DECIMAL)/2
      SELECT 1/CAST(2.0 AS DECIMAL)

      Reply
      • Eldo – Thanks for helping Praveen

      • For the above code the result will be 0.500000, 0.5000000000000000000

        If we want to get exactly 0.5
        we can use the following:

        Select Convert(Float,1)/2
        Select 1/Convert(Float,2)

  • on sql server 2008r2 (sp1) and sql server 2012

    SELECT
    PERCENTAGE = CAST (((ISNULL(ACTUALMARKS,0) + ISNULL(ADJUSTMARKS,0) + ISNULL(GRACEMARKS,0)) / nullif(A.MAXMARKS , 0)) * 100 AS DECIMAL(9,2)) ,
    OBTMARKS = ISNULL(ACTUALMARKS,0) + ISNULL(ADJUSTMARKS,0) + ISNULL(GRACEMARKS,0)
    FROM EPEXAMREGPAPER A, EPEXAMREG B
    WHERE B.EXAMID = ‘S1501’
    AND A.REGISTRATIONID = B.REGISTRATIONID
    AND [STATUS] ‘ABSENT’
    AND A.ACTIVE=’Y’ AND B.ACTIVE=’Y’ AND B.CANDIDATECHOICEID’C’;

    Select statement works fine returning 1123 rows

    but when we update it gives divide by zero error

    how ever with same data update and select works fine on sql server 2008r2 (rtm)

    Reply
  • Shashanka Aithal P
    May 17, 2016 5:39 pm

    Hi,

    I will Create two variable and not assigning any values if I compare means it will
    take memory address of two variable for compare or what.Please help me for this issues.

    Reply
  • Your blogs were awasome

    Reply
  • Hello Pinal / others,

    one question: why am getting NULL to following:

    Declare @A INT;
    SELECT NULLIF(@A, 2);

    am getting NULL.
    So the NULLIF operator works based on first param only?
    I had an expectation as both the parameter values are different it will take the Not Null value (second parameter value) as result. But it didn’t mind the second param!
    So should I lean NULLIF as: it returns NULL if both values are same; if first param is not null then it returns the first value. it won’t mind the second param if first value is Null.
    Am I correct?

    Reply

Leave a Reply

Menu