Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the usage of NULLIF function.
– SELECT NULLIF quick demo
– Counts all, including ListPrice=0.0
SELECT COUNT(ListPrice)
FROM AdventureWorks2008.Production.Product
– 504
– Counts only when ListPrice != 0 – does not count NULLs (ListPrice = 0.0)
SELECT COUNT(NULLIF(ListPrice,0.0))
FROM AdventureWorks2008.Production.Product
– 304
————
– SQL NULLIF: returns NULL when the two operands are equivalent, else the first one
– SQL COALESCE
USE tempdb
GO
CREATE TABLE Budget (
BudgetID TINYINT IDENTITY,
DepartmentID TINYINT,
CurrentYear DECIMAL NULL,
PriorYear DECIMAL NULL);
GO
INSERT Budget VALUES(10,2000000,1800000);
INSERT Budget VALUES(20,NULL,3000000);
INSERT Budget VALUES(30,0,8000000);
INSERT Budget VALUES(70,NULL,3500000);
INSERT Budget VALUES(90,6000000,6500000);
GO
SELECT * FROM Budget
GO
/*
BudgetID DepartmentID CurrentYear PriorYear
1 10 2000000 1800000
2 20 NULL 3000000
3 30 0 8000000
4 70 NULL 3500000
5 90 6000000 6500000
*/
SELECT COALESCE(CurrentYear,PriorYear) AS ‘Yearly Budget’
FROM Budget;
/*
Yearly Budget
2000000
3000000
0
3500000
6000000
*/
– SQL NULLIF changes 0 to NULL
SELECT NULLIF(COALESCE(CurrentYear,PriorYear),0.00) AS ‘Yearly Budget No Zeros’
FROM Budget;
/*
Yearly Budget No Zeros
2000000
3000000
NULL
3500000
6000000
*/
– SQL NULLIF
SELECT AVG(NULLIF(COALESCE(CurrentYear,PriorYear),0.00)) AS ‘Average Yearly Budget’
FROM Budget;
GO
/* Result
Average Yearly Budget
3625000.000000
*/
– Cleanup
DROP TABLE tempdb.dbo.Budget
GO
