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 NULLIF
ISNULL:
ISNUL is a system function which replaces NULL with the specified replacement value.
The sytax for ISNULL function is
ISNULL (<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 parameter1
Lets see this with an example so that it will be more clear.
CODE
DECLARE @PARAM1 INT, @PARAM2 INT
SELECT @PARAM1 =10 , @PARAM2= 20
SELECT ISNULL(@PARAM1 ,@PARAM2)
OUTPUT
10
In the above example Parameter1 IS NOT NULL , so ISNULL Function returned Parameter1
CODE
DECLARE @PARAM1 INT, @PARAM2 INT
SELECT @PARAM1 =NULL, @PARAM2= 20
SELECT ISNULL(@PARAM1 ,@PARAM2)
OUTPUT
20
In the above example Parameter1 IS NULL , so ISNULL Function returned Parameter2
NULLIF:
NULLIF is a system function which returns a null value if the two specified expressions are equal.
The sytax for NULLIF function is
NULLIF ( <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 INT
SELECT @PARAM1 =10, @PARAM2= 20
SELECT NULLIF(@PARAM1 ,@PARAM2)
OUTPUT
10
In the above example Paramter1 is not equal to Paramter2, so ISNULL Function returned Paramter1
CODE
DECLARE @PARAM1 INT, @PARAM2 INT
SELECT @PARAM1 =10, @PARAM2= 10
SELECT NULLIF(@PARAM1 ,@PARAM2)
OUTPUT
NULL
In the above example Parameter1 is equal to Parameter2, so ISNULL Function returned NULL.
To summarize nullif functionality...
Lets conclude the discussion with the similarities and differences between ISNULL and NULLIF
Similarities:
Differences:
I hope this session is useful. Please me know your comments and valuable suggestion.
Bye , Have a good Day!!
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 NULLIF
ISNULL:
ISNUL is a system function which replaces NULL with the specified replacement value.
The sytax for ISNULL function is
ISNULL (<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 parameter1
Lets see this with an example so that it will be more clear.
CODE
DECLARE @PARAM1 INT, @PARAM2 INT
SELECT @PARAM1 =10 , @PARAM2= 20
SELECT ISNULL(@PARAM1 ,@PARAM2)
OUTPUT
10
In the above example Parameter1 IS NOT NULL , so ISNULL Function returned Parameter1
CODE
DECLARE @PARAM1 INT, @PARAM2 INT
SELECT @PARAM1 =NULL, @PARAM2= 20
SELECT ISNULL(@PARAM1 ,@PARAM2)
OUTPUT
20
In 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 is
NULLIF ( <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 INT
SELECT @PARAM1 =10, @PARAM2= 20
SELECT NULLIF(@PARAM1 ,@PARAM2)
OUTPUT
10
In the above example Paramter1 is not equal to Paramter2, so ISNULL Function returned Paramter1
DECLARE @PARAM1 INT, @PARAM2 INT
SELECT @PARAM1 =10, @PARAM2= 10
SELECT NULLIF(@PARAM1 ,@PARAM2)
OUTPUT
NULL
In the above example Parameter1 is equal to Parameter2, so ISNULL Function returned NULL.
To summarize nullif functionality...
- Parameter1 = Parameter2 , retuns NULL
- Parameter1 <> Parameter2 , retuns Parameter1
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 <> 2nd paramter.
I hope this session is useful. Please me know your comments and valuable suggestion.
Bye , Have a good Day!!
Cost Benefits Of Remote Database Administration DBA Metrix Solutions
ReplyDelete