What is different between nullif and isnull? [Resolved]

Posted by Sudhakar_A under Sql Server on 9/20/2013 | Points: 10 | Views : 3400 | Status : [Member] | Replies : 4
What is different between nullif and isnull?




Responses

Posted by: Allemahesh on: 9/20/2013 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Dear Sudhakar,

NULLIF : It is a Function
1. Returns a NULL value if the two specified expressions are equal
2. Returns first expression, If the specified expressions are NOT equal
/*Both expressions(@A and @B) are Equal*/

Declare @A Int, @B Int

Select @A = 100, @B = 100
Select NULLIF(@B, @A) [Result]
Result
---------
NULL

/*Both expressions(@A and @B) are NOT Equal*/

Declare @A Int, @B Int

Select @A = 100, @B = 101

Select NULLIF(@B, @A) [Result]
Result
---------
101


ISNULL : It is a Function

- Returns second Expression, If the first one is NULL
- Returns first expression, If the first one is NOT NULL

Declare @A Int

Select @A = 100
Select ISNULL(@A,0) [Result]

Result
---------
100


Happy Coding
If it helps you or directs U towards the solution, MARK IT AS ANSWER

Sudhakar_A, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Explanation of NULLIF
Syntax:
NULLIF ( expression , expression )

Returns a null value if the two specified expressions are equal. 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. NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.

NULLIF() Returns a null value if the two specified expressions are equal. If the Two expressions are not equal then it will return the first expression's value. Whether the returned value is NULL or NOT NULL, NULLIF() will return the same data type as the first expression


Explanation of ISNULL
Syntax:
ISNULL ( check_expression , replacement_value )

Replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

ISNULL() Replaces the NULL value with the specified expression value.

Observation:
Interesting observation is NULLIF returns null if it comparison is successful, where as ISNULL returns not null if its comparison is successful. In one way they are opposite to each other.


Examples:

Example 1 :
Select NULLIF(100,50*2)  'NULLIFValue' 

OutPut :
NULL


Example 2 :
Select NULLIF(2*2,2*7)  'NULLIFValue' 


OutPut :
4


Example 3 :
Select NULLIF(20-2,19)  'NULLIFValue' 


OutPut :
NULLIFValue
18


ISNULL Examples
Example 1 :

Select ISNULL(null,12) 'ISNULL VALUE'

OutPut :
ISNULLVALUE
12


Simply ISNULL replaces first argument with second argument if first argument value is NULL; otherwise first argument value.
NULLIF() return NULL if first & second arguments are equal; otherwise first argument value


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sudhakar_A, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
COALESCE( exp1, exp2, ....) is also same as ISNULL() function in SQL Server..
To replace NULLs with other values we supposed to use ISNULL()/COALESCE() functions...
The difference between ISNULL() and COALESCE() is that COALESCE() will take more than two arguments... where as Isnull() will take two params and returns value

DECLARE @A int = NULL, @B INT = NULL, @C INT = 10

SELECT ISNULL( @A, @B) -- returns NULL because of second argument value too NULL
SELECT ISNULL(ISNULL( @A, @B) , @c) -- returns 10
equivalent COALESCE() is as follows:
SELECT COALESCE( @A, @B, @C) -- Returns 10


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sudhakar_A, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sqldev on: 12/12/2013 [Member] Starter | Points: 25

Up
0
Down
Hi , here is a video which explains Differences between ISNULL and NULLIF in detail.

https://www.youtube.com/watch?v=waiKG7pPqHg

I hope this helps you in clarifying your doubt!!

Sudhakar_A, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response