NULLIF –
NULLIF returns NULL if both of the strings are equal else returns first string.
Syntax :
NULLIF(expression1, expression2)
e.g.
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.
e.g.
SELECT ‘Null if Equal’ =
CASE
WHEN ‘MakeFlag’ = ‘FinishedGoodsFlag’ THEN NULL
ELSE ‘FinishedGoodsFlag’
END
Output : FinishedGoodsFlag
ISNULL() –
ISNULL is used to replace value of expression, if it comes to NULL.
Syntax :
SELECT ISNULL(NULL, expression)
e.g.
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
Pawankkmr@hotmail.com
Improving my SQL BI Skills 
“ISNULL returns NULL if comparison becomes successful.
On the other hand, NULLIF returns not null if comparison becomes successful.”
is it correct? if it is correct statement could you please explain me by taking an example…. thank u
LikeLike
Yes you are correct ! Please find the examples below
–Example of ISNULL
–Here we are checking if the Input value is NULL, then we are placing it by Pawan
DECLARE @t AS VARCHAR(10) = NULL
SELECT ISNULL(@t, ‘Pawan’)
–Example of NULLIF, Here we are checking whether the input value is Pawan
–IF yes then we are returning NULL
DECLARE @t1 AS VARCHAR(10) = ‘Pawan’
SELECT NULLIF(@t1, ‘Pawan’)
LikeLike