COALESCE (Transact-SQL)

[アーティクル]
05/23/2023

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)Microsoft Fabric の SQL 分析エンドポイント Microsoft Fabric のウェアハウス

引数を順番に評価し、NULL と評価されない最初の式の現在の値を返します。たとえば、SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); では、3 番目の値が null ではない最初の値であるために、3 番目の値が返されます。

Transact-SQL 構文表記規則

構文

COALESCE ( expression [ ,...n ] )

引数

式 (expression)
任意のデータ型の式を指定します。

Note

SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

戻り値の型

式のデータ型のうち、最も優先順位が高いものを返します。すべての式で 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 など) が複数回評価されます。サブクエリを含む値式は不明確な式と見なされ、サブクエリは 2 回評価されます。この結果は、SQL 標準に準拠しています。どちらの場合も、最初の評価とその後の評価で返される結果が異なります。

たとえば、COALESCE((subquery), 1) というコードを実行すると、サブクエリは 2 回評価されます。その結果、クエリの分離レベルによっては、得られる結果が異なる場合があります。たとえば、マルチユーザー環境の READ COMMITTED 分離レベルでは、このコードによって NULL 値が返される場合があります。安定した結果が返されるようにするには、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 式の規則に従って、優先順位が最も高い値のデータ型が返されます。
結果式の 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 以外の値を持つ最初の列からデータを選択する方法を示します。この例では、AdventureWorks2022 データベースを使います。

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)

C: 簡単な例

次の例では、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 値が Socks, Mens でなく PN1278 であることに着目してください。この値がこうなるのは、この例で、Name 列が COALESCE のパラメーターとして指定されていないためです。

D:複雑な例

次の例では、COALESCE を使用して 3 つの列の値を比較し、列で検索された null 以外の値のみを返します。

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

参照

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

COALESCE (Transact-SQL)

構文

引数

戻り値の型

解説

COALESCE と CASE の比較

COALESCE と ISNULL の比較

例

A. 簡単な例を実行する

B. 複雑な例を実行する

C: 簡単な例

D:複雑な例

参照

フィードバック

フィードバック

その他のリソース