NULLIF returns NULL if both of the strings are equal else returns first string.
SELECT NULLIF(‘MakeFlag’, ‘MakeFlag’)AS ‘Null if Equal’
Output : NULL
SELECT NULLIF(‘FinishedGoodsFlag’, ‘MakeFlag’)AS ‘Null if Equal’ — FinishedGoodsFlag
NULLIF can be implemented using CASE statement. It will compare two parameters and return NULL if both expressions are same.
SELECT ‘Null if Equal’ =
WHEN ‘MakeFlag’ = ‘FinishedGoodsFlag’ THEN NULL
Output : FinishedGoodsFlag
ISNULL is used to replace value of expression, if it comes to NULL.
SELECT ISNULL(NULL, expression)
DECLARE @var varchar(50)
SELECT ISNULL(@var, ‘No value has been assigned to variable.’)
Output : No value has been assigned to variable.
ISNULL and NULLIF are exactly opposite to each other.
ISNULL returns NULL if comparison becomes successful.
On the other hand, NULLIF returns not null if comparison becomes successful.
Pawan Kumar / Gauri D