Oracle Database SQL (1Z0-071-JPN)試験 NULLを扱うファンクションについて【第５章_後編】

NULL関連ファンクションの概要

Oracle には、NULL を別の値に置換したり、NULL かどうかに応じて返す値を変えたりするための専用ファンクションが用意されています。これらは SELECT 句のリストや WHERE 句など、単一行ファンクションが使える箇所であればどこでも使用できます。

ファンクション	構文	主な用途
NVL	NVL(in, replace)	NULL を別の値に置換する
NVL2	NVL2(in, out1, out2)	NULL か否かで異なる値を返す
NULLIF	NULLIF(in, check)	2つの値が等しい場合に NULL を返す
COALESCE	COALESCE(in1, in2, ..., inN)	複数の値から最初の非 NULL を返す

---

NVL — NULL を別の値に置換する

NVL(in, replace)

• in が NULL の場合、replace を返す
• in が NULL でない場合、in をそのまま返す
• 戻り値のデータ型は第1引数（in）のデータ型に合わせられる
• in と replace のデータ型が異なる場合、replace を in のデータ型に暗黙的に変換しようとする。変換に失敗した場合はエラーとなる

実行例：

-- manager_id が NULL の場合は 0 を返す
SELECT id, name, NVL(manager_id, 0) AS manager
FROM   null_func_test
ORDER  BY id;

-- NULL を文字列に置換
SELECT NVL(NULL, '未登録')  FROM DUAL; -- '未登録'
SELECT NVL('田中', '未登録') FROM DUAL; -- '田中'

動作確認

■テストデータ

CREATE TABLE null_func_test (
    id              NUMBER(3)      PRIMARY KEY,
    name            VARCHAR2(20),
    manager_id      NUMBER(6),
    age             NUMBER(3),
    mobile_phone    VARCHAR2(20),
    office_phone    VARCHAR2(20),
    home_phone      VARCHAR2(20),
    job_id          VARCHAR2(10),
    old_job_id      VARCHAR2(10)
);
-- id=1: manager_id NULL, age NULL, 連絡先ほぼなし、職種変更なし
INSERT INTO null_func_test VALUES (
    1,
    'ALICE',
    NULL,        -- manager_id
    NULL,        -- age
    NULL,        -- mobile_phone
    NULL,        -- office_phone
    NULL,        -- home_phone
    'DEV',       -- job_id
    'DEV'        -- old_job_id
);

-- id=2: manager_id 100, age 30, mobileのみあり、職種変更あり
INSERT INTO null_func_test VALUES (
    2,
    'BOB',
    100,
    30,
    '090-1111-2222',
    NULL,
    NULL,
    'DEV',
    'OP'         -- old_job_id
);

-- id=3: manager_id NULL, age 0, officeのみあり
INSERT INTO null_func_test VALUES (
    3,
    'CAROL',
    NULL,
    0,
    NULL,
    '03-1234-5678',
    NULL,
    'SALES',
    NULL
);

-- id=4: manager_id 200, age 25, homeのみあり
INSERT INTO null_func_test VALUES (
    4,
    'DAVE',
    200,
    25,
    NULL,
    NULL,
    '048-999-0000',
    'HR',
    'HR'
);

COMMIT;

0 を返す

NULL を文字列に置換

⚠️ データ型エラーに注意：

-- age 列が NUMBER 型のため、文字列 '未登録' との型不一致で ORA-01722 エラー
SELECT NVL(age, '未登録') FROM null_func_test;  -- ❌ エラー

-- 型を合わせる（正しい例）
SELECT NVL(age, 0)           FROM null_func_test;  -- ✅ NUMBER 同士
SELECT NVL(TO_CHAR(age), '未登録') FROM null_func_test;  -- ✅ 文字列同士

動作確認

age 列が NUMBER 型のため、文字列 '未登録' との型不一致で ORA-01722 エラー

-- 型を合わせる（正しい例）

🔑 NVL は算術演算の NULL 対策に多用されます。
Oracle では NULL を含む算術演算はすべて NULL になるため、SUM や掛け算の前に NVL(列, 0) でゼロ置換するのが定石です。

---

NVL2 — NULL か否かで分岐して異なる値を返す

NVL2(in, out1, out2)

• in が NULL でない 場合、out1 を返す
• in が NULL の場合、out2 を返す
• out1 と out2 のデータ型が異なる場合、out2 を out1 のデータ型に暗黙的に変換しようとする。変換に失敗した場合はエラーとなる
• in のデータ型と out1・out2 のデータ型は同じでなくてよい

⚠️ NVL2 は Oracle 独自の拡張関数です。他の RDBMS（MySQL など）では使用できません。移植性を考慮する場合は CASE 式で代替します。

実行例：

-- manager_id が NULL でなければ '管理下' を、NULL なら 'トップ' を返す
SELECT id,
       name,
       NVL2(manager_id, '管理下', 'トップ') AS position
FROM   null_func_test
ORDER  BY id;

SELECT NVL2(NULL, 'NOT NULL', 'NULL値') FROM DUAL;  -- 'NULL値'
SELECT NVL2('値あり', 'NOT NULL', 'NULL値') FROM DUAL;  -- 'NOT NULL'

動作確認

-- manager_id が NULL でなければ '管理下' を、NULL なら 'トップ' を返す

-- 'NULL値'

-- 'NOT NULL'

**CASE 式による等価表現：**

-- NVL2(manager_id, 'available', 'N/A') と等価
SELECT CASE WHEN id IS NOT NULL THEN 'available'
            ELSE 'N/A'
       END
FROM   null_func_test;

