Explanation and Differances of NULLIF and ISNULL [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

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

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

Nice explanation

SRILATHA
.Net Developer

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