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