Hi friends, in this session I am gonna discuss the differences between ISNULL and NULLIF.
Both ISNULL and NULLIF are built in system functions in sql server.
Lot of people get confused between these 2 system functions.
Lets discuss about each of these system functions separately so that you will get better idea.
1st lets discuss about ISNULL.
ISNULL:
ISNUL is a system function which replaces NULL with the specified replacement value.
The functionalityof ISNULL is :
1. 1st it will Evaluate the expression which is in parameter 1.
2. it will compare the evaluated expression with null. if the paramter 1 = NULL ?
3. If parameter1 is equal to null i.e., if parameter 1 is null, then isnull function returns replacement value. if parameter1is not NULL, then ISNULL function returns parameter1
I hope we are clear on ISNULL.
Lets move on to the next system function, that is NULLIF.
NULLIF:
NULLIF is a system function which returns a null value if the two specified expressions are equal.
The functionalityof NULLIF is :
1. 1st it will Evaluate both the expression which are in parameter 1 and parameter 2.
2. next it will compare both the evaluated expressions with null.
3. If both the expressions are equal, the nullif functions returns NULL.
4. If both the expressions are not equal, the nullif functions returns 1st expression.
Lets conclude the discussion with the similarities and differences between ISNULL and NULLIF
Similarities:
Both ISNULL and NULLIF are system functions.
Syntax for Both the functions are similar
Both ISNULL and NULLIF functions accepts 2 input parameters.
Differences:
isnull function compares 1st parameter with null.where as nullif function compares 1st parameter with 2nd parameter
ISNULL function returns 2nd paramter if the 1st parameter isnull. nullif function returns null is 1st parameter = 2nd paramter.
ISNULL function returns 1st paramter if the 1st parameter is not null. nullif function returns 1st paramter if 1st parameter is not equal to 2nd paramter.
Thanks for watching this video. Bye , Have a good Day
