COALESCE (Transact-SQL)

發行項
01/29/2025

適用於：sql Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System （PDW） SQL 分析端點在 Microsoft Fabric SQL 資料庫中Microsoft網狀架構倉儲Microsoft網狀架構

依序評估引數，並傳回一開始未評估為 NULL 之第一個運算式的目前值。下列範例會傳回第三個值，因為第三個值是非 Null 的第一個值。

SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');

注意

如果您想要串連字串，請改用 STRING_AGG。

Transact-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) 時，會評估子查詢兩次。因此，您會根據查詢的隔離等級取得不同的結果。例如，程式碼在多使用者環境的 NULL 隔離等級下，會傳回 READ COMMITTED。為了確保會傳回穩定的結果，請使用 SNAPSHOT ISOLATION 隔離等級，或以 COALESCE 函數取代 ISNULL。或者，您可以重寫查詢，將子查詢推送至子選擇，如下列範例所示：

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 傳回值一律視為 NOT 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 INT 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 INT NULL,
    col2 AS COALESCE (col1, 0),
    col3 AS ISNULL(col1, 0) PRIMARY KEY
);
```
適用於 ISNULL 和 COALESCE 的驗證也不一樣。例如，NULL 的 ISNULL 值會轉換為 int；而針對 COALESCE，您卻必須提供資料類型。
ISNULL 只接受兩個參數。相較之下，COALESCE 接受數目不定的參數。

範例

本文中的程式代碼範例會使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，您可以從 Microsoft SQL Server 範例和社群專案首頁下載。

A. 從具有非 Null 值的第一個數據行傳回數據

下列範例示範 COALESCE 如何從具有非 Null 值的第一個資料行選取資料。此範例假設 Products 資料表包含此資料：

Name         Color      ProductNumber
------------ ---------- -------------
Socks, Mens  NULL       PN1278
Socks, Mens  Blue       PN1965
NULL         White      PN9876

然後，我們會執行下列 COALESCE 查詢：

SELECT Name,
       Color,
       ProductNumber,
       COALESCE (Color, ProductNumber) AS FirstNotNull
FROM Products;

結果集如下所示。

Name         Color      ProductNumber  FirstNotNull
------------ ---------- -------------  ------------
Socks, Mens  NULL       PN1278         PN1278
Socks, Mens  Blue       PN1965         Blue
NULL         White      PN9876         White

在第一個資料列中，FirstNotNull 值為 PN1278，而不是 Socks, Mens。這個值會這樣，是因為在範例中，未將 Name 資料行指定為 COALESCE 的參數。

B. 傳回工資數據表中的非 Null 值

在下列範例中，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

共用方式為

COALESCE (Transact-SQL)

語法

引數

expression

傳回型別

備註

比較 COALESCE 和 CASE

比較 COALESCE 和 ISNULL

範例

A. 從具有非 Null 值的第一個數據行傳回數據

B. 傳回工資數據表中的非 Null 值

意見反應

其他資源

共用方式為

COALESCE (Transact-SQL)

語法

引數

expression

傳回型別

備註

比較 COALESCE 和 CASE

比較 COALESCE 和 ISNULL

範例

A. 從具有非 Null 值的第一個數據行傳回數據

B. 傳回工資數據表中的非 Null 值

相關內容

意見反應

其他資源