CASE (Transact-SQL)
一連の条件を評価して、考えられる結果式のうちの 1 つを返します。
CASE 式には 2 つの形式があります。
単純 CASE 式では、1 つの式を一連の単純式と比較して結果を決定します。
検索 CASE 式では、一連のブール式を評価して結果を判定します。
どちらの形式も、ELSE 引数 (省略可) をサポートしています。
CASE は、有効な式を使用できる任意のステートメントや句で使用できます。たとえば、SELECT、UPDATE、DELETE、SET などのステートメントや、select_list、IN、WHERE、ORDER BY、HAVING などの句で使用できます。
.gif "トピック リンク アイコン")
構文
Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Searched CASE expression:
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
引数
input_expression
単純 CASE 形式を使用した場合に評価される式です。input_expression は、任意の有効な式です。WHEN when_expression
単純 CASE 形式を使用した場合に input_expression と比較される単純式です。when_expression は、任意の有効な式です。input_expression と各 when_expression のデータ型は同一であるか、暗黙的な変換によって同一の型になる必要があります。THEN result_expression
input_expression = when_expression が TRUE になるとき、または Boolean_expression が TRUE になるときに返される式です。result expression は任意の有効な式です。ELSE else_result_expression
比較操作の評価がいずれも TRUE でなかった場合に返される式です。この引数を省略し、比較操作のいずれも TRUE でなかった場合、CASE は NULL を返します。else_result_expression は任意の有効な式です。else_result_expression と任意の result_expression のデータ型は同一であるか、暗黙的な変換によって同一の型になる必要があります。WHEN Boolean_expression
検索 CASE 形式で評価するブール式です。Boolean_expression は任意の有効なブール式です。
戻り値の型
result_expressions およびオプションの else_result_expression の型のセットの中から、最も優先順位の高い型を返します。詳細については、「データ型の優先順位 (Transact-SQL)」を参照してください。
戻り値
単純 CASE 式 :
単純 CASE 式では、最初の式が各 WHEN 句の式と比較されて、等しいかどうかが確認されます。等しかった場合は、THEN 句の式が返されます。
実行できるのは、等しいかどうかのチェックだけです。
input_expression を評価し、次に指定の順序で各 WHEN 句の input_expression = when_expression を評価します。
TRUE と評価された最初の input_expression = when_expression の result_expression を返します。
input_expression = when_expression の評価がいずれも TRUE でなかった場合、SQL Server データベース エンジンは、ELSE 句が指定されていれば else_result_expression を、ELSE 句が指定されていない場合は NULL を返します。
検索 CASE 式 :
各 WHEN 句の Boolean_expression を指定の順序で評価します。
TRUE と評価された最初の Boolean_expression の result_expression を返します。
Boolean_expression の評価がいずれも TRUE でなかった場合、データベース エンジンは、ELSE 句が指定されていれば else_result_expression を、ELSE 句が指定されていない場合は NULL を返します。
説明
SQL Server では、Case 式に入れ子にできるのは 10 レベルまでです。
CASE 式を使用して、Transact-SQL ステートメント、ステートメント ブロック、ユーザー定義関数、およびストアド プロシージャの実行フローを制御することはできません。フロー制御の方法の一覧については、「流れ制御言語 (Transact-SQL)」を参照してください。
CASE ステートメントは、条件を順番に評価して、最初に満たされた条件で停止します。場合によっては、CASE ステートメントの前に式が評価され、その式の結果を CASE ステートメントが入力として受け取ることもあります。そして、それらの式の評価でエラーが発生する可能性もあります。CASE ステートメントの WHEN 引数に集計式が含まれている場合、それらの集計式は先に評価されて CASE ステートメントに渡されます。たとえば次のクエリでは、MAX 集計の値を計算するときに 0 除算エラーが発生しますが、このエラーは、CASE 式を評価する前に発生します。
WITH Data (value) AS
(
SELECT 0
UNION ALL
SELECT 1
)
SELECT
CASE
WHEN MIN(value) <= 0 THEN 0
WHEN MAX(1/value) >= 100 THEN 1
END
FROM Data ;
WHEN の条件が評価される順序に依存する式を作成できるのは、スカラー式 (スカラーを返す非相関サブクエリを含む) の場合だけです。集計式の場合はできません。
使用例
A. SELECT ステートメントを単純 CASE 式と共に使用する
SELECT ステートメント内では、単純 CASE 式は等しいかどうかのチェックだけを実行できます。これ以外の比較操作は実行できません。CASE 式を使用して、製品ラインのカテゴリの表示をわかりやすいものに変更する例を次に示します。
USE AdventureWorks2008R2;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO
B. SELECT ステートメントを検索 CASE 式と共に使用する
SELECT ステートメント内では、検索 CASE 式は比較値に基づいて結果セット内で値を置換できます。次の例では、表示価格を、製品の価格範囲に基づいたテキスト コメントとして表示しています。
USE AdventureWorks2008R2;
GO
SELECT ProductNumber, Name, 'Price Range' =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;
GO
C. Microsoft Access で使用される IIf 関数を CASE で置き換える
CASE は、Microsoft Access の IIf 関数と同様の機能を提供します。次の例は、IIf を使用して、db1.ContactInfo という名前の Access テーブルの TelephoneInstructions 列に値を出力する簡単なクエリを示しています。
SELECT FirstName, LastName, TelephoneNumber,
IIf(IsNull(TelephoneInstructions),"Any time",
TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo;
次の例では、CASE を使用して、AdventureWorks2008R2 の Person.vAdditionalContactInfo ビューの TelephoneSpecialInstructions 列に値を出力します。
USE AdventureWorks2008R2;
GO
SELECT FirstName, LastName, TelephoneNumber, 'When to Contact' =
CASE
WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'
ELSE TelephoneSpecialInstructions
END
FROM Person.vAdditionalContactInfo;
D. ORDER BY 句で CASE を使用する
次の例では、ORDER BY 句で CASE 式を使用して、指定された列の値に基づいて行の並べ替え順序を決定しています。最初の例では、HumanResources.Employee テーブルの SalariedFlag 列の値を評価します。SalariedFlag が 1 に設定されている従業員は EmployeeID の降順で、SalariedFlag が 0 に設定されている従業員は EmployeeID の昇順で返されます。2 番目の例では、列 CountryRegionName が 'United States' と等しい場合は結果セットが列 TerritoryName によって並べ替えられ、他のすべての列は CountryRegionName によって並べ替えられます。
SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
GO
SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
ELSE CountryRegionName END;
E. UPDATE ステートメントで CASE を使用する
次の例では、UPDATE ステートメントで CASE 式を使用して、SalariedFlag が 0 に設定されている従業員の VacationHours 列の値を決定しています。VacationHours の値を 10 時間差し引くと値がマイナスになる場合は 40 時間、それ以外の場合は 20 時間、VacationHours の値を増やします。OUTPUT 句は、この処理の前後の VacationHours の値を表示するために使用されています。
USE AdventureWorks2008R2;
GO
UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
ELSE (VacationHours + 20.00)
END
)
OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue,
Inserted.VacationHours AS AfterValue
WHERE SalariedFlag = 0;
F. SET ステートメントで CASE を使用する
次の例では、テーブル値関数 dbo.GetContactInfo の SET ステートメントで CASE 式を使用しています。AdventureWorks2008R2 データベースでは、人に関連するデータはすべて Person.Person テーブルに格納されています。たとえば、従業員、仕入先の代表者、消費者などはすべて人に関連するデータとして扱われます。この関数は、指定された BusinessEntityID の氏名と連絡先タイプを返します。ContactType 列に表示される値は、SET ステートメント内の CASE 式により、Employee、Vendor、または Customer のどのテーブルに BusinessEntityID 列が含まれているかに基づいて決定されます。
USE AdventureWorks2008R2;
GO
CREATE FUNCTION dbo.GetContactInformation(@BusinessEntityID int)
RETURNS @retContactInformation TABLE
(
BusinessEntityID int NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
ContactType nvarchar(50) NULL,
PRIMARY KEY CLUSTERED (BusinessEntityID ASC)
)
AS
-- Returns the first name, last name and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@BusinessEntityID = BusinessEntityID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Person
WHERE BusinessEntityID = @BusinessEntityID;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e
WHERE e.BusinessEntityID = @BusinessEntityID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.BusinessEntityContact AS bec
WHERE bec.BusinessEntityID = @BusinessEntityID)
THEN 'Vendor'
-- Check for store
WHEN EXISTS(SELECT * FROM Purchasing.Vendor AS v
WHERE v.BusinessEntityID = @BusinessEntityID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Customer AS c
WHERE c.PersonID = @BusinessEntityID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @BusinessEntityID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @BusinessEntityID, @FirstName, @LastName, @ContactType;
END;
RETURN;
END;
GO
SELECT BusinessEntityID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(2200);
GO
SELECT BusinessEntityID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(5);
G. HAVING 句で CASE を使用する
次の例では、HAVING 句で CASE 式を使用して、SELECT ステートメントで返される行を制限しています。このステートメントは、HumanResources.Employee テーブル内の各役職の最も高い時給を返します。HAVING 句では、最も高い時給が男性の場合には 40 ドル、女性の場合には 42 ドルを超えている役職のみが返されるように制限しています。
USE AdventureWorks2008R2;
GO
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = 'M'
THEN ph1.Rate
ELSE NULL END) > 40.00
OR MAX(CASE WHEN Gender = 'F'
THEN ph1.Rate
ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;