MySQL - CASE式

提供: MochiuWiki : SUSE, EC, PCB

概要

CASE式は、条件分岐を実現するSQLの式であり、SELECT、UPDATE、ORDER BY 等、様々な句で使用できる。

CASE式には、単純CASE式と検索CASE式の2種類がある。
単純CASE式は値の等価比較を行い、検索CASE式は柔軟な条件式を指定できる。

CASE式は、上から順に条件を評価し、最初に真となる条件の結果を返す。
全ての条件が偽の場合、ELSE 句で指定した値を返す。
ELSE 句を省略した場合は NULL を返す。

CASE式のほかに、MySQLでは条件分岐を実現する関数として IF、IFNULL、NULLIF、COALESCE が提供されている。
これらの関数は、CASE式よりも簡潔に条件分岐を記述できるが、機能は限定的である。

CASE式は、SELECT 句での値変換、ORDER BY 句でのカスタムソート、UPDATE 文での条件付き更新、GROUP BY と組み合わせたカテゴリ別集計等、幅広い用途で使用される。

パフォーマンスの点では、WHERE 句でのCASE式はインデックスを活用できないため、SELECT 句や UPDATE 句での使用が効率的である。
また、WHEN 句の順序を最適化し、マッチ可能性の高い条件を先に配置することにより、評価回数を削減できる。


単純CASE式

単純CASE式は、指定した値と各 WHEN 句の値を等価演算子 (=) で比較する。

最初に真となる WHEN 句の結果を返し、そこで評価を終了する。
全ての WHEN 句が偽の場合、ELSE 句の値を返す。
ELSE 句を省略した場合は、NULL を返す。

基本構文

単純CASE式の基本構文は以下の通りである。

 CASE value
    WHEN compare_value1 THEN result1
    WHEN compare_value2 THEN result2
    ...
    [ELSE default_result]
 END


使用例

単純CASE式を使用した例を以下に示す。

 # 曜日番号を曜日名に変換
 SELECT
    order_id,
    CASE DAYOFWEEK(ordered_at)
       WHEN 1 THEN '日曜日'
       WHEN 2 THEN '月曜日'
       WHEN 3 THEN '火曜日'
       WHEN 4 THEN '水曜日'
       WHEN 5 THEN '木曜日'
       WHEN 6 THEN '金曜日'
       WHEN 7 THEN '土曜日'
       ELSE '不明'
    END AS weekday
    FROM orders;
 
 # ステータスコードをステータス名に変換
 SELECT
    order_id,
    CASE status
       WHEN 0 THEN '未処理'
       WHEN 1 THEN '処理中'
       WHEN 2 THEN '完了'
       WHEN 9 THEN 'キャンセル'
       ELSE '不明'
    END AS status_name
    FROM orders;
 
 # 商品カテゴリIDをカテゴリ名に変換
 SELECT
    product_id,
    product_name,
    CASE category_id
       WHEN 1 THEN '家電'
       WHEN 2 THEN '書籍'
       WHEN 3 THEN '食品'
       ELSE 'その他'
    END AS category_name
    FROM products;


単純CASE式は、等価比較 (=) のみを行うため、範囲指定や不等号を使用した比較はできない。
このような場合は、検索CASE式を使用する。


検索CASE式

検索CASE式は、各 WHEN 句で柔軟な条件式を指定できる。

不等号、AND、OR、IS NULL 等、様々な条件を指定できるため、単純CASE式よりも汎用性が高い。

基本構文

検索CASE式の基本構文は以下の通りである。

 CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ELSE default_result]
 END


使用例

