Explanation and Differances of NULLIF and ISNULL [Resolved]

Posted by Saranya Boopathi under Sql Server on 8/20/2012 | Points: 10 | Views : 2180 | Status : [Member] | Replies : 4
In Sql Server,
What is the purpose of using NULLIF and ISNULL.Where it is use.Comparison and also Syntax and Example for NULLIF and ISNULL.

Saranya Boopathi


Responses

Posted by: Kirthiga on: 8/21/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi Saranya,

NULLIF

NULLIF is used to compare two epressions
1. If both the expressions are same it returns NULL value.
2. If both the expressions are different returns the first expression.

NOTE : The first expression should not be NULL while using NULLIF function.

Syntax :
NULLIF(expresion,expresion)


Example:
select nullif(12,12)[1],nullif(0,100)[2],nullif(12,NULL)[3]


Output:
1 2 3
NULL 0 12

ISNULL

ISNULL is used to replace the NULL value
1. If first expression is NULL it replaces the second expression.
2. If first expression is not NULL it retain its value.

Syntax:
ISNULL(expression,replacement value)


Example:
select ISNULL(1,5)[1],ISNULL(NULL,4)[2],ISNULL(null,null)[3]


Output:
1 2 3
1 4 NULL

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

Posted by: Gopesh9 on: 8/20/2012 [Member] Starter | Points: 25

Up
0
Down
NULLIF : It will returns a null value if the two specified expressions are equal i.e. NULLIF(expression1,expression2) will return NULL if expression1 = expression2.

ISNULL : It will replace NULL with the specified replacement value i.e. ISNULL ( check_expression , replacement_value )
For example If you write ISNULL(Height,170) It will specify 170 in place of height if the value of height is Null.




G. S.
.Net Developer

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

Posted by: Pandians on: 8/20/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Check it out!

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

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Posted by: Srilu.Nayini577 on: 8/20/2012 [Member] Starter | Points: 25

Up
0
Down
Nice explanation

SRILATHA
.Net Developer

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

Login to post response