COALESCE (Transact-SQL)

[アーティクル]
01/29/2025

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

引数を順番に評価し、NULL と評価されない最初の式の現在の値を返します。次の例では、3 番目の値が null ではない最初の値であるため、3 番目の値が返されます。

SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');

手記

文字列を連結する場合は、代わりに STRING_AGG を使用します。

Transact-SQL 構文表記規則

構文

COALESCE ( expression [ , ...n ] )

引数

式 (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 など) が複数回評価されます。サブクエリを含む値式は非決定的と見なされ、サブクエリは 2 回評価されます。この結果は、SQL 標準に準拠しています。どちらの場合も、最初の評価とその後の評価で返される結果が異なります。

たとえば、COALESCE((subquery), 1) というコードを実行すると、サブクエリは 2 回評価されます。その結果、クエリの分離レベルによっては、得られる結果が異なる場合があります。たとえば、マルチユーザー環境の NULL 分離レベルでは、このコードによって READ COMMITTED 値が返される場合があります。安定した結果が返されるようにするには、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 戻り値は常に NOT 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 INT 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 INT NULL,
    col2 AS COALESCE (col1, 0),
    col3 AS ISNULL(col1, 0) PRIMARY KEY
);
```
ISNULL と COALESCE の妥当性検査も異なります。たとえば、NULL の ISNULL 値は int に変換されますが、COALESCE の場合は、データ型を指定する必要があります。
ISNULL は、2 つのパラメーターのみを受け取ります。これに対し COALESCE はさまざまな数のパラメーターを受け取ります。

例

この記事のコードサンプルでは、AdventureWorks2022 または AdventureWorksDW2022 サンプルデータベースを使用します。このサンプルデータベースは、Microsoft SQL Server サンプルとコミュニティプロジェクトホームページからダウンロードできます。

A. null 以外の値を持つ最初の列からデータを返す

次の例では、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

最初の行では、Socks, Mensではなく、FirstNotNull 値が PN1278されます。この値がこうなるのは、この例で、Name 列が COALESCE のパラメーターとして指定されていないためです。

B. ウェイジテーブル内の null 以外の値を返します

次の例では、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

次の方法で共有

COALESCE (Transact-SQL)

構文

引数

式 (expression)

戻り値の型

解説

COALESCE と CASE の比較

COALESCE と ISNULL の比較

例

A. null 以外の値を持つ最初の列からデータを返す

B. ウェイジテーブル内の null 以外の値を返します

フィードバック

その他のリソース

次の方法で共有

COALESCE (Transact-SQL)

構文

引数

式 (expression)

戻り値の型

解説

COALESCE と CASE の比較

COALESCE と ISNULL の比較

例

A. null 以外の値を持つ最初の列からデータを返す

B. ウェイジ テーブル内の null 以外の値を返します

関連コンテンツ

フィードバック

その他のリソース

B. ウェイジテーブル内の null 以外の値を返します