Monday , December 11 2017
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.

Syntax

ISNULL (check_exp, change_value)

Example 1 :

Select ISNULL(null,15) ‘ISNULL VALUE’

output:

15

Explanation:

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 :

Select ISNULL(20,12) ‘ISNULL VALUE’

output:

20

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  :

NULLIF(expression1,expression2)

Example 1 :

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

OutPut :

NULL

Explanation:

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 :

10

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
--------------
212500.000000
(1 row(s) affected)

Conclusion:

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

comments

About Murali

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

Check Also

Find all rows count and columns count in single database in sql server

In General for any database it contains lot of tables, if we want to find …

%d bloggers like this: