【SQL】単純CASE式・検索CASE式の使い方 (ORACLE DATABASE 12C SQL基礎)

ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
単純CASE式・検索CASE式の使用方法を紹介します。

目次

単純CASE式・検索CASE式の使用例は以下のようなテーブルを使っています。

EMPLOYEE_ID	DEPARTMENT_ID	NAME	PHONE	SALARY	MANAGER_ID
1	10	山田	00000000000	500000	« NULL »
2	10	佐藤	00000000001	450000	1
3	10	鈴木	00000000002	450000	1
4	10	田中	00000000003	300000	1
5	10	山本	00000000004	220000	1
6	20	森	00000000005	470000	« NULL »
7	20	中村	00000000006	250000	6
8	20	松本	00000000007	220000	6
9	30	井上	00000000008	210000	10
10	30	木村	00000000009	300000	« NULL »

単純CASE式について

単純CASE式は条件分岐をするときに使います。
順番に列と条件値と比較して、列が条件値に一致する場合に対象の値を返します。
条件に一致するものが見つかると後続の条件との比較はしません。
いずれの条件値にも合致しない場合はデフォルト値を返します。(デフォルト値を指定しない場合はNULLを返します)

＜単純CASE式の書式＞

CASE 列 WHEN 条件値1 THEN 値1
        [WHEN 条件値2 THEN 値2
        ・・・・・
        WHEN 条件値n THEN 値
        ELSE デフォルト値]
END

単純CASE式のメモ
列に対してWHEN句で指定した条件に合致する場合THEN句で指定した値を返す。
WHEN句の条件に合致するものがなければELSE句のデフォルト値を返す。デフォルト値がない場合はNULLを返す。

単純CASE式の使用例

単純CASE式を使ってDEPARTMENT_IDが10の場合は経営管理部、20の場合は営業部、それ以外はその他と返すSQLを考えます。

select
  employee_id AS 従業員番号,
  case department_id when 10 then '経営管理部'
                     when 20 then '営業部'
                     else 'その他'
  end AS 部署,
  NAME AS 名前
from test5;

SQLの結果

検索CASE式について

検索CASE式も条件分岐をするときに使います。
単純CASE式とは違い条件の対象列を指定しません。
検索CASE式では条件式に「列名 演算子 値」の形式で指定ができます。
単純CASE式では対象の列に対して条件と＝(等価)のものに対して値を返していましたが、検索CASE式では等価条件以外を指定できます。

CASE WHEN 条件式1 THEN 戻り式1
    [WHEN 条件式2 THEN 戻り式2
     WHEN 条件式n THEN 戻り式n
     ELSE デフォルト値]
END

検索CASE式の使用例

salaryの値ごとにランクをつける場合を考えてみます。
salaryが250000未満はD、300000未満はC、350000未満はB、350000以上はAを返すSQLを試してみます。

select
  employee_id AS 従業員番号,
  case when salary < 250000 then 'D'
       when salary < 300000 then 'C'
	   when salary < 350000 then 'B'
	   else 'A'
  end AS 給与ランク,
  NAME AS 名前
from test5;

SQLの結果

【SQL】DECODE関数の使い方 (ORACLE DATABASE 12C SQL基礎)

ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
DECODE関数の使用方法を紹介します。

目次

DECODE関数について

DECODE関数は以下のような書式で使います。
列の値が条件値1に合致する場合は値1を返し、条件値nに合致する場合は値nを返します。いずれの条件値にも合致しない場合はデフォルト値を返します。(デフォルト値を指定しない場合はNULLを返します)

書式： DECODE(列, 条件値1 , 値1[, ・・・条件値n, 値n] [, デフォルト値])

以下のテーブルを使って、実際にDECODE関数の使い方を確認してみましょう。

