Differences between ISNULL and NULLIF

Posted onWednesday, 11 December 2013bySQL Developer

Hi friends, in this article I am gonna discuss the differences between ISNULL and NULLI. 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.Here is a video which clearly explains Differences between ISNULL and NULLIFISNULL:ISNUL is a system function which replaces NULL with the specified replacement value.The sytax for ISNULL function isISNULL (<check_expression> , <replacement_value> ).Which implies isnull function accepts 2 input parameters.1st one is an expression and 2nd paramter is a 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 parameter1 <> NULL, then ISNULL function returns parameter1Lets see this with an example so that it will be more clear.CODE DECLARE @PARAM1 INT, @PARAM2 INTSELECT @PARAM1 =10 , @PARAM2= 20SELECT ISNULL(@PARAM1 ,@PARAM2)OUTPUT10In the above example Parameter1 IS NOT NULL , so ISNULL Function returned Parameter1CODE DECLARE @PARAM1 INT, @PARAM2 INTSELECT @PARAM1 =NULL, @PARAM2= 20SELECT ISNULL(@PARAM1 ,@PARAM2)OUTPUT 20In the above example Parameter1 IS NULL , so ISNULL Function returned Parameter2

To summarize is null functionality.

Parameter1 = NULL , retuns Parameter2

Parameter1 <> NULL , retuns Parameter1

NULLIF:NULLIF is a system function which returns a null value if the two specified expressions are equal.The sytax for NULLIF function isNULLIF ( <Expression1> , <Expression2> )Which implies NULLIF function also accepts 2 input parameters.The functionality of 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 see this with an example so that it will be more clear.CODE DECLARE @PARAM1 INT, @PARAM2 INTSELECT @PARAM1 =10, @PARAM2= 20SELECT NULLIF(@PARAM1 ,@PARAM2)OUTPUT10In the above example Paramter1 is not equal to Paramter2, so ISNULL Function returned Paramter1