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 値の少なくとも 1 つは、型指定された 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 標準に準拠して、サブクエリを含む値式は不明確な式と見なされ、サブクエリは 2 回評価されます。どちらの場合も、最初の評価とその後の評価で返される結果が異なります。

たとえば、COALESCE((subquery), 1) というコードを実行すると、サブクエリは 2 回評価されます。その結果、クエリの分離レベルによっては、得られる結果が異なる場合があります。たとえば、マルチユーザー環境の READ COMMITTED 分離レベルでは、このコードによって NULL 値が返される場合があります。安定した結果が返されるようにするには、SNAPSHOT ISOLATION 分離レベルを使用するか、COALESE を 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 式の規則に従って、優先順位が最も高い値のデータ型が返されます。
結果式の NULL 値の許容は、ISNULL と COALESCE で異なります。 ISNULL の戻り値は、常に 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 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 テーブルに、従業員の年俸に関する情報 (時給、給与、歩合) が含まれている 3 つの列を含めています。ただし、1 人の従業員が受け取る給与の種類は 1 つだけです。すべての従業員に支払われている給料の総額を算出するには、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)

Share via