OracleのSQL decodeについてまとめています。
SQL decodeの構文
decodeはOracleで利用できる関数です。
DECODE(検索地と比較される式, {検索値, 結果 [,...])} [,一つも一致しない場合のデフォルト値])
具体的には、以下のようになります。以下のDECODE関数では、カラムcountry_idの内容がITならItaly、JPならJapan、USならUnited States、それ以外ならOtherを返します。
SELECT location_id,
DECODE (country_id, 'IT', 'Italy',
'JP', 'Japan',
'US', 'United States',
'Other')
FROM locations WHERE location_id < 2000;
LOCATION_ID, EXP
1000, Italy
1100, Italy
1200, Japan
1300, Japan
1400, United States
1500, United States
1600, United States
1700, United States
1800, Other
1900, Other
10 rows found.
case式の簡易版のような機能と言えるでしょう。decodeのメリットは関数一つで条件分岐が記述できる点、デメリットはcase式に比べると大小比較やワイルドカード指定など複雑な条件の記述ができない点です。
また、decodeはselect文で使用可能ですが、PL/SQLなどで直接使用することはできません。
【関連記事】
▶SQLのCASE式サンプル集 order byやgroup byとの組み合わせもバッチリ
なお、decode文はcase式が存在しなかったOracle8までの時代によく使われていました。新しいバージョンのOracleでは、可読性の良さの点からもdecodeよりもcase式を使うようにしたほうがよいでしょう。
SQL decodeのサンプルコード
decodeをcase式に置き換える
decodeは、ほとんどの場合、case式に置き換えが可能です。
SELECT product_id,
DECODE (warehouse_id, 1, 'Southlake',
2, 'San Francisco',
3, 'New Jersey',
4, 'Seattle',
'Non domestic') "Location"
FROM inventories
WHERE product_id < 1775
ORDER BY product_id, "Location";
上記のSQLをcase式で置き換えると以下のようになります。
SELECT product_id,
CASE warehouse_id
WHEN 1 THEN 'Southlake'
WHEN 2 THEN 'San Francisco'
WHEN 3 THEN 'New Jersey'
WHEN 4 THEN 'Southlake'
ELSE 'Non domestic'
END "Location"
FROM inventories
WHERE product_id < 1775
ORDER BY product_id, "Location";
decodeとcase式はNULLの扱いに差異あり decodeはNULL=NULL判定が可能
case式でほとんどの場合代用できるdecodeが重宝されている理由はNULLの扱いにあります。
case式では対象カラムがNULLかどうかの判定に検索case式を使ってIS NULLを使う必要がありますが、decodeでは一般の数値などと同様にNULLを扱えるんですね。
【関連記事】
▶SQLのwhenはcase式で条件分岐を記述 複数指定やNULL判定も可能
例えば、NULL時にEmptyを返すようにするには、以下のように指定します。
SELECT product_id,
DECODE (warehouse_id, 1, 'Southlake',
2, 'San Francisco',
3, 'New Jersey',
4, 'Seattle',
NULL, 'Emply',
'Non domestic') "Location"
FROM inventories
WHERE product_id < 1775
ORDER BY product_id, "Location";
とは言え、case式で記述したとしても、以下のように書くだけで済むためそれほど違いはないと言えるでしょう。
SELECT product_id,
case when warehouse_id = 1 then 'Southlake'
when warehouse_id = 2 then 'San Francisco'
when warehouse_id = 3 then 'New Jersey'
when warehouse_id = 4 then 'Seattle'
when warehouse_id IS NULL then 'Emply'
else 'Non domestic' END "Location"
FROM inventories
WHERE product_id < 1775
ORDER BY product_id, "Location";
decodeによるクロス集計 異なる項目を1つのselectで出力する
decodeとsumを組み合わせて、データ種別ごとの集計値を１つのselect文で取得できます。以下はTABLE1から、gender（性別）がM（男性）のデータとF（女性）のデータを集計するサンプルです。
SELECT
SUM(DECODE( gender, 'M', 1, 0, 0 ) AS "Male"
,SUM(DECODE( gender, 'F', 1, 0, 0 ) AS "Female"
FROM
TABLE1;
参考）SELECT 文の活用(実践) – オラクル・Oracle SQL 入門
decodeの引数は、最大256個まで。つまり、126組の検索値と結果を記述することが可能です。
まとめ
- decode関数は簡易的な条件分岐が可能
- decode関数はほとんどの場合case式で置き換えができる
- decode関数とcase式の違いはNULLの扱い
