NULLIF function returns null value if the two specified expressions are equivalent.
NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.
NULLIF returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a null value of the type of the first expression.
Syntax of NULLIF Function :
NULLIF (expression1, expression2)
expression1 and expression2 are any valid sql server expression. It can be constant, column name, function, subquery, or any combination of arithmetic, bitwise, and string operators.
Return type of NULLIF function is same as expression1.
Examples of NULLIF Function :
Example 1 : Use of NULLIF function in select clause
SELECT ProductName, NULLIF(UnitsInStock,ReorderLevel) AS Stock
FROM Products
Output
ProductName Stock
Rogede sild NULL
Spegesild 95
Zaanse koeken 36
Chocolade 15
Scottish Longbreads NULL
Above example displays Null where both columns UnitsInStock and ReorderLevel values are same otherwise it displays value of UnitsInStock column.