Explanation of NULLIF
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.
NULLIF() Returns a null value if the two specified expressions are equal. If the Two expressions are not equal then it will return the first expression's value. Whether the returned value is NULL or NOT NULL, NULLIF() will return the same data type as the first expression
Explanation of ISNULL
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.
ISNULL() Replaces the NULL value with the specified expression value.
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.
Example 1 :
Select NULLIF(100,50*2) 'NULLIFValue'
Example 2 :
Select NULLIF(2*2,2*7) 'NULLIFValue'
Example 3 :
Select NULLIF(20-2,19) 'NULLIFValue'
Example 1 :
Select ISNULL(null,12) 'ISNULL VALUE'
Simply ISNULL replaces first argument with second argument if first argument value is NULL; otherwise first argument value.
NULLIF() return NULL if first & second arguments are equal; otherwise first argument value Sudhakar_A, if this helps please login to Mark As Answer. | Alert Moderator