SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

達人に学ぶSQL

CASE式のススメ(後編)

UPDATEや集計関数との連携によるCASE式の高度な応用


  • X ポスト
  • このエントリーをはてなブックマークに追加

SQL-92で標準に取り入れられたCASE式を用いると、今までCASE式の簡略版であるDECODE(Oracle)、IF(MySQL)などの関数で記述していたSQLを、実装非依存のコードに記述し直すことができ、コードの汎用性を高めることができます。今回は、UPDATEや集約関数と組み合わせた、CASE式の高度な応用方法について学びます。

  • X ポスト
  • このエントリーをはてなブックマークに追加

はじめに

 CASE式は、SQL-92で標準に取り入れられました。比較的新しい道具であるためか、便利なわりにその真価があまり知られておらず、利用されていなかったり、CASE式の簡略版であるDECODE(Oracle)、IF(MySQL)などの関数で代用されていたりします。

 CASE式を活用すると、SQLでできることの幅がぐっと広がり、書き方もスマートになります。しかも、実装非依存の技術なのでコードの汎用性も高まります。特にDECODE関数を使っているOracleユーザーには、ぜひCASE式への乗り換えをお薦めします。

 前回『CASE式のススメ(前編)』では、CASE式の文法とその応用方法について解説しました。今回は引き続き、CASE式のより高度な応用方法について解説します。

必要な環境

 次のいずれかのデータベース(MS Accessは対象外)。

  • Oracle(9i以降)
  • SQL Server
  • DB2
  • PostgreSQL
  • MySQL

対象読者

 入門者~中級者。

条件を分岐させたUPDATE

 数値型の列に対して、現在の値を判定対象として別の値へ変えたいというケースを考えます。問題は、そのときのUPDATEの条件が複数に分岐する場合です。例えば、次のような条件です。

  1. 現在の給料が30万以上の社員は、10%の減給とする
  2. 現在の給料が25万以上28万未満の社員は、20%の昇給とする

 単純に考えると、次のようにUPDATE文を2回実行すればよいように思えますが、これは正しくありません。

正しくない更新
--条件1
UPDATE Personnel
SET salary = salary * 0.9
WHERE salary >= 300000;
--条件2
UPDATE Personnel
SET salary = salary * 1.2
WHERE salary >= 250000 AND salary < 280000;

 というのも、例えば、現在の給料が30万円の社員の場合、当然、条件1のUPDATEによって給料は27万に減ります。しかし、それで終わりではなく、続いて実行される条件2のUPDATEによって32万4000円に増えてしまうからです。減給と見えた人事部の仕打ちは、実は2万4000円の昇給だった、ということになります。

 もちろん、このような結果は人事部の意図したところではありません。この社員はきっちり27万円に減給せねばなりません。問題は、最初に実行されたUPDATEによって、「現在の給料」が変わってしまい、正しい条件判定ができないことにあります。だからと言って、実行するSQLの順番を逆にしても、例えば、現在の給料が27万円の社員の場合に同じ問題が発生します。鬼の人事部長の意図を正確に反映するSQLは、次のようにCASE式を使って書く必要があります。

サンプル1
UPDATE Personnel
SET salary = CASE WHEN salary >= 300000
                     THEN salary * 0.9
                  WHEN salary >= 250000 AND salary < 280000
                      THEN salary * 1.2
                  ELSE salary END;

 このSQLは正しいうえに、一度の実行で済むので速度まで速くなります。これなら人事部長も納得でしょう。

 なお、最後の行のELSE salaryは非常に重要なので、必ず書いてください。これがないと、条件1と条件2のどちらの条件にも該当しない社員の給料はNULLになってしまいます。これは、「CASE式に明示的なELSE句がない場合、デフォルトでNULLが挿入される」という CASE式の仕様によります。CASE式を使うときは、常に明示的にELSE句を書く(たとえNULLでかまわない場合でも!)癖をつけましょう。

 このテクニックは応用範囲が広く、これを使えば主キーの値を入れ替えるという荒業も簡単に実現できます。普通、abという主キーの値を入れ替えるためには、ワーク用の値へ一度どちらかを退避させるか、遅延制約を使う必要があります。前者の方法では3回の UPDATEが必要になりますが、CASE式を使えば一つのSQLで実現できます。

