ORACLE CASE式かDECODE関数か？SQLで条件分岐する方法

検証環境は ORACLE 11g です。インストールは下記を参考に。

今回使ったテーブルはこちらです。

CREATE TABLE EMPLOYEES
(
  ID NVARCHAR2(255) NOT NULL,
  NAME NVARCHAR2(255) NOT NULL,
  EMAIL NVARCHAR2(255),
  SEX NVARCHAR2(1),
  SALARY NUMBER,
  CREATED_AT DATE NOT NULL,
  UPDATED_AT DATE,
  CONSTRAINT PK_EMPLOYEES PRIMARY KEY (ID) USING INDEX
);

今回使ったサンプルデータはこちら。

INSERT INTO EMPLOYEES VALUES('001','hoge','hoge@example.com','1','30',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('002','piyo','piyo@example.com','2','50',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('003','fuga','fuga@example.com',null,null,sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('004','foo','foo@example.com','2','65',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('005','bar','bar@example.com','1','25',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('006','baz','baz@example.com','2','70',sysdate,sysdate);
INSERT INTO EMPLOYEES VALUES('007','saka','saka@example.com',null,'1000',sysdate,sysdate);

ORACLE CASE式とDECODE関数の違い

どちらも条件式といった意味では同じなので、条件に該当するものが見つかると後続の条件は評価しません。CASE 式と DECODE 関数の大きな違いは NULL の取り扱い方です。

まずは DECODE関数の使い方から見てみましょう。

DECODE は NULL を直接評価できるため NULL = NULL は True となります。

例えばこんな使い方ができます。

DECODE(sex, '1', '男', '2', '女', null, '不明')

次に CASE 式ですが、これには２種類あります。

単純 CASE 式 (simple case expression) の具体的な使い方はこうです。

CASE sex
  WHEN n'1' THEN '男'
  WHEN n'2' THEN '女'
  ELSE '不明'
END AS conv_sex

ここで「n'1'」って書いているのは、型が NVARCHAR2 だからです。条件判定の '1' は、暗黙的に CHAR となります。明示的 CAST しないと、「ORA-12704:キャラクタ・セットが一致しません。」のエラーが出ます。

DECODE だと出ないんですよねー。DECODE に慣れているとハマりますんでご注意を。

また、単純 CASE 式では、NULL との評価はすべて NULL になります。つまり WHEN NULL THEN '不明' とか書いても NULL になる、つまり評価されないということですね。

上記にあるように、DECODE であれば NULL の評価はできます。単純な条件分岐であれば、やはり DECODE が最強といったところでしょうか。

次に、検索 CASE 式 (searched case expression)の具体的な使い方です。

CASE
  WHEN salary <= 30 THEN '少額'
  WHEN salary <= 50 THEN '普通'
  WHEN salary <= 80 THEN '高額'
  WHEN salary IS NULL THEN '未定'
  ELSE '超額'
END AS conv_salary

DECODE 関数では使用できなかった比較条件や演算子が使えます。

これは便利ですねー。

一昔前は FUNCTION を作って SQL に組み込むなどのことをやってた気が・・・。懐かしい話です＾＾

せっかくなんで、上で書いた CASE 式を実行してみましょう。

SELECT
name,
sex,
CASE sex
  WHEN n'1' THEN '男'
  WHEN n'2' THEN '女'
  ELSE '不明'
END AS conv_sex,
salary,
CASE
 WHEN salary <= 30 THEN '少額'
 WHEN salary <= 50 THEN '普通'
 WHEN salary <= 80 THEN '高額'
 WHEN salary IS NULL THEN '未定'
 ELSE '超額'
END AS conv_salary
FROM EMPLOYEES
ORDER BY name

結果はこなりました。