Diff ISNULL & NULLIF

ISNull :

Requires 2 arguments, check if first argument is NULL then replace NULL value with second argument , exactly works as :

Select ISNULL (FirstArgument, SecondArgumanet)

NULLIF :

Returns a null value if the two specified expressions are equal.

NULLIF ( expression1 , expression2 )

Like, When expression1 = expression2 Then Null Else FirstAgument End

Difference between NULLIF and ISNULL

Sample data

 CREATE TABLE dbo.TestTable

 (

     Id int IDENTITY (1,1),

     Name varchar(20),

     OldAddress varchar(50),

     NewAddress varchar(50)

 );

 INSERT INTO TestTable (Name, OldAddress, NewAddress)

 VALUES ('Emp1', NULL, NULL)

 INSERT INTO TestTable (Name, OldAddress, NewAddress)

 VALUES ('Emp2', '123 Street', '456 Street')

 INSERT INTO TestTable (Name, OldAddress, NewAddress)

 VALUES ('Emp3', NULL, NULL)

 INSERT INTO TestTable (Name, OldAddress, NewAddress)

 VALUES ('Emp3', '890 Street', '890 Street')

NULLIF

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.

SELECT *, NULLIF(OldAddress,NewAddress) AS 'No change in address' FROM TestTable

Results and explanation for NULLIF