検索CASE式を使用した例を以下に示す。

 # 価格帯で商品をカテゴリ分け
 SELECT
    product_name,
    price,
    CASE
       WHEN price < 1000 THEN 'economy'
       WHEN price >= 1000 AND price < 5000 THEN 'standard'
       WHEN price >= 5000 THEN 'premium'
       ELSE 'unknown'
    END AS price_category
    FROM products;
 
 # 給与範囲でランク付け
 SELECT
    name,
    salary,
    CASE
       WHEN salary < 30000 THEN 'ジュニア'
       WHEN salary >= 30000 AND salary < 60000 THEN 'ミドル'
       WHEN salary >= 60000 THEN 'シニア'
    END AS rank
    FROM employees;
 
 # 在庫状態の判定
 SELECT
    product_name,
    stock,
    CASE
       WHEN stock = 0 THEN '在庫なし'
       WHEN stock > 0 AND stock <= 10 THEN '残りわずか'
       WHEN stock > 10 THEN '在庫あり'
    END AS stock_status
    FROM products;


NULLの比較

NULLの比較には、IS NULL または IS NOT NULL を使用する必要がある。

WHEN col = NULL は常に偽となるため、正しく動作しない。

 # NULLの正しい比較方法
 SELECT
    name,
    CASE
       WHEN manager_id IS NULL THEN 'トップ'
       WHEN manager_id IS NOT NULL THEN '部下あり'
    END AS position
    FROM employees;
 
 # 誤った例 (col = NULLは常に偽)
 SELECT
    name,
    CASE
       WHEN manager_id = NULL THEN 'トップ'  # これは動作しない
       ELSE '部下あり'
    END AS position
    FROM employees;
 
 # 複数条件でのNULLチェック
 SELECT
    product_name,
    CASE
       WHEN discount IS NULL THEN '割引なし'
       WHEN discount > 0 AND discount <= 10 THEN '小割引'
       WHEN discount > 10 THEN '大割引'
    END AS discount_status
    FROM products;



IF関数

IF 関数は、3項演算子と等価な機能を提供する。

条件式が真の場合は第2引数を返し、偽またはNULLの場合は第3引数を返す。

基本構文

IF 関数の基本構文は以下の通りである。

 IF(expr, true_val, false_val)


  • expr
    評価する条件式
  • true_val
    expr が真の場合に返す値
  • false_val
    expr が偽 または NULL の場合に返す値


使用例

IF 関数を使用した例を以下に示す。

 # 在庫の有無を判定
 SELECT
    product_name,
    IF(stock > 0, '在庫あり', '在庫なし') AS stock_status
    FROM products;
 
 # 性別表示
 SELECT
    name,
    IF(gender = 'M', '男性', '女性') AS gender_name
    FROM users;
 
 # 割引適用判定
 SELECT
    product_name,
    price,
    IF(price >= 5000, price * 0.9, price) AS sale_price
    FROM products;
 
 # NULL判定
 SELECT
    name,
    IF(email IS NOT NULL, 'メールあり', 'メールなし') AS email_status
    FROM users;


ネストしたIF関数

IF 関数はネスト (入れ子) できるが可読性が低下するため、複雑な条件分岐にはCASE式を推奨する。

 # CASE式で書き換え (可読性が高い)
 SELECT
    product_name,
    price,
    CASE
       WHEN price < 1000 THEN 'economy'
       WHEN price < 5000 THEN 'standard'
       ELSE 'premium'
    END AS price_category
    FROM products;
 
 # ネストしたIF関数 (可読性が低い)
 SELECT
    product_name,
    price,
    IF(price < 1000, 'economy',
       IF(price < 5000, 'standard', 'premium')
    ) AS price_category
    FROM products;



IFNULL関数

IFNULL 関数は、第1引数がNULLの場合に第2引数を返す。

第1引数がNULLでない場合は、第1引数をそのまま返す。

基本構文

IFNULL 関数の基本構文は以下の通りである。

 IFNULL(expr1, expr2)


  • expr1
    評価する式
  • expr2
    expr1 が NULLの場合に返す値


IFNULL(expr1, expr2) は、COALESCE(expr1, expr2) と等価である。

使用例

IFNULL 関数を使用した例を以下に示す。

 # NULLをデフォルト値に置換
 SELECT
    name,
    IFNULL(phone, '未登録') AS phone_number
    FROM users;
 
 # NULLを0に置換して集計
 SELECT
    user_id,
    IFNULL(SUM(amount), 0) AS total_amount
    FROM orders
    GROUP BY user_id;
 
 # NULLを空文字列に置換
 SELECT
    product_name,
    IFNULL(description, '') AS description
    FROM products;


