NULLIF Function

NULLIF function in sql server with examples.

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 


ProductName             Stock
Rogide soild                 NULL
Speagesild                    95
Zanse koeken               36
Cho colade                   15
Scottish                        NULL

Above example displays Null where both columns UnitsInStock and ReorderLevel values are same otherwise it displays value of UnitsInStock column.


No comments: