SQL Server：認識 COALESCE() 函數

比較 ISNULL() 函數以及 COALESCE() 函數

(1)
COALESCE() 函數是 ANSI SQL-92 標準。
ISNULL() 函數不是 ANSI SQL-92 標準。

(2)
COALESCE() 函數，支援多個輸入參數。
若只有使用兩個參數，則 COALESCE() 函數的行為類似於 ISNULL() 函數。

(3)
若是有多個參數時，COALESCE() 函數 ，可以替代 CASE 運算式搭配 ISNULL() 函數。

(4)
若以效能觀點來看，在某些情境下，ISNULL() 函數可能會優於 COALESCE() 函數。

請參考以下的文章：
Performance of COALESCE vs. IS NULL
http://sqlserverperformance.idera.com/tsql-optimization/performance-coalesce-null/

---

以下範例示範使用 COALESCE() 函數：

EX1. 兩個參數：ISNULL() 函數、COALESCE() 函數

USE Northwind
GO
-- 使用 COALESCE() 函數
SELECT CustomerID, Country, Region, City, 
 Country + ',' + COALESCE(Region, ' ') + ',' + City N'地區'
FROM dbo.Customers
GO
-- 使用 ISNULL() 函數
SELECT CustomerID, Country, Region, City, 
 Country + ',' + ISNULL(Region, ' ') + ',' + City N'地區'
FROM dbo.Customers
GO

-- 01_兩個參數：ISNULL() 函數、COALESCE() 函數

---

EX2. 比較 ISNULL() 函數以及 COALESCE() 函數

-- 使用 ISNULL() 函數，僅支援兩個參數
SELECT ISNULL(NULL, 'A') -- 回傳 A
GO
--
SELECT ISNULL(NULL, NULL) -- 回傳 NULL
GO

-- 02_使用 ISNULL() 函數，僅支援兩個參數

-- ISNULL() 函數，卻輸入三個參數
SELECT ISNULL(NULL, NULL, 'A')  -- 失敗
GO
/*
訊息 174，層級 15，狀態 1，行 2
isnull 函數需要 2 個引數。
*/

-- 03_ISNULL() 函數，卻輸入三個參數

-- 使用 COALESCE() 函數
SELECT COALESCE(NULL, 'A') -- 回傳 A
GO

-- 04_使用 COALESCE() 函數

-- COALESCE() 函數，全部的參數都是 NULL
SELECT COALESCE(NULL, NULL) -- 失敗
GO
/*
訊息 4127，層級 16，狀態 1，行 1
COALESCE 的引數至少要有一個是非 NULL 常數的運算式。
*/

-- 05_COALESCE() 函數，全部的參數都是 NULL

-- COALESCE() 函數，使用三個參數
SELECT COALESCE(NULL, NULL, 'A') -- 回傳 A
GO

-- COALESCE() 函數，使用四個參數
SELECT COALESCE(NULL, 'A', NULL, 'B') -- 回傳 A 
GO

-- 06_COALESCE() 函數，使用多個參數

---

EX3. 使用 ISNULL() 函數，可能會有資料被截斷的問題

DECLARE @str1 varchar(5), @str2 varchar(100)
SET @str1 =NULL
SET @str2 = 'This is a book.'

SELECT @str1 N'變數@str1', @str2 N'變數@str2'

SELECT ISNULL(@str1, @str2) N'ISNULL() 函數', DATALENGTH(ISNULL(@str1, @str2))  N'位元組' -- 回傳：This
SELECT COALESCE(@str1, @str2) N'COALESCE() 函數', DATALENGTH(COALESCE(@str1, @str2)) N'位元組' -- 完整回傳：This is a book.
GO

-- 07_使用 ISNULL() 函數，可能會有資料被截斷的問題

---

EX4. 使用 COALESCE() 函數、 CASE  運算式以及 CASE  運算式 + ISNULL() 函數等版本

-- 建立範例資料表
USE tempdb
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myInvoices]') AND type in (N'U'))
 DROP TABLE [dbo].[myInvoices]
GO
SELECT *
 INTO myInvoices
FROM (SELECT CustomerID, DATEPART(M,OrderDate) N'OrderMonth', SUM(UnitPrice) N'SubTotal'
 FROM Northwind.dbo.Invoices
 GROUP BY DATEPART(M,OrderDate),CustomerID) myInvoices
PIVOT (SUM(SubTotal)
  FOR OrderMonth IN ([1],[2],[3], [4], [5],[6],[7],[8],[9],[10],[11],[12]) ) pvt
GO

-- 查詢：資料表內容
SELECT * FROM myInvoices
GO

-- 08_查詢：資料表內容

需求：找出客戶第一次有下單月份的金額

-- 使用 COALESCE() 函數
SELECT CustomerID, 
 COALESCE([1], [2], [3]) N'第一次有下單月份的金額', 
 [1], [2], [3]
FROM myInvoices
GO

-- 09_使用 COALESCE() 函數，找出客戶第一次有下單月份的金額

-- CASE  運算式版本，找出客戶第一次有下單月份的金額
SELECT CustomerID, 
 CASE 
  WHEN [1] IS NOT NULL THEN [1]
  WHEN [2] IS NOT NULL THEN [2]
  WHEN [3] IS NOT NULL THEN [3]
  ELSE NULL
 END N'首月的績效', 
 [1], [2], [3]
FROM myInvoices
GO

-- CASE  運算式 + ISNULL() 函數版本，找出客戶第一次有下單月份的金額
SELECT CustomerID, 
 ISNULL([1], ISNULL([2], ISNULL([3],[3]))) N'首月的績效', 
 [1], [2], [3]
FROM myInvoices
GO

---

認識 COALESCE 函數

COALESCE 函數：傳回其引數中第一個非 Null 的運算式。

語法：
COALESCE ( expression [ ,...n ] )

引數
expression
這是任何類型的運算式。

傳回類型
傳回具有最高資料類型優先順序的 expression 資料類型。
如果所有運算式都不可為 Null，結果的類型也是不可為 Null。

如果所有引數都是 NULL，COALESCE 便會傳回 NULL。

ISNULL 和 COALESCE 雖然相當，但行為可能不同。
使用 ISNULL 和非 Null 參數的運算式會視為是非 NULL，而使用 COALESCE 和非 Null 參數的運算式則會視為是 NULL。

在 SQL Server 中，對於使用 COALESCE 和非 Null 參數的索引運算式而言，計算資料行可以使用 PERSISTED 資料行屬性保存。

---

參考資料

ISNULL (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms184325.aspx

COALESCE (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms190349(v=sql.110).aspx

Differences between ISNULL and COALESCE
http://blogs.msdn.com/b/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx

ISNULL() and COALESCE()
http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ISNULL_COALESCE

How to use COALESCE and ISNULL?
http://www.sqlusa.com/bestpractices/coalesce/

Performance of COALESCE vs. IS NULL
http://sqlserverperformance.idera.com/tsql-optimization/performance-coalesce-null/