• 發行項

COALESCE (Transact-SQL)

傳回其引數中第一個非 Null 的運算式。

主題連結圖示Transact-SQL 語法慣例

語法

COALESCE ( expression [ ,...n ] )

引數

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

傳回類型

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

備註

如果所有引數都是 NULL，COALESCE 便會傳回 NULL。

[!附註]

至少其中一個 Null 值必須是 NULL 類型。

COALESCE(expression1,...n) 等於下列 CASE 運算式：

CASE

   WHEN (expression1 IS NOT NULL) THEN expression1

   WHEN (expression2 IS NOT NULL) THEN expression2

   ...

   ELSE expressionN

END

ISNULL 和 COALESCE 雖然相當，但行為可能不同。使用 ISNULL 和非 Null 參數的運算式會視為是非 NULL，而使用 COALESCE 和非 Null 參數的運算式則會視為是 NULL。在 SQL Server 中，對於使用 COALESCE 和非 Null 參數的索引運算式，計算的資料行可以使用 PERSISTED 資料行屬性保存，如下列陳述式所示：

CREATE TABLE #CheckSumTest 
    (
        ID int identity ,
        Num int DEFAULT ( RAND() * 100 ) ,
        RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
    );

範例

簡單範例

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

USE AdventureWorks ;

GO

SELECT Name, Class, Color, ProductNumber,

COALESCE(Class, Color, ProductNumber) AS FirstNotNull

FROM Production.Product ;

GO

複雜範例

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

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

20800.0000

41600.0000

62400.0000

83200.0000

10000.0000

20000.0000

30000.0000

40000.0000

45000.0000

50000.0000

120000.0000

56000.0000

(12 個資料列受到影響)