00:00SQL-92で標準に取り入れられたCASE式を用いると、今までCASE式の簡略版であるDECODE(Oracle)、IF(MySQL)などの関数で記述していたSQLを、実装非依存のコードに記述し直すことができ、コードの汎用性を高めることができます。今回は、UPDATEや集約関数と組み合わせた、CASE式の高度な応用方法について学びます。分岐させたUPDATEテーブル同士のマッチングPage2CASE式の中で集約関数を使う陥ってはいけない間違いおわりにはじめにCASE式は、SQL-92で標準に取り入れられました。比較的新しい道具であるためか、便利なわりにその真価があまり知られておらず、利用されていなかったり、CASE式の簡略版であるDECODE(Oracle)、IF(MySQL)などの関数で代用されていたりします。CASE式を活用すると、SQLでできることの幅がぐっと広がり、書き方もスマートになります。実装非依存の技術なのでコードの汎用性も高まります。DECODE関数を使っているOracleユーザーには、ぜひCASE式への乗り換えをお薦めします。前回『CASE式のススメ(前編)』では、CASE式の文法とその応用方法について解説しました。今回は引き続き、CASE式のより高度な応用方法について解説します。Accessは対象外)。条件を分岐させたUPDATE数値型の列に対して、現在の値を判定対象として別の値へ変えたいというケースを考えます。問題は、そのときのUPDATEの条件が複数に分岐する場合です。現在の給料が30万以上の社員は、10%の減給とする現在の給料が25万以上28万未満の社員は、20%の昇給とする単純に考えると、次のようにUPDATE文を2回実行すればよいように思えますが、これは正しくありません。280000;というのも、例えば、現在の給料が30万円の社員の場合、当然、条件1のUPDATEによって給料は27万に減ります。それで終わりではなく、続いて実行される条件2のUPDATEによって32万4000円に増えてしまうからです。減給と見えた人事部の仕打ちは、実は2万4000円の昇給だった、ということになります。このような結果は人事部の意図したところではありません。この社員はきっちり27万円に減給せねばなりません。問題は、最初に実行されたUPDATEによって、「現在の給料」が変わってしまい、正しい条件判定ができないことにあります。言って、実行するSQLの順番を逆にしても、例えば、現在の給料が27万円の社員の場合に同じ問題が発生します。鬼の人事部長の意図を正確に反映するSQLは、次のようにCASE式を使って書く必要があります。END;このSQLは正しいうえに、一度の実行で済むので速度まで速くなります。これなら人事部長も納得でしょう。salaryは非常に重要なので、必ず書いてください。これがないと、条件1と条件2のどちらの条件にも該当しない社員の給料はNULLになってしまいます。CASE式の仕様によります。CASE式を使うときは、常に明示的にELSE句を書く(たとえNULLでかまわない場合でも!)癖をつけましょう。このテクニックは応用範囲が広く、これを使えば主キーの値を入れ替えるという荒業も簡単に実現できます。普通、aとbという主キーの値を入れ替えるためには、ワーク用の値へ一度どちらかを退避させるか、遅延制約を使う必要があります。UPDATEが必要になりますが、CASE式を使えば一つのSQLで実現できます。う例えば、上のようなテーブルについて、主キーaとbを入れ替えるには、次のように書きます。'b');一読して分かるように、「aならbへ、bならaへ」という条件分岐させたUPDATEを行なっています。主キーだけでなく、もちろんユニークキーの入れ替えも同様に可能です。ポイントは先ほどの昇給・減給の例題のときと同じです。CASE式の分岐による更新は「一気に」行なわれるので、主キーの重複によるエラーを回避できるのです。このような入れ替えをする必要が生じるということは、テーブル設計にどこか間違いがある可能性が高いので、まずは設計を見直して、必要がなければ制約を外してください。テーブル同士のマッチングCASE式はDECODE関数と違って式を評価できます。NULLといった便利な述語群を使用できるということです。中でもINとEXISTSはサブクエリを作れるため、非常に強力な表現力を持ちます。CASE式の中でこの2つを使うことで、サブクエリをSELECT句で書くことができます。テーブル「tbl_A」と「tbl_B」をkeyCol列でマッチングすることを考えます。すると、テーブル「tbl_B」とマッチするキーを持つテーブル「tbl_A」のレコードに「Matched」、マッチしないレコードに「Unmatched」というラベルを貼るSQLは次のように記述できます。tbl_A;INとEXISTSどちらを使っても、結果は同じになります。EXISTSを使って「マッチしない」という条件を書くこともできますが、その場合はサブクエリ内で参照されるテーブルの列にNULLが存在するか否かで両者の動作が異なることに注意が必要です。この問題については、筆者のWebサイトの『3値論理』を参照してください。MySQLユーザへの注意このSQLは、サブクエリのサポートされたバージョン4.1以降でのみ使用可能です。実は2万4000円の昇給だった著者プロフィールミック(ミック)日本では、主にBI/DWHの設計からチューニングまでを専門とするデータベースエンジニアとして活動。2018年より米国シリコンバレーに活動拠点を移し、技術調査とビジネス開発に従事している。圧縮・解凍UNIX基本講座@CodeZine『CodeZine(コードジン)』の公式アカウントです。【NEW】デブスト2019デブサミ2019福岡・関西デブサミ2019夏デブスト関西デブサミ2019×ログインEメールパスワード※旧SEメンバーシップ会員の方は、同じ登録情報(Eメール&パスワード)でログインいただけます。イラストの無断転載を禁じます。記載されているロゴ、システム名、製品名は各社及び商標権者の登録商標あるいは商標です。