NULLIF accept two parameters. It basically compare two value of both parameters.

1) It return the NULL value if both parameters values are same.
Ex. SELECT NULLIF(1,1) -> Output : NULL

2) It return the first parameter value if both parameters values are different.
Ex. SELECT NULLIF('10',NULL) -> Output : 10


Below is the syntax of NULLIF.


Syntax for NULLIF

SELECT NULLIF ( < parameter1 / value1 > , < parameter2 / value2 > )

1) Example

Below example where we create two string variable and assign same value to it. So NULLIF check if both parameters value are same then it return NULL value.
Let's see.

Example

DECLARE @Course1 VARCHAR (50) = 'WebNetGuru'
DECLARE @Course2 VARCHAR (50) = 'WebNetGuru'
SELECT NULLIF (@Course1, @Course2 ) AS 'IS Value Same'

Output

null if example in sql server


Note : If we observe the above example we get NULL as output because both values are same.

2) Example

Below example where we create two string variable and assign different value to it. So NULLIF check if both parameters having different values then it return value of first parameter.
Let's see.

Example

DECLARE @Value1 INT = 10
DECLARE @Value2 INT = 20
SELECT NULLIF (@Value1, @Value2) AS 'IS Value Same'

Output

nullif example in sql server


Note : In above example we get 10 as output because both values are different.


How to use ISNULL with NULLIF ?

Find out the below example.

null if example in sql server


What is difference between ISNULL and NULLIF?

  ISNULL NULLIF
Functionality It check if first value is null then it replace output with second value It compare two value and
1) return NULL if both values are same
2) return first value if both values are not same
Syntax
SELECT ISNULL ( < value or columns to check for null > , < new replace value > )
SELECT NULLIF ( < parameter1 / value1 > , < parameter2 / value2 > )
Example
DECLARE @CourseName VARCHAR (100)
SELECT ISNULL (@CourseName, 'WebNetGuru' ) AS 'DefalutName'

DECLARE @Course1 VARCHAR (50) = 'WebNetGuru'
SELECT NULLIF (@Course1, @Course2 ) AS 'IS Value Same'
Output
what is diffrence between isnull and nullif in sql server
what is diffrence between isnull and nullif in sql server