In this article I will explain the differences between the ISNULL() and NULLIF() functions in SQL Server 2012.
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 :
Select ISNULL(20,12) ‘ISNULL VALUE’
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
Select ProductId,ProductName,Qty,ItemAmount, ISNULL(Qty,0) * ISNULL(Qty,ItemAmount) as TotalAmount from Item_Sales
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.
Example 1 :
Select NULLIF(50,25*2) ‘NULLIF Value’
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’
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.
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.
IF OBJECT_ID ('dbo.budgets','U') IS NOT NULL
DROP TABLE budgets;
SET NOCOUNT ON;
CREATE TABLE dbo.budgets
dept tinyint IDENTITY,
current_year decimal NULL,
previous_year decimal NULL
INSERT budgets VALUES(100000, 150000);
INSERT budgets VALUES(NULL, 300000);
INSERT budgets VALUES(0, 100000);
INSERT budgets VALUES(NULL, 150000);
INSERT budgets VALUES(300000, 250000);
SET NOCOUNT OFF;
previous_year), 0.00)) AS 'Average Budget'
Here is the result set.
(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.