オーロラさんの勉強帳

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

【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);