動作確認

-- NVL2(manager_id, 'available', 'N/A') と等価

***

NULLIF — 2 つの値が等しい場合に NULL を返す

NULLIF(in, check)

• in と check の値が等しい場合、NULL を返す
• in と check の値が異なる場合、in を返す
• in にリテラルの NULL は指定できない（コンパイルエラー）
• 2つの引数が数値型でない場合、データ型は同じでなければならない。データ型が異なる場合はエラーとなる
• 2つの引数が両方とも数値型の場合、優先順位の高いデータ型に暗黙的に変換される

実行例：

-- 等しい場合 → NULL
SELECT NULLIF('Oracle', 'Oracle') FROM DUAL;  -- NULL

-- 異なる場合 → 第1引数を返す
SELECT NULLIF('Oracle', 'SQL')    FROM DUAL;  -- 'Oracle'

-- 実用例：現在の job_id と過去の job_id が同じなら NULL（職種変更なし）
SELECT id,
       name,
       job_id,
       old_job_id,
       NULLIF(job_id, old_job_id) AS job_changed
FROM   null_func_test
ORDER  BY id;

動作確認

-- 等しい場合 → NULL

-- 異なる場合 → 第1引数を返す

-- 実用例：現在の job_id と過去の job_id が同じなら NULL（職種変更なし）

⚠️ in への NULL リテラル指定に注意：

SELECT NULLIF(NULL, 999) FROM DUAL;  -- ❌ エラー（リテラル NULL は不可）

ただし、変数や列の値が評価の結果として NULL になる場合は問題ありません。その場合、第2引数の値にかかわらず NULL が返されます。

動作確認

❌ エラー（リテラル NULL は不可）

---

COALESCE — 複数の値から左順に最初の非 NULL を返す

COALESCE(in1, in2, ..., inN)

• in1 から順に評価し、最初に見つかった NULL でない値を返す
• 引数はすべて NULL でなければ最初の値が返るため、2つ以上の引数が必要
• すべての引数が NULL の場合は NULL を返す
• 短絡評価（ショートサーキット） を採用しており、非 NULL の値が見つかった時点で後続の引数の評価を打ち切る
• ANSI SQL 標準の関数であり、移植性が高い

実行例：

-- 左から順に非 NULL を探す
SELECT COALESCE(NULL, NULL, 'first', 'second') FROM DUAL;  -- 'first'
SELECT COALESCE(NULL, NULL, NULL)              FROM DUAL;  -- NULL

-- 複数の連絡先列から最初の有効な値を取得する実用例
SELECT id,
       name,
       COALESCE(mobile_phone,
                office_phone,
                home_phone,
                '連絡先なし') AS contact
FROM   null_func_test
ORDER  BY id;

動作確認

-- 'first'

-- NULL

-- 複数の連絡先列から最初の有効な値を取得する実用例

CASE 式による等価表現：

-- COALESCE(expr1, expr2) は下記と等価
CASE WHEN expr1 IS NOT NULL THEN expr1
     ELSE expr2
END

動作確認

■テストデータ

CREATE TABLE coalesce_test (
    id    NUMBER(3)    PRIMARY KEY,
    col1  VARCHAR2(20),
    col2  VARCHAR2(20)
);

INSERT INTO coalesce_test VALUES (1, 'A',    'B');    -- col1 NOT NULL
INSERT INTO coalesce_test VALUES (2, NULL,  'B');    -- col1 NULL, col2 NOT NULL
INSERT INTO coalesce_test VALUES (3, NULL,  NULL);   -- 両方 NULL

COMMIT;

■確認用SQL

SELECT
    id,
    col1,
    col2,
    COALESCE(col1, col2) AS coalesce_val,
    CASE
        WHEN col1 IS NOT NULL THEN col1
        ELSE col2
    END                 AS case_val
FROM
    coalesce_test
ORDER BY id;

-- COALESCE(expr1, expr2) は下記と等価

***

NVL と COALESCE の使い分け

どちらも「NULL を別の値に置換する」用途で使えますが、いくつかの重要な違いがあります。

比較項目	NVL	COALESCE
引数の数	2つのみ	2つ以上（可変長）
SQL 標準	Oracle 独自	ANSI SQL 標準
評価方式	第1引数が非 NULL でも第2引数を常に評価	短絡評価（非 NULL が見つかれば後続を評価しない）
型変換	第1引数の型に合わせる	共通型へ変換
複数候補	ネストが必要	1つの式でまとめられる

🔑 試験ポイント：NVL の非短絡評価
NVL(1, 重い処理()) のように書くと、第1引数が NULL でなくても第2引数の 重い処理() が実行されます。一方、COALESCE(1, 重い処理()) は第1引数が非 NULL の時点で第2引数を評価しません。

---

ファンクション一覧まとめ

ファンクション	戻り値	引数の NULL 制約	データ型制約
NVL(in, replace)	in または replace	in・replace ともに NULL 可	replace → in の型に変換
NVL2(in, out1, out2)	out1 または out2	in は NULL 可	out2 → out1 の型に変換
NULLIF(in, check)	in または NULL	in にリテラル NULL 不可	非数値型は同一型であること
COALESCE(in1,...,inN)	最初の非 NULL	すべて NULL なら NULL を返す	共通型への変換

---

参考

• COALESCE — Oracle Database SQL言語リファレンス
• NULLIF — Oracle Help Center
• NULLIF — Oracle Database 11g SQL言語リファレンス
• NVL, NVL2 : NULLデータ置換え
• Oracle SQLのNULLに関する関数徹底解説！NVL／NVL2 — Qiita

---