How to use COALESCE and ISNULL?

Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate COALESCE (ANSI 92 SQL standard) and ISNULL (T-SQL only) usage and performance implications.

-- SQL coalesce: returns the first non-null expression among its arguments
SELECT COALESCE(NULL, NULL, NULL, NULL, 'New York', NULL, NULL, 'Seattle', NULL)
-- New York
 
-- SQL isnull: two arguments only, replaces NULL with the specified replacement value
SELECT ISNULL(NULL, ISNULL(NULL, ISNULL(NULL, ISNULL('New York', 'New York'))))
-- New York
 
SELECT COALESCE( NULL, 'Missing Data')
SELECT  ISNULL ( NULL, 'Missing Data')
-- Missing Data
------------

-- SQL coalesce to select first non-null year sales for sales staff
SELECT [FullName]
      ,FirstYearPerformance = COALESCE([2002], [2003], [2004])
      ,[SalesTerritory]
      ,[2002]
      ,[2003]
      ,[2004]
  FROM [AdventureWorks].[Sales].[vSalesPersonSalesByFiscalYears]
  ORDER BY FirstYearPerformance desc
GO

-- Results

FullName	FirstYearPerformance	SalesTerritory	2002	2003	2004
Jae B Pak	5287044.313	United Kingdom	NULL	5287044	5015682
Jillian  Carson	3308895.851	Central	3308896	4991868	3857164
Tsvi Michael Reiter	3242697.013	Southeast	3242697	2661156	2811013
Linda C Mitchell	2800029.154	Southwest	2800029	4647225	5200475
José Edvaldo Saraiva	2532500.913	Canada	2532501	1488793	3189356
Rachel B Valdez	2241204.042	Germany	NULL	NULL	2241204
Shu K Ito	2040118.623	Southwest	2040119	2870321	3018725
Michael G Blythe	1951086.826	Northeast	1951087	4743907	4557045
Lynn N Tsoflias	1758385.926	Australia	NULL	NULL	1758386
Ranjit R Varkey Chudukatil	1677652.437	France	NULL	1677652	3827950
Pamela O Ansman-Wolfe	1473076.914	Northwest	1473077	900368.6	1656493
David R Campbell	1243580.769	Northwest	1243581	1377431	1930886
Garrett R Vargas	1135639.263	Canada	1135639	1480136	1764939
Tete A Mensa-Annan	883338.7107	Northwest	NULL	883338.7	1931620

 

-- Equivalent CASE expression
-- COALESCE syntax is simpler 
SELECT   [FullName], 
         FirstYearPerformance = CASE 
                                  WHEN [2002] IS NOT NULL THEN [2002] 
                                  WHEN [2003] IS NOT NULL THEN [2003] 
                                  WHEN [2004] IS NOT NULL THEN [2004] 
                                  ELSE NULL 
                                END, 
         [SalesTerritory], 
         [2002], 
         [2003], 
         [2004] 
FROM     [AdventureWorks].[Sales].[vSalesPersonSalesByFiscalYears] 
ORDER BY FirstYearPerformance DESC

GO
-- SQL isnull version - nested isnulls - isnull nesting
-- SQL coalesce syntax simpler than isnull syntax - same result
SELECT [FullName]
      ,FirstYearPerformance = ISNULL([2002], ISNULL([2003], ISNULL([2004],[2004])))
      ,[SalesTerritory]
      ,[2002]
      ,[2003]
      ,[2004]
  FROM [AdventureWorks].[Sales].[vSalesPersonSalesByFiscalYears]
  ORDER BY FirstYearPerformance desc
GO
------------
 
-- Kill all connection in the database - DBA utility - Dynamic SQL
-- May be used by DBA to get exclusive access to the database
DECLARE  @SQL NVARCHAR(MAX) 
SELECT @SQL = ISNULL(@SQL,'') + 'Kill ' + CAST(spid AS VARCHAR) + '; ' 
FROM   sys.sysprocesses 
WHERE  DBID = DB_ID('AdventureWorks') 
       AND spid > 50 
PRINT @SQL 
-- Kill 52; Kill 54; Kill 55; Kill 57; Kill 58; Kill 59; Kill 60; Kill 61; 
-- EXEC sp_executeSQL @SQL -- remove comments for production run
------------
 
 
-- SQL make comma-limited list
DECLARE  @DeptList VARCHAR(MAX); 
SELECT   @DeptList = coalesce(@DeptList + ', ','') + Name 
FROM     AdventureWorks.HumanResources.Department 
ORDER BY Name 
 
SELECT @DeptList 
GO
/*  Result
Document Control, Engineering, Executive, Facilities and Maintenance, 
Finance, Human Resources, Information Services, Marketing, Production, 
Production Control, Purchasing, Quality Assurance, Research and Development, 
Sales, Shipping and Receiving, Tool Design
*/
-- Equivalent with ISNULL
DECLARE  @DeptList VARCHAR(MAX); 
 
SELECT   @DeptList = isnull(@DeptList + ', ','') + Name 
FROM     AdventureWorks.HumanResources.Department 
ORDER BY Name 
 
SELECT @DeptList 
GO
------------
 
-- T-SQL build comma-limited list
DECLARE  @ColorList VARCHAR(MAX); 
-- SQL table variable
DECLARE @Colors TABLE (Color varchar(32))
INSERT @Colors SELECT DISTINCT Color
FROM     AdventureWorks.Production.Product  
SELECT * FROM @Colors ORDER BY Color desc
/*
Color
NULL
Black
Blue
Grey
Multi
Red
Silver
Silver/Black
White
Yellow
*/
-- Equivalent result for coalesce and isnull
SELECT   @ColorList = COALESCE(@ColorList + ', ','') +  Color 
-- SELECT   @ColorList = ISNULL(@ColorList + ', ','') +  Color 
FROM     @Colors 
ORDER BY Color
 