EMPLOYEE_ID	DEPARTMENT_ID	NAME	PHONE	SALARY	MANAGER_ID
1	10	山田	00000000000	500000	« NULL »
2	10	佐藤	00000000001	450000	1
3	10	鈴木	00000000002	450000	1
4	10	田中	00000000003	300000	1
5	10	山本	00000000004	220000	1
6	20	森	00000000005	470000	« NULL »
7	20	中村	00000000006	250000	6
8	20	松本	00000000007	220000	6
9	30	井上	00000000008	210000	10
10	30	木村	00000000009	300000	« NULL »

DECODE関数の使用例

DEPARTMENT_IDが10の場合は経営管理部、20の場合は営業部、それ以外はその他と返すSQLを考えます。

SELECT EMPLOYEE_ID,NAME,DECODE(DEPARTMENT_ID, 10, '経営管理部', 20, '営業部','その他') FROM TEST5;

SQLの結果

列名が「DECODE(DEPARTMENT_ID, 10, '経営管理部', 20, '営業部','その他')」と非常に長いので、以下のように列別名をつけてみましょう。
可読性を高めるために、インデントもつけてみます。

SELECT 
  EMPLOYEE_ID,
  NAME,
  DECODE(DEPARTMENT_ID, 10, '経営管理部',
                          20, '営業部',
                          'その他') AS 部署
FROM TEST5;

SQLの結果

DECODE関数の使用例2

MANAGER_IDに値がある場合は〇〇さんの部下、MANAGER_IDに値がない場合は部署長と返すSQLを考えてみましょう。

SELECT 
  EMPLOYEE_ID,
  NAME,
  DECODE(MANAGER_ID, NULL, '部署長',
                                        1, '山田さんの部下',
					6, '森さんの部下',
                                        10, '木村さんの部下') 
FROM TEST5;

以下のように部署長をデフォルトにする書き方もできます。

SELECT 
  EMPLOYEE_ID,
  NAME,
  DECODE(MANAGER_ID, 1, '山田さんの部下',
					 6, '森さんの部下',
                                         10, '木村さんの部下',
                                          '部署長') 
FROM TEST5;

【SQL】NULLを扱う単一行関数 NVL、NVL2、NULLIF、COALESCEの使い方 (ORACLE DATABASE 12C SQL基礎)

ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
NVL関数、NVL2関数、NULLIF関数、COALESCE関数の使用方法を紹介します。

目次

NVL関数、NVL2関数、NULLIF関数、COALESCE関数の概要

NVL関数、NVL2関数、NULLIF関数、COALESCE関数の使い方の概要は以下の通りです。

関数	書式	説明
NVL	NVL(列, 値)	列に含まれるNULLだけを指定した値に変換して返す
NVL2	NVL2(列, 値1, 値2)	列値がNULLではない場合は値1、NULLの場合は値2を返す
NULLIF	NULLIF(列1, 列2)	2つの列値を比較し等価であればNULLを返す。等価でなければ列1を返す
COALESCE	COALESCE(列1,列2,・・・,列n)	指定した列を順番に評価して、最初に検出されたNULL以外の値を返す

※列には、列または式を指定します。
※値には、列または式、定数を指定します。

以下でNVL関数、NVL2関数、NULLIF関数、COALESCE関数の使い方を詳しく紹介します。
その際、以下のようなテスト用のデータを使います。テスト用のテーブルを作成するSQLは本記事の末尾に記載してますので、参考にしてください。

＜test4テーブル＞
ID、AGE列はNUMBER型、NAMEはCHAR型です。

ID	NAME	AGE
1	佐藤	25
2	鈴木	28
3	« NULL »	22
4	松本	« NULL »
5	« NULL »	« NULL »
6	山田	31
7	« NULL »	25

NVL関数の使い方

NVL関数は引数で指定した列のNULLを、引数で指定した値に変換して返す関数です。
注意点としては、引数で指定する列のデータ型と置換する値のデータ型が一致している必要があります。
※「NVL」は、NULL VALUE LOGICの略です。

NVL関数の書式

NVL(列, 値)

列値がNULLの場合は、値に変換します。