ゼロ除算への対応

IFNULL 関数は、ゼロ除算のエラーを回避するために使用できる。

 # ゼロ除算はNULLを返す
 SELECT IFNULL(1/0, 10) AS result;
 # 結果: 10
 
 # ゼロ除算を含む計算
 SELECT
    product_id,
    total_sales,
    total_orders,
    IFNULL(total_sales / total_orders, 0) AS avg_sales
    FROM product_stats;



NULLIF関数

NULLIF 関数は、2つの引数が等しい場合に NULL を返し、それ以外の場合は第1引数を返す。

この関数は、特定の値をNULLに変換したい場合に有用である。

基本構文

NULLIF 関数の基本構文は以下の通りである。

 NULLIF(expr1, expr2)


  • expr1
    評価する式
  • expr2
    比較する値


NULLIF(expr1, expr2) は、以下のCASE式と等価である。

 CASE
    WHEN expr1 = expr2 THEN NULL
    ELSE expr1
 END


使用例

NULLIF 関数を使用した例を以下に示す。

 # 空文字列をNULLに変換
 SELECT
    name,
    NULLIF(email, '') AS email
    FROM users;
 
 # 0をNULLに変換
 SELECT
    product_id,
    NULLIF(stock, 0) AS stock
    FROM products;
 
 # 特定の値をNULLに変換
 SELECT
    order_id,
    NULLIF(status, 'cancelled') AS active_status
    FROM orders;


ゼロ除算の防止

NULLIF 関数は、ゼロ除算を防ぐために使用できる。

 # ゼロ除算を防止
 SELECT
    user_id,
    total_amount,
    order_count,
    total_amount / NULLIF(order_count, 0) AS avg_amount
    FROM user_stats;
 # order_countが0の場合、NULLIFは0をNULLに変換するため、除算結果もNULLになる
 
 # IFNULLと組み合わせてデフォルト値を設定
 SELECT
    user_id,
    IFNULL(total_amount / NULLIF(order_count, 0), 0) AS avg_amount
    FROM user_stats;
 # order_countが0の場合、0を返す



COALESCE関数

COALESCE 関数は、引数リストの中で最初の非NULL値を返す。

全ての引数がNULLの場合、NULL を返す。

詳細は MySQL - COALESCE関数 のページを参照すること。

基本構文

COALESCE 関数の基本構文は以下の通りである。

 COALESCE(val1, val2, val3, ...)


使用例

COALESCE 関数を使用した例を以下に示す。

 # 複数のカラムから最初の非NULL値を取得
 SELECT
    user_id,
    COALESCE(mobile_phone, home_phone, office_phone, '連絡先なし') AS contact
    FROM users;
 
 # フォールバック値のチェーン
 SELECT
    product_id,
    COALESCE(sale_price, regular_price, 0) AS price
    FROM products;
 
 # NULLを含む計算
 SELECT
    order_id,
    price * COALESCE(discount_rate, 1.0) AS final_price
    FROM orders;



サンプルクエリ

SELECT句での使用

SELECT 句でCASE式を使用する時、取得する値を条件に応じて変換できる。

 # 価格帯でカテゴリ分け
 SELECT
    product_name,
    price,
    CASE
       WHEN price < 1000 THEN 'economy'
       WHEN price >= 1000 AND price < 5000 THEN 'standard'
       WHEN price >= 5000 THEN 'premium'
    END AS price_category
    FROM products;
 
 # 複数条件での値変換
 SELECT
    order_id,
    status,
    CASE
       WHEN status = 0 THEN '未処理'
       WHEN status = 1 AND processed_at IS NULL THEN '処理待ち'
       WHEN status = 1 AND processed_at IS NOT NULL THEN '処理中'
       WHEN status = 2 THEN '完了'
       ELSE '不明'
    END AS status_name
    FROM orders;
 
 # 計算結果の条件分岐
 SELECT
    product_name,
    price,
    stock,
    CASE
       WHEN stock * price > 100000 THEN '高額在庫'
       WHEN stock * price > 10000 THEN '通常在庫'
       ELSE '低額在庫'
    END AS inventory_value
    FROM products;