SELECT @ColorList 
GO
/*
Black, Blue, Grey, Multi, Red, Silver, Silver/Black, White, Yellow
*/
------------
 
-- SQL replace NULL column value - coalesce, isnull
-- SQL replace NULL string column with empty space
SELECT FirstName + ' ' + COALESCE(MiddleName,'') + ' ' + LastName AS 'FullName', 
       FirstName, 
       MiddleName, 
       LastName, 
       ContactID 
FROM   AdventureWorks.Person.Contact 
GO
/* Partial results
 
FullName                FirstName   MiddleName  LastName    ContactID
Gustavo  Achong         Gustavo     NULL        Achong      1
Catherine R. Abel       Catherine   R.          Abel        2
Kim  Abercrombie        Kim         NULL        Abercrombie 3
Humberto  Acevedo       Humberto    NULL        Acevedo     4
*/
-- Same result with ISNULL
 
SELECT FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName AS 'FullName', 
       FirstName, 
       MiddleName, 
       LastName, 
       ContactID 
FROM   AdventureWorks.Person.Contact 
GO
------------

------------ 
-- SQL COALESCE vs ISNULL in Performance 
------------ 
-- In some cases ISNULL performs better than COALESCE 
-- Database engine generates different execution plans 
-- SET SHOWPLAN_TEXT ON  
-- The above can be used to see the execution plan in text format 
SET STATISTICS  IO  ON 
GO 
 
-- COALESCE version
DBCC DROPCLEANBUFFERS 
DECLARE  @SOList VARCHAR(MAX); 
SET @SOList = NULL; 
SELECT   TOP ( 60000 ) @SOList = COALESCE(@SOList + ', ','') + 
                                 CAST(SalesOrderID AS VARCHAR) 
FROM     AdventureWorks.Sales.SalesOrderDetail 
ORDER BY SalesOrderID 
SELECT LEFT(@SOList,60) 
GO 
-- 25 sec execution time
-- 43659, 43659, 43659, 43659, 43659, 43659, 43659, 43659, 4365 
/* Messages 
Table 'SalesOrderDetail'. Scan count 1, logical reads 657, physical reads 3,  
read-ahead reads 1242, lob logical reads 0, lob physical reads 0,  
lob read-ahead reads 0. 
Table 'Worktable'. Scan count 0, logical reads 24981, physical reads 0,  
read-ahead reads 0, lob logical reads 4539236, lob physical reads 0,  
lob read-ahead reads 0. 
 
 
*/
-- ISNULL version 
DBCC DROPCLEANBUFFERS 
DECLARE  @SOList VARCHAR(MAX); 
SET @SOList = NULL; 
SELECT   TOP ( 60000 ) @SOList = ISNULL(@SOList + ', ','') + 
                                 CAST(SalesOrderID AS VARCHAR) 
FROM     AdventureWorks.Sales.SalesOrderDetail 
ORDER BY SalesOrderID 
SELECT LEFT(@SOList,60) 
GO 
-- 5 sec execution time 
/* Messages 
 
Table 'SalesOrderDetail'. Scan count 1, logical reads 657, physical reads 2,  
read-ahead reads 1242, lob logical reads 0, lob physical reads 0,  
lob read-ahead reads 0. 
 
*/ 
SET STATISTICS  IO  OFF

GO
-- Execution plan for COALESCE version
SET SHOWPLAN_TEXT ON;
GO
DECLARE @SOList VARCHAR(MAX); 
SET @SOList = NULL;  
SELECT TOP (60000) @SOList = COALESCE(@SOList + ', ','') + 
                             CAST(SalesOrderID AS VARCHAR) 
FROM AdventureWorks.Sales.SalesOrderDetail 
ORDER BY SalesOrderID 
SELECT LEFT(@SOList,60) 
GO
SET SHOWPLAN_TEXT OFF;
/*
StmtText
  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN ([@SOList]+', ') IS NOT NULL THEN [@SOList]+', ' ELSE CONVERT_IMPLICIT(varchar(max),'',0) END+[Expr1004]))
       |--Top(TOP EXPRESSION:((60000)))
            |--Compute Scalar(DEFINE:([Expr1004]=CONVERT(varchar(30),[AdventureWorks].[Sales].[SalesOrderDetail].[SalesOrderID],0)))
                 |--Clustered Index Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderDetail].[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]), ORDERED FORWARD)

*/
-- Execution plan for ISNULL version
SET SHOWPLAN_TEXT ON;
GO
DECLARE @SOList VARCHAR(MAX); 
SET @SOList = NULL;  
SELECT TOP (60000) @SOList = ISNULL(@SOList + ', ','') + 
                             CAST(SalesOrderID AS VARCHAR) 
FROM AdventureWorks.Sales.SalesOrderDetail 
ORDER BY SalesOrderID 
SELECT LEFT(@SOList,60) 
GO
/*
  |--Compute Scalar(DEFINE:([Expr1003]=isnull([@SOList]+', ',CONVERT_IMPLICIT(varchar(max),'',0))+[Expr1005]))
       |--Top(TOP EXPRESSION:((60000)))
            |--Compute Scalar(DEFINE:([Expr1005]=CONVERT(varchar(30),[AdventureWorks].[Sales].[SalesOrderDetail].[SalesOrderID],0)))

                 |--Clustered Index Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderDetail].[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]), ORDERED FORWARD)
*/

------------