COALESCE (Transact-SQL)COALESCE (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

依序評估引數,並傳回一開始未評估為 NULL 之第一個運算式的目前值。Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. 例如,SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); 會傳回第三個值,因為第三個值是第一個非 Null 的值。For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn't null.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

COALESCE ( expression [ ,...n ] )   

引數Arguments

expressionexpression
這是任何類型的運算式Is an expression of any type.

傳回類型Return Types

傳回具有最高資料類型優先順序的 expression 資料類型。Returns the data type of expression with the highest data type precedence. 如果所有運算式都不可為 Null,結果的類型也是不可為 Null。If all expressions are nonnullable, the result is typed as nonnullable.

RemarksRemarks

如果所有引數均為 NULLCOALESCE 就會傳回 NULLIf all arguments are NULL, COALESCE returns NULL. 至少其中一個 Null 值必須是 NULL 類型。At least one of the null values must be a typed NULL.

比較 COALESCE 和 CASEComparing COALESCE and CASE

COALESCE 運算式是 CASE 運算式的語法捷徑。The COALESCE expression is a syntactic shortcut for the CASE expression. 也就是說,查詢最佳化工具會將程式碼 COALESCE(expression1, ...n) 重寫為下列 CASE 運算式:That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:

CASE  
WHEN (expression1 IS NOT NULL) THEN expression1  
WHEN (expression2 IS NOT NULL) THEN expression2  
...  
ELSE expressionN  
END  

如此,會多次評估輸入值 (expression1expression2expressionN 等等)。As such, the input values (expression1, expression2, expressionN, and so on) are evaluated multiple times. 包含子查詢的值運算式會視為不具決定性,且會評估子查詢兩次。A value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. 此結果符合 SQL 標準。This result is in compliance with the SQL standard. 不論是哪一種情況,第一次評估和後續評估之間都會傳回不同的結果。In either case, different results can be returned between the first evaluation and upcoming evaluations.

例如,執行程式碼 COALESCE((subquery), 1) 時,會評估子查詢兩次。For example, when the code COALESCE((subquery), 1) is executed, the subquery is evaluated twice. 因此,您會根據查詢的隔離等級取得不同的結果。As a result, you can get different results depending on the isolation level of the query. 例如,程式碼在多使用者環境的 READ COMMITTED 隔離等級下,會傳回 NULLFor example, the code can return NULL under the READ COMMITTED isolation level in a multi-user environment. 為了確保會傳回穩定的結果,請使用 SNAPSHOT ISOLATION 隔離等級,或以 ISNULL 函數取代 COALESCETo ensure stable results are returned, use the SNAPSHOT ISOLATION isolation level, or replace COALESCE with the ISNULL function. 或者,您可以重寫查詢,將子查詢推送至子選擇,如下列範例所示:As an alternative, you can rewrite the query to push the subquery into a subselect as shown in the following example:

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 和 ISNULLComparing COALESCE and ISNULL

ISNULL 函數和 COALESCE 運算式具有相似的目的,但運作方式可能不同。The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

  1. 因為 ISNULL 是函式,所以只會評估一次。Because ISNULL is a function, it's evaluated only once. 如上所述,可能會評估多次 COALESCE 運算式的輸入值。As described above, the input values for the COALESCE expression can be evaluated multiple times.

  2. 結果運算式所決定的資料類型會不同。Data type determination of the resulting expression is different. ISNULL 使用第一個參數的資料類型,而 COALESCE 會遵循 CASE 運算式規則,並傳回具有最高優先順序之值的資料類型。ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

  3. 針對 ISNULLCOALESCE,結果運算式的可 NULL 性不同。The NULLability of the result expression is different for ISNULL and COALESCE. ISNULL 傳回值一律視為不可為 NULL (假設傳回值是不可為 Null 的值)。The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one). 相反地,具有非 Null 參數的 COALESCE 會被視為 NULLBy contrast,COALESCE with non-null parameters is considered to be NULL. 因此,運算式 ISNULL(NULL, 1)COALESCE(NULL, 1) 雖然相等,卻有不同的可為 Null 值。So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equal, have different nullability values. 當您在計算資料行中使用這些運算式、建立索引鍵條件約束,或使純量 UDF 的傳回值具確定性時,這些值會產生差異,以便編製索引,如下列範例所示:These values make a difference if you're using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example:

    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   
    );  
    
  4. 適用於 ISNULLCOALESCE 的驗證也不一樣。Validations for ISNULL and COALESCE are also different. 例如,ISNULLNULL 值會轉換為 int;而針對 COALESCE,您卻必須提供資料類型。For example, a NULL value for ISNULL is converted to int though for COALESCE, you must provide a data type.

  5. ISNULL 只接受兩個參數。ISNULL takes only two parameters. 相較之下,COALESCE 接受數目不定的參數。By contrast COALESCE takes a variable number of parameters.

