COALESCE (Transact-SQL)

發行項
03/07/2014

依序評估引數，並傳回一開始未評估為 NULL 之第一個運算式的目前值。

適用於：SQL Server (SQL Server 2008 透過目前版本)、Windows Azure SQL 資料庫 (初始版本，透過目前版本)。

語法

COALESCE ( expression [ ,...n ] )

引數

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

傳回類型

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

備註

如果所有引數都是 NULL，COALESCE 便會傳回 NULL。至少其中一個 Null 值必須是 NULL 類型。

比較 COALESCE 和 CASE

COALESCE 運算式是 CASE 運算式的語法捷徑。也就是說，查詢最佳化工具會將程式碼 COALESCE(expression1，...n) 重寫為下列 CASE 運算式：

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

...

ELSE expressionN

END

這表示將多次評估輸入值 (expression1、expression2、expressionN 等)。此外，為了符合 SQL 標準，包含子查詢的值運算式會視為不具決定性，且會評估子查詢兩次。不論是哪一種情況，第一次評估和後續評估之間都可能傳回不同的結果。

例如，執行程式碼 COALESCE((subquery), 1) 時，會評估子查詢兩次。因此，您會根據查詢的隔離等級取得不同的結果。例如，程式碼在多使用者環境的 READ COMMITTED 隔離等級下，會傳回 NULL。為了確保傳回的結果穩定，請使用 SNAPSHOT ISOLATION 隔離等級，或以 ISNULL 函數取代 COALESE。或者，您可以如下列範例所示重寫查詢，並將子查詢推入子選擇中。

SELECT CASE WHEN x IS NOT NULL THEN x ELSE 1 END
from
(
SELECT (SELECT Nullable FROM Demo WHERE SomeCol = 1) AS x
) AS T;

比較 COALESCE 和 ISNULL

ISNULL 函數和 COALESCE 運算式具有相似的目的，但運作方式可能不同。

因為 ISNULL 是函數，所以只會評估一次。如上所述，COALESCE 運算式的輸入值可能會評估多次。
結果運算式所決定的資料類型會不同。 ISNULL 使用第一個參數的資料類型，而 COALESCE 則遵循 CASE 運算式規則，並傳回最高優先順序之值的資料類型。

ISNULL 和 COALESCE 之結果運算式的 Null 屬性不同。 ISNULL 傳回值一律視為不可為 NULL (假設傳回值不可為 Null)，而具有非 Null 參數的 COALESCE 會視為 NULL。因此，運算式 ISNULL(NULL, 1) 和 COALESCE(NULL, 1) 雖然對等，但有不同的 Null 屬性值。如果您在計算資料行時使用這些運算式、建立索引鍵條件約束，或將純量 UDF 的傳回值設為具決定性以便編製索引 (如下列範例所示)，會很有幫助。

USE tempdb;
GO
-- This statement fails because the PRIMARY KEY cannot accept NULL values
-- and the nullability of the COALESCE expression for col2 
-- evaluates to NULL.
CREATE TABLE #Demo 
( 
col1 integer NULL, 
col2 AS COALESCE(col1, 0) PRIMARY KEY, 
col3 AS ISNULL(col1, 0) 
); 

-- This statement succeeds because the nullability of the 
-- ISNULL function evaluates AS NOT NULL.

CREATE TABLE #Demo 
( 
col1 integer NULL, 
col2 AS COALESCE(col1, 0), 
col3 AS ISNULL(col1, 0) PRIMARY KEY 
);

ISNULL 和 COALESCE 的驗證也不同。例如，ISNULL 的 Null 值會轉換成 int，而針對 COALESCE，您必須提供資料類型。
ISNULL 只使用 2 個參數，而 COALESCE 則使用變動數目的參數。

範例

A.執行簡單範例

下列範例示範 COALESCE 如何從具有非 Null 值的第一個資料行選取資料。這個範例會使用 AdventureWorks2012 資料庫。

SELECT Name, Class, Color, ProductNumber,
COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product;

B.執行複雜範例

在下列範例中，wages 資料表有三個含員工年薪 (時薪、月薪加上分紅) 相關資訊的資料行。不過，員工只會收到其中一種款項。若要算出支付給所有員工的總金額，請使用 COALESCE，只接收 hourly_wage、salary 和 commission 中的非 Null 值。

SET NOCOUNT ON;
GO
USE tempdb;
IF OBJECT_ID('dbo.wages') IS NOT NULL
    DROP TABLE wages;
GO
CREATE TABLE dbo.wages
(
    emp_id        tinyint   identity,
    hourly_wage   decimal   NULL,
    salary        decimal   NULL,
    commission    decimal   NULL,
    num_sales     tinyint   NULL
);
GO
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)
VALUES
    (10.00, NULL, NULL, NULL),
    (20.00, NULL, NULL, NULL),
    (30.00, NULL, NULL, NULL),
    (40.00, NULL, NULL, NULL),
    (NULL, 10000.00, NULL, NULL),
    (NULL, 20000.00, NULL, NULL),
    (NULL, 30000.00, NULL, NULL),
    (NULL, 40000.00, NULL, NULL),
    (NULL, NULL, 15000, 3),
    (NULL, NULL, 25000, 2),
    (NULL, NULL, 20000, 6),
    (NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52, 
   salary, 
   commission * num_sales) AS money) AS 'Total Salary' 
FROM dbo.wages
ORDER BY 'Total Salary';
GO

以下為結果集：

Total Salary

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

10000.00

20000.00

20800.00

30000.00

40000.00

41600.00

45000.00

50000.00

56000.00

62400.00

83200.00

120000.00

(12 row(s) affected)

請參閱

參考

ISNULL (Transact-SQL)

CASE (Transact-SQL)

共用方式為