CASE (Transact-SQL)

[アーティクル]
12/16/2008

更新 : 2006 年 4 月 14 日

一連の条件を評価して、考えられる結果式のうちの 1 つを返します。

CASE 式には、次の 2 つの形式があります。

単純 CASE 式では、1 つの式を一連の単純式と比較して結果を決定します。
検索 CASE 式では、一連のブール式を評価して結果を決定します。

どちらの形式も、ELSE 引数 (省略可) をサポートしています。

CASE は有効な式を含めることができる任意のステートメントまたは句で使用できます。たとえば、CASE は SELECT、UPDATE、DELETE、および SET などのステートメント、および select_list、IN、WHERE、ORDER BY、および HAVING などの句で使用できます。

トピックリンクアイコン Transact-SQL 構文表記規則

構文

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)」を参照してください。

解説

SQL Server では、Case 式に入れ子にできるのは 10 レベルまでです。

CASE 式は、Transact-SQL ステートメント、ステートメントブロック、ユーザー定義関数、およびストアドプロシージャの実行のフローを制御するためには使用できません。フロー制御のメソッドの一覧については、「流れ制御言語 (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 を返します。

例

A. SELECT ステートメントを単純 CASE 式と共に使用する

SELECT ステートメント内では、単純 CASE 式は等しいかどうかのチェックだけを実行できます。これ以外の比較操作は実行できません。CASE 式を使用して、製品ラインのカテゴリの表示をわかりやすいものに変更する例を次に示します。

USE AdventureWorks;
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 AdventureWorks;
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 を使用して、AdventureWorks の Person.vAdditionalContactInfo ビューの TelephoneSpecialInstructions 列に値を出力します。

USE AdventureWorks;
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 の昇順に返されます。

SELECT EmployeeID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN EmployeeID END DESC
        ,CASE WHEN SalariedFlag = 0 THEN EmployeeID END;
GO

E. UPDATE ステートメントで CASE を使用する

次の例では、UPDATE ステートメントで CASE 式を使用して、SalariedFlag が 0 に設定されている従業員の VacationHours 列に設定された値を決定します。 VacationHours から 10 時間を減算すると負の値になる場合は、VacationHours を 40 時間増やし、それ以外の場合は VacationHours を 20 時間増やします。OUTPUT 句は、休暇の前と後の値を表示する場合に使用します。

USE AdventureWorks;
GO
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
         ELSE (VacationHours + 20.00)
       END
    )
OUTPUT Deleted.EmployeeID, Deleted.VacationHours AS BeforeValue, 
       Inserted.VacationHours AS AfterValue
WHERE SalariedFlag = 0;

F. SET ステートメントで CASE を使用する

次の例では、テーブル値関数 dbo.GetContactInfo の SET ステートメントで CASE 式を使用しています。AdventureWorks データベースでは、人に関連するすべてのデータは Person.Contact テーブルに保存されます。たとえば、従業員、仕入先の担当者、販売店担当者、または消費者などのデータがあります。この関数では、特定の ContactID の姓名、およびその人の連絡先タイプが返されます。SET ステートメント内の CASE 式では、Employee、StoreContact、VendorContact、または Individual (消費者) テーブル内の ContactID の存在に基づき、表示する ContactType 列の値が決定されます。

USE AdventureWorks;
GO
CREATE FUNCTION dbo.GetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE 
(
    ContactID int NOT NULL,
    FirstName nvarchar(50) NULL,
    LastName nvarchar(50) NULL,
    ContactType nvarchar(50) NULL,
    PRIMARY KEY CLUSTERED (ContactID 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 
        @ContactID = ContactID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM Person.Contact 
    WHERE ContactID = @ContactID;

    SET @ContactType = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e 
                WHERE e.ContactID = @ContactID) 
                THEN 'Employee'

            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc 
                    INNER JOIN Person.ContactType AS ct 
                    ON vc.ContactTypeID = ct.ContactTypeID 
                WHERE vc.ContactID = @ContactID) 
                THEN 'Vendor Contact'

            -- Check for store
            WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc 
                    INNER JOIN Person.ContactType AS ct 
                    ON sc.ContactTypeID = ct.ContactTypeID 
                WHERE sc.ContactID = @ContactID) 
                THEN 'Store Contact'

            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Sales.Individual AS i 
                WHERE i.ContactID = @ContactID) 
                THEN 'Consumer'
        END;

    -- Return the information to the caller
    IF @ContactID IS NOT NULL 
    BEGIN
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @ContactType;
    END;

    RETURN;
END;
GO
SELECT ContactID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, ContactType
FROM dbo.GetContactInformation(5);

G. HAVING 句で CASE を使用する

次の例では、HAVING 句で CASE 式を使用して SELECT ステートメントによって返される行を制限します。このステートメントでは、HumanResources.Employee テーブルに含まれる役職ごとに最大の時給が返されます。HAVING 句では、役職は、最大時給が 40 ドルより多い男性、または最大時給が 42 ドルより多い女性が就いている役職に制限されます。

USE AdventureWorks;
GO
SELECT Title, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.EmployeeID = ph1.EmployeeID
GROUP BY Title
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;

参照

その他の技術情報

CASE の使用

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース	履歴
2006 年 4 月 14 日	追加内容 : CASE を使用して IIf 機能を実装する例を追加しました。