NVL関数の使用例

以下の例では、NAME列がNULLの場合にNULLを未登録という文字列に変換して返します。

select id,NVL(name,'未登録') from test4;

次はエラーの例になる例です。
以下のSQLはage列がNULLの場合に文字列の「未登録」を返そうとしてますが、age列はNUMBER型であり、文字列の「未登録」とデータ型が異なるため「ORA-01722：数値が無効です。」エラーとなります。

select id,NVL(age,'未登録') from test4;

次はNAME列がNULLの場合は文字列「未登録」、AGE列がNULLの場合は「0」を返す例です。

select id,NVL(name,'未登録'),NVL(age,0) from test4;

NVL2関数の使い方

NVL2関数は引数で指定した列値がNULLでない場合は値1を返し、NULLの場合は値2を返します。
値1、値2は同じデータ型である必要がありますが、列値と値1・値2は同じデータ型である必要はりません。

NVL2関数の書式

NVL2(列, 値1, 値2)

NVL2関数の使用例

以下SQLの例では、AGE列とAGE列がNULL以外の場合は文字列「登録あり」、NULLの場合は「登録なし」を返しています。
NVL関数と違い列と値1・値2が同じデータ型である必要はありません。(値1・値2は同じデータ型である必要があります)

select  age,NVL2(age,'登録あり','登録なし') from test4;

次はエラーにならないSQLとエラーになるSQL例です。

①のSQLでは、AGE列がNULL以外の場合はAGE列をそのまま返し、NULLの場合は数値「0」を返します。NUMBER型のAGE列と数値の0ですのでデータ型が一致するためエラーにはなりません。
②のSLQでは、AGE列がNULL以外の場合はAGE列、NULLの場合は文字列「未登録」を返すように書いてますが、NUMBER型のAGE列と文字型の「未登録」でデータが違うので「ORA-01722：数値が無効です。」エラーとなります。

--①エラーにならない例
select  age,NVL2(age,age,0) from test4;
--②エラーになる例
select  age,NVL2(age,age,'登録なし') from test4;

NULLIF関数の使い方

NULLIF関数は引数で指定した2つの列値を比較して、2つの列値が等しい場合にはNULLを返し、等しくない場合には列1を返します。

NULLIF関数の書式

NULLIF(列1, 列2)

NULLIF関数の使用例

以下SQLの例では、ID列、AGE列、AGE列の1文字目とID列を比較して等しい場合はNULLを返し、等しくない場合はAGE列の1文字目を返しています。

※ID列の4、5のように比較する列にNULLがある場合もNULLが返るようです。NULLが比較できないような不確かなデータなので、NULLが返るみたいですね。

select id,age,NULLIF(to_number(substr(age,1,1),'9'),id) from test4;

COALESCE関数の使い方

COALESCE関数は引数で指定した複数の列の中で最初にNULL出ない値を返します。
指定する列はすべて同じデータ型である必要があります。
引数は最低でも2つ指定する必要があります。
引数で指定した列がすべてNULLの場合はNULLを返します。

COALESCE関数の書式

COALESCE(列1, 列2, ・・・列n)

COALESCE関数の使用例

以下のSQL例は、AGE列、ID列、COALESCE関数でAGE列、ID列を順番に評価して最初に検出されたNULL以外の値を返します。

select age,id,COALESCE(age,id) from test4;

テスト用データ

今回の記事で使用したテーブル「test4」を作成して、データを挿入するSQLは以下の通りです。

テーブル作成
create table test4 (
id number(3) constraint test4_id_pk primary key,
name char(20),
age number(3)
);

データ挿入
insert into test4 values(1,'佐藤',25);
insert into test4 values(2,'鈴木',28);
insert into test4 values(3,NULL,22);
insert into test4 values(4,'松本',NULL);
insert into test4 values(5,NULL,NULL);
insert into test4 values(6,'山田',31);
insert into test4 values(7,NULL,25);