SQL decodeは、Oracleの条件分岐でデータ変換が可能 case式で代用できる

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.

参考）Oracle公式ドキュメント：DECODE

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の扱い