WHERE句での使用 (非推奨)

WHERE句でのCASE式の使用は、インデックスを活用できないため推奨されない。

複数の条件がある場合は、UNION で分割することを推奨する。

 # UNIONで分割 (推奨)
 SELECT * FROM products WHERE category_id = 1 AND price > 1000
 UNION ALL
 SELECT * FROM products WHERE category_id = 2 AND price > 2000
 UNION ALL
 SELECT * FROM products WHERE category_id NOT IN (1, 2) AND price > 500;
 
 # WHERE句でCASE式を使用 (非推奨)
 SELECT * FROM products
    WHERE
       CASE
          WHEN category_id = 1 THEN price > 1000
          WHEN category_id = 2 THEN price > 2000
          ELSE price > 500
       END;


ORDER BY句での使用

ORDER BY 句でCASE式を使用すると、カスタムソート順を指定できる。

詳細は MySQL - ORDER BY のページを参照すること。

 # カスタムソート順を指定
 SELECT * FROM orders
    ORDER BY
       CASE region
          WHEN 'Tokyo' THEN 1
          WHEN 'Osaka' THEN 2
          WHEN 'Nagoya' THEN 3
          ELSE 4
       END,
       ordered_at DESC;
 
 # 優先度順にソート
 SELECT * FROM tasks
    ORDER BY
       CASE priority
          WHEN 'urgent' THEN 1
          WHEN 'high' THEN 2
          WHEN 'normal' THEN 3
          WHEN 'low' THEN 4
       END;
 
 # NULL値を最後にソート
 SELECT * FROM employees
    ORDER BY
       CASE WHEN manager_id IS NULL THEN 1 ELSE 0 END,
       manager_id;


UPDATE文での使用

UPDATE 文でCASE式を使用すると、条件に応じて異なる値で更新できる。

詳細は MySQL - UPDATE のページを参照すること。

 # 条件付き給与調整
 UPDATE employees
    SET salary = CASE
       WHEN performance_score >= 90 THEN salary * 1.1
       WHEN performance_score >= 70 THEN salary * 1.05
       ELSE salary
    END;
 
 # 複数カラムの条件付き更新
 UPDATE products
    SET
       status = CASE
          WHEN stock = 0 THEN 'out_of_stock'
          WHEN stock <= 10 THEN 'low_stock'
          ELSE 'in_stock'
       END,
       updated_at = NOW();
 
 # 特定条件での値変更
 UPDATE orders
    SET discount_rate = CASE
       WHEN total_amount >= 100000 THEN 0.15
       WHEN total_amount >= 50000 THEN 0.10
       WHEN total_amount >= 10000 THEN 0.05
       ELSE 0
    END;


GROUP BYとの組み合わせ

GROUP BY 句とCASE式を組み合わせると、縦横変換やカテゴリ別集計が可能である。

詳細は MySQL - GROUP BY のページを参照すること。

 # 縦横変換 (ピボット)
 SELECT
    product_id,
    SUM(CASE WHEN region = 'Tokyo' THEN sales ELSE 0 END) AS tokyo_sales,
    SUM(CASE WHEN region = 'Osaka' THEN sales ELSE 0 END) AS osaka_sales,
    SUM(CASE WHEN region = 'Nagoya' THEN sales ELSE 0 END) AS nagoya_sales
    FROM sales_data
    GROUP BY product_id;
 
 # カテゴリ別カウント
 SELECT
    department_id,
    COUNT(CASE WHEN salary >= 50000 THEN 1 END) AS high_salary_count,
    COUNT(CASE WHEN salary < 50000 THEN 1 END) AS low_salary_count
    FROM employees
    GROUP BY department_id;
 
 # 条件別集計
 SELECT
    category_id,
    SUM(CASE WHEN price < 1000 THEN 1 ELSE 0 END) AS economy_count,
    SUM(CASE WHEN price >= 1000 AND price < 5000 THEN 1 ELSE 0 END) AS standard_count,
    SUM(CASE WHEN price >= 5000 THEN 1 ELSE 0 END) AS premium_count
    FROM products
    GROUP BY category_id;