SomeTable
主キー(p_key) 列1(col_1) 列2(col_2)
a 1
b 2
c 3

 例えば、上のようなテーブルについて、主キーabを入れ替えるには、次のように書きます。

サンプル2
UPDATE SomeTable
SET p_key = CASE WHEN p_key = 'a'
                     THEN 'b'
                 WHEN p_key = 'b'
                     THEN 'a'
                 ELSE p_key END
WHERE p_key IN ('a', 'b');

 一読して分かるように、「aならbへ、bならaへ」という条件分岐させたUPDATEを行なっています。主キーだけでなく、もちろんユニークキーの入れ替えも同様に可能です。ポイントは先ほどの昇給・減給の例題のときと同じです。すなわち、CASE式の分岐による更新は「一気に」行なわれるので、主キーの重複によるエラーを回避できるのです。

 ただし、このような入れ替えをする必要が生じるということは、テーブル設計にどこか間違いがある可能性が高いので、まずは設計を見直して、必要がなければ制約を外してください。

テーブル同士のマッチング

 CASE式はDECODE関数と違って式を評価できます。それはつまり、CASE式の中でBETWEENLIKEIS NULLといった便利な述語群を使用できるということです。中でもINEXISTSはサブクエリを作れるため、非常に強力な表現力を持ちます。CASE式の中でこの2つを使うことで、サブクエリをSELECT句で書くことができます。

 さて、テーブル「tbl_A」と「tbl_B」をkeyCol列でマッチングすることを考えます。すると、テーブル「tbl_B」とマッチするキーを持つテーブル「tbl_A」のレコードに「Matched」、マッチしないレコードに「Unmatched」というラベルを貼るSQLは次のように記述できます。

サンプル3
--IN述語の場合
SELECT keyCol,
        CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B )
                 THEN 'Matched'
             ELSE 'Unmatched' END Label
FROM tbl_A;

--EXISTS述語の場合
SELECT keyCol,
        CASE WHEN EXISTS ( SELECT * FROM tbl_B 
                     WHERE tbl_A.keyCol = tbl_B.keyCol )
                 THEN 'Matched'
             ELSE 'Unmatched' END Label
FROM tbl_A;

 INEXISTSどちらを使っても、結果は同じになります。同様にNOT INNOT EXISTSを使って「マッチしない」という条件を書くこともできますが、その場合はサブクエリ内で参照されるテーブルの列にNULLが存在するか否かで両者の動作が異なることに注意が必要です。この問題については、筆者のWebサイトの『3値論理』を参照してください。

MySQLユーザへの注意
 このSQLは、サブクエリのサポートされたバージョン4.1以降でのみ使用可能です。
 

会員登録無料すると、続きをお読みいただけます

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

次のページ
CASE式の中で集約関数を使う

修正履歴

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
達人に学ぶSQL連載記事一覧

もっと読む

この記事の著者

ミック(ミック)

日本では、主にBI/DWHの設計からチューニングまでを専門とするデータベースエンジニアとして活動。2018年より米国シリコンバレーに活動拠点を移し、技術調査とビジネス開発に従事している。主な著書・訳書:『達人に学ぶSQL徹底指南書 第2版』(2018)『SQL実践入門』(2015)Joe Celko『プログラマのためのSQL 第4版』(2015)翔泳社 - 著者ページ:https://www.shoeisha.co.jp/book/author/3964著者個人ページ:http://mickindex.sakura.ne.jp/

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/405 2009/10/26 13:08

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング