Saturday , August 18 2018
Home / DataBase / SQL SERVER / ISNULL() Vs NULLIF() in sqlserver

ISNULL() Vs NULLIF() in sqlserver

In this article I will explain the differences between the ISNULL() and NULLIF() functions in SQL Server 2012.

ISNULL() Function

The ISNULL() function is used to replace NULL with the specified replacement value. This function contains only two arguments.


ISNULL (check_exp, change_value)

Example 1 :

Select ISNULL(null,15) ‘ISNULL VALUE’




in above select statement first it will check first parameter value, if it has not null value then it will print given value otherwise if it has null value then it will print second parameter value, so from above statement it prints 15

Example 2 :




Usage Scenario of ISNULL():

Then ISNULL() mainly used when performing calculations on columns, for example we have below sales table we want to find total sale amount for an item

Table Name : Item_Sales

ProductId ProductName Qty ItemAmount
1 Product-1 20 100
2 Product-2 NULL 80

Select ProductId,ProductName,Qty,ItemAmount, ISNULL(Qty,0) * ISNULL(Qty,ItemAmount) as TotalAmount  from Item_Sales

ProductId ProductName Qty ItemAmount TotalAmount
1 Product-1 20 100 2000
2 Product-2 NULL 80 0


NULLIF() Function:

the NULLIF function compares expression1 and expression2. If expression1 and expression2 are equal, the NULLIF function returns NULL. Otherwise, it returns the first expression which is expression1.

Syntax  :


Example 1 :

Select NULLIF(50,25*2)  ‘NULLIF Value’

OutPut :



in above select statement both expression1 and expression 2 are equal so it will returns NULL Value.

in below example both expressions are not equal so it will returns first expression

Example 2 :

Select NULLIF(10,25*2)  ‘NULLIF Value’

OutPut :


Usage Scenario of NULLIF():

NULLIF is equivalent to a searched CASE expression in which the two expressions are equal and the resulting expression is NULL.

We recommend that you not use time-dependent functions, such as RAND(), within a NULLIF function. This could cause the function to be evaluated twice and to return different results from the two invocations.

More Examples

A. Returning budget amounts that have not changed

The following example creates a budgets table to show a department dept its current budget current_year and its previous budget previous_year. For the current year, NULL is used for departments with budgets that have not changed from the previous year, and 0 is used for budgets that have not yet been determined. To find out the average of only those departments that receive a budget and to include the budget value from the previous year (use the previous_year value, where the current_year is NULL, combine the NULLIF and COALESCE functions.

Here is the result set.

Average Budget
(1 row(s) affected)


Did you find this post as useful? I hope you liked the article. Please share your valuable suggestions and feedback in the comments.

Print Friendly, PDF & Email

Give us your valuable feedback


About Murali

Hello everyone I am Software Developer having 3+ years of Experience in Microsoft Technologies and Others. My skills :-, C#,, SQL SERVER, web services, windows Service,Windows applications,HTML,CSS, JavaScript,JQuery,AnjularJs

Check Also

sql server backup script

Backup all databases in SQL Instance with one script

Backup: A copy of SQL Server data that can be used to restore and recover the …