COALESCE (Transact-SQL)
適用対象: 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
と評価されない最初の式の現在の値を返します。 たとえば、SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');
では、3 番目の値が null ではない最初の値であるために、3 番目の値が返されます。
COALESCE ( expression [ ,...n ] )
式 (expression)
任意のデータ型の式を指定します。
式のデータ型のうち、最も優先順位が高いものを返します。 すべての式で NULL 値が許可されない場合、結果は NULL 値が許可されない型になります。
すべての引数が NULL
である場合、COALESCE
は NULL
を返します。 NULL 値の少なくとも 1 つは、型指定された NULL
である必要があります。
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;
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
はさまざまな数のパラメーターを受け取ります。
次の例では、COALESCE
を使用して、NULL 以外の値を持つ最初の列からデータを選択する方法を示します。 この例では、AdventureWorks2022 データベースを使います。
SELECT Name, Class, Color, ProductNumber,
COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product;
次の例では、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)
次の例では、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
のパラメーターとして指定されていないためです。
次の例では、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