ネストしたCASE式

CASE式はネストできるが可読性が低下するため、3段階以上のネストは避けることを推奨する。

 # 2段階のネスト
 SELECT
    product_name,
    price,
    stock,
    CASE
       WHEN stock = 0 THEN '在庫なし'
       ELSE
          CASE
             WHEN price < 1000 THEN '低価格・在庫あり'
             WHEN price >= 5000 THEN '高価格・在庫あり'
             ELSE '通常価格・在庫あり'
          END
    END AS status
    FROM products;
 
 # 複雑な条件分岐 (3段以上は避ける)
 SELECT
    order_id,
    CASE
       WHEN status = 'completed' THEN
          CASE
             WHEN total_amount >= 10000 THEN 'VIP顧客'
             ELSE '通常顧客'
          END
       WHEN status = 'pending' THEN '処理待ち'
       ELSE '不明'
    END AS customer_type
    FROM orders;



パフォーマンスと推奨される事柄

WHERE句でのCASE式

WHERE 句でCASE式を使用すると、インデックスが活用されない。

複雑な条件がある場合は、UNION で分割するか、条件を直接記述することを推奨する。

 # UNIONで分割 (インデックス活用可能)
 SELECT * FROM products WHERE category_id = 1 AND price > 1000
 UNION ALL
 SELECT * FROM products WHERE category_id = 2 AND price > 2000;
 
 # インデックスが活用されない例
 SELECT * FROM products
    WHERE
       CASE category_id
          WHEN 1 THEN price > 1000
          WHEN 2 THEN price > 2000
       END;


SELECT句とUPDATE句での使用

SELECT 句 や UPDATE 句でのCASE式の使用は、効率的である。

 # SELECT句での効率的な使用
 SELECT
    product_name,
    CASE
       WHEN price < 1000 THEN 'economy'
       WHEN price < 5000 THEN 'standard'
       ELSE 'premium'
    END AS price_category
    FROM products
    WHERE category_id = 1;
 
 # UPDATE句での効率的な使用
 UPDATE employees
    SET bonus = CASE
       WHEN performance_score >= 90 THEN salary * 0.2
       WHEN performance_score >= 70 THEN salary * 0.1
       ELSE 0
    END
    WHERE department_id = 10;


WHEN句の順序最適化

WHEN 句は上から順に評価されるため、マッチ可能性の高い条件を先に配置することで、評価回数を削減できる。

 # マッチ可能性の高い条件を先に配置
 SELECT
    product_name,
    CASE
       WHEN price < 5000 THEN 'standard'     # 80%がマッチ
       WHEN price >= 5000 THEN 'premium'     # 15%がマッチ
       WHEN price < 1000 THEN 'economy'      # 5%がマッチ
    END AS price_category
    FROM products;


戻り値の型の統一

CASE式の各 THEN 句 と ELSE 句の戻り値の型は、統一することを推奨する。

型が異なる場合、MySQLは暗黙的型変換を行うが、パフォーマンスに影響する可能性がある。

 # 戻り値の型が統一されている (推奨)
 SELECT
    CASE
       WHEN status = 0 THEN '未処理'
       WHEN status = 1 THEN '処理中'
       ELSE '完了'
    END AS status_name
    FROM orders;
 
 # 戻り値の型が異なる (非推奨)
 SELECT
    CASE
       WHEN status = 0 THEN '未処理'  # 文字列型
       WHEN status = 1 THEN 1         # 数値型
       ELSE NULL
    END AS status_name
    FROM orders;
 # MySQLは文字列型に変換するが、パフォーマンスに影響する可能性がある



関連ページ



「https://mochiu.net/index.php?title=MySQL_-_CASE式&oldid=14279」から取得