sql server เปรียบเทียบระหว่าง NULLIF และ ISNULL

Explanation of NULLIF
Syntax:
NULLIF ( expression , expression )

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 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 good example of ISNULL from BOL:

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

Observation:
Interesting observation is NULLIF returns null if it comparison is successful, where as ISNULL 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?

Reference : Pinal Dave (http://www.SQLAuthority.com), BOL