範例Examples

A.A. 執行簡單範例Running a simple example

下列範例示範 COALESCE 如何從具有非 Null 值的第一個資料行選取資料。The following example shows how COALESCE selects the data from the first column that has a nonnull value. 這個範例會使用 AdventureWorks2012AdventureWorks2012 資料庫。This example uses the AdventureWorks2012AdventureWorks2012 database.

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

B.B. 執行複雜範例Running a complex example

在下列範例中,wages 資料表有三個含員工年薪 (時薪、月薪加上分紅) 相關資訊的資料行。In the following example, the wages table includes three columns that contain information about the yearly wages of the employees: the hourly wage, salary, and commission. 不過,員工只會收到其中一種款項。However, an employee receives only one type of pay. 若要算出支付給所有員工的總金額,請使用 COALESCE,只接收 hourly_wagesalarycommission 中的非 Null 值。To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in hourly_wage, salary, and commission.

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  

以下為結果集:Here is the result set.

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)

C.簡單範例C: Simple Example

下列範例示範 COALESCE 如何從具有非 Null 值的第一個資料行選取資料。The following example demonstrates how COALESCE selects the data from the first column that has a non-null value. 此範例假設 Products 資料表包含此資料:Assume for this example that the Products table contains this data:

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

我們接著執行下列 COALESCE 查詢:We then run the following COALESCE query:

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

以下為結果集:Here is the result set.

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

請注意,在第一個資料列中,FirstNotNull 值是 PN1278,而非 Socks, MensNotice that in the first row, the FirstNotNull value is PN1278, not Socks, Mens. 這個值會這樣,是因為在範例中,未將 Name 資料行指定為 COALESCE 的參數。This value is this way because the Name column wasn't specified as a parameter for COALESCE in the example.

D.複雜範例D: Complex Example

下列範例會使用 COALESCE 來比較三個資料行中的值,並且只傳回在資料行中找到的非 Null 值。The following example uses COALESCE to compare the values in three columns and return only the non-null value found in the columns.

CREATE TABLE dbo.wages  
(  
    emp_id        tinyint   NULL,  
    hourly_wage   decimal   NULL,  
    salary        decimal   NULL,  
    commission    decimal   NULL,  
    num_sales     tinyint   NULL  
);  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (1, 10.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (2, 20.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (3, 30.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (4, 40.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (5, NULL, 10000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (6, NULL, 20000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (7, NULL, 30000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (8, NULL, 40000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (9, NULL, NULL, 15000, 3);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (10,NULL, NULL, 25000, 2);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (11, NULL, NULL, 20000, 6);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (12, NULL, NULL, 14000, 4);  
  
SELECT CAST(COALESCE(hourly_wage * 40 * 52,   
   salary,   
   commission * num_sales) AS decimal(10,2)) AS TotalSalary   
FROM dbo.wages  
ORDER BY TotalSalary;  

以下為結果集:Here is the result set.

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

另請參閱See Also

ISNULL (Transact-SQL) ISNULL (Transact-SQL)
CASE (Transact-SQL)CASE (Transact-SQL)