COALESCE (Transact-SQL)

發行項
01/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 而言，結果運算式的 NULLability 並不同。 ISNULL 傳回值一律視為 NOT NULLable (假設傳回值是不可為 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)