オーロラさんの勉強帳

SESのロースキル客先常駐勤務。データベース、Excel、Excel VBA、ネットワーク、LinuxなどIT関連のことを主に書いていきます。

【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の結果
f:id:auroralights:20210619012006p:plain


検索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の結果
f:id:auroralights:20210619014427p:plain

【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の結果
f:id:auroralights:20210617220217p:plain


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

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


SQLの結果
f:id:auroralights:20210617220546p:plain

DECODE関数の使用例2

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

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

f:id:auroralights:20210617221836p:plain


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

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;

f:id:auroralights:20210609211906p:plain


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

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

f:id:auroralights:20210609212325p:plain


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

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

f:id:auroralights:20210609212559p:plain


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;

f:id:auroralights:20210609222902p:plain


次はエラーにならない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;

f:id:auroralights:20210609225328p:plain

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;

f:id:auroralights:20210609230352p:plain



テスト用データ

今回の記事で使用したテーブル「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);