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

語法Syntax

``````COALESCE ( expression [ ,...n ] )
``````

引數Arguments

expressionexpression

比較 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
``````

``````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. 針對 `ISNULL``COALESCE`，結果運算式的可 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` 會被視為 `NULL`By 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. 適用於 `ISNULL``COALESCE` 的驗證也不一樣。Validations for `ISNULL` and `COALESCE` are also different. 例如，`ISNULL``NULL` 值會轉換為 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

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

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

``````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)
``````

C.簡單範例C: Simple Example

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

``````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
``````

D.複雜範例D: Complex Example

``````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;
``````

``````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
``````