Oracle Databaseにおいて、SQLでの集計や計算結果が合わない最大の原因は「NULL」の扱いにあります。OracleではNULLを含む算術演算は結果がすべてNULLになってしまうため、NVLやNVL2関数を用いた適切なハンドリングが必須です。
本記事では、実機で動くサンプルデータを使い、NULL置換の基本から計算式での活用法、よくあるエラーの回避策までを解説します。
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
結論・速習：NVLとNVL2の使い分け
NULL対策は、状況に応じて以下の関数を使い分けます。
- NVL関数：単純な置換を行う（基本）
- 構文：
NVL( 対象列, NULL時の置換値 ) - 用途：NULLなら「0」や「該当なし」に変換する。
- 構文：
- NVL2関数：NULLか否かで処理を分岐する（応用）
- 構文：
NVL2( 対象列, 非NULL時の戻り値, NULL時の戻り値 ) - 用途：値がある時は「あり」、ない時は「なし」と表示するなど。
- 構文：
- 注意点：置換する値のデータ型は、対象列と一致（または暗黙変換可能）させる必要があります。
1. NULL値と算術式の基礎知識
NULLの伝播（Propagate）とは
Oracle DatabaseにおけるNULLは「値が存在しない状態（未知）」を指します。そのため、100 + NULL のような計算を行うと、結果は 100 ではなく NULL になります。これを「NULLの伝播」と呼びます。
SQL> set null '(null)'
SQL> select 100+NULL from dual;
100+NULL
----------
(null)
この特性により、売上計算や給与計算で一部のカラムがNULLの場合、合計値が消えてしまう事故が発生します。これを防ぐのが NVL 関数です。
2. 実践：サンプルデータの作成
解説用のテーブル EMPLOYEE_DATA を作成します。
※お手元の検証環境（Oracle 19c等）でそのまま実行可能です。
前提条件
- 接続ユーザーに対象スキーマへの
CREATE TABLE,UNLIMITED TABLESPACE権限があること。
-- 既存テーブルがある場合は削除（実環境では注意）
-- DROP TABLE EMPLOYEE_DATA PURGE;
-- 1. テーブル作成
CREATE TABLE EMPLOYEE_DATA (
EMP_ID NUMBER PRIMARY KEY, -- 社員ID
EMP_NAME VARCHAR2(50), -- 社員名
SALARY NUMBER, -- 給与
BONUS NUMBER -- ボーナス（NULL許容）
);
-- 2. データ挿入（NULLを含むパターン）
INSERT INTO EMPLOYEE_DATA VALUES (1, 'Alice', 5000, 300); -- 両方あり
INSERT INTO EMPLOYEE_DATA VALUES (2, 'Bob', NULL, 200); -- 給与がNULL
INSERT INTO EMPLOYEE_DATA VALUES (3, 'Charlie', 4000, NULL); -- ボーナスがNULL
INSERT INTO EMPLOYEE_DATA VALUES (4, 'Diana', NULL, NULL); -- 両方NULL
INSERT INTO EMPLOYEE_DATA VALUES (5, 'Eve', 6000, 400);
INSERT INTO EMPLOYEE_DATA VALUES (6, 'Frank', NULL, 100);
-- 3. コミット
COMMIT;
-- 4. 現状の確認
SELECT * FROM EMPLOYEE_DATA ORDER BY EMP_ID;
SQL> set null '(null)'
SQL> SELECT * FROM EMPLOYEE_DATA ORDER BY EMP_ID;
EMP_ID EMP_NAME SALARY BONUS
---------- -------------------- ---------- ----------
1 Alice 5000 300
2 Bob (null) 200
3 Charlie 4000 (null)
4 Diana (null) (null)
5 Eve 6000 400
6 Frank (null) 100
6 rows selected.
3. NVL関数による計算誤差の回避
まずは、NULLをケアせずに計算した場合と、NVLを使用した場合を比較します。
実行例：給与＋ボーナスの合計算出
SELECT
EMP_ID,
EMP_NAME,
-- [NG例] そのまま足すと、どちらかがNULLの場合に結果が消える
SALARY + BONUS AS TOTAL_NG,
-- [OK例] NVLでNULLを0に変換してから足す
NVL(SALARY, 0) + NVL(BONUS, 0) AS TOTAL_OK
FROM EMPLOYEE_DATA
ORDER BY EMP_ID;
SQL> set null '(null)'
SQL> SELECT
2 EMP_ID,
3 EMP_NAME,
4 SALARY + BONUS AS TOTAL_NG,
5 NVL(SALARY, 0) + NVL(BONUS, 0) AS TOTAL_OK
6 FROM EMPLOYEE_DATA
7 ORDER BY EMP_ID;
EMP_ID EMP_NAME TOTAL_NG TOTAL_OK
---------- -------------------- ---------- ----------
1 Alice 5300 5300
2 Bob (null) 200
3 Charlie (null) 4000
4 Diana (null) 0
5 Eve 6400 6400
6 Frank (null) 100
6 rows selected.
実行結果と解説
EMP_ID EMP_NAME TOTAL_NG TOTAL_OK
------ ---------- ---------- ----------
1 Alice 5300 5300 <- 両方値があるので一致
2 Bob (null) 200 <- NULL+200=NULL(NG) / 0+200=200(OK)
3 Charlie (null) 4000 <- 4000+NULL=NULL(NG) / 4000+0=4000(OK)
4 Diana (null) 0 <- 両方NULLでも0になる
...
- 解説:
NVL(BONUS, 0)は、「BONUSがNULLなら0を、そうでなければBONUSの値を返す」という処理を行います。これにより算術演算が成立します。
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
4. NVL2関数による条件分岐
NVL2は「値がある場合」と「NULLの場合」で全く異なる値を返したい時に便利です。
実行例：ボーナス有無に応じた手当の計算
- ボーナスがある人：ボーナス額 + 500（特別加算）
- ボーナスがない人：一律 0
SELECT
EMP_ID,
EMP_NAME,
BONUS,
-- 第1引数(BONUS)がNULLでなければ第2引数、NULLなら第3引数を返す
NVL2(BONUS, BONUS + 500, 0) AS ADJUSTED_BONUS
FROM EMPLOYEE_DATA
ORDER BY EMP_ID;
SQL> set null '(null)'
SQL> SELECT
2 EMP_ID,
3 EMP_NAME,
4 BONUS,
5 NVL2(BONUS, BONUS + 500, 0) AS ADJUSTED_BONUS
6 FROM EMPLOYEE_DATA
7 ORDER BY EMP_ID;
EMP_ID EMP_NAME BONUS ADJUSTED_BONUS
---------- -------------------- ---------- --------------
1 Alice 300 800
2 Bob 200 700
3 Charlie (null) 0
4 Diana (null) 0
5 Eve 400 900
6 Frank 100 600
6 rows selected.
実行結果
EMP_ID EMP_NAME BONUS ADJUSTED_BONUS
------ ---------- ------ --------------
1 Alice 300 800 <- 300 + 500
2 Bob 200 700 <- 200 + 500
3 Charlie (null) 0 <- NULLなので 0
...
- ポイント:
DECODEやCASE文でも記述可能ですが、NULL判定に特化しているためNVL2の方がシンプルに記述できます。
5. トラブルシューティング（よくあるエラー）
NVL/NVL2使用時によく遭遇するエラーと対処法です。
| エラーコード | 原因 | 対処法 |
| ORA-01722: 数値が無効です | 数値型の列の置換値に、変換できない文字列（’なし’等）を指定した。 | 置換値を数値にするか、対象列を TO_CHAR で文字列化する。 |
| データ型の不一致 | 第1引数と第2引数の型が異なる（例: 日付型列の置換に数値0を指定）。 | 型を合わせる。日付型なら SYSDATE や日付リテラルを使う。 |
エラーになる例（型不一致）
-- SALARYはNUMBER型なのに、置換値に文字列を指定している
SELECT NVL(SALARY, '未払い') FROM EMPLOYEE_DATA;
-- 結果: ORA-01722: 数値が無効です
修正案: 表示目的であれば、左側を文字列に変換します。
SELECT NVL(TO_CHAR(SALARY), '未払い') FROM EMPLOYEE_DATA;
6. 運用・実装上の注意点とベストプラクティス
メリットとデメリット
- メリット: SQLが簡潔になり、アプリケーション側でのNULLチェック処理を減らせる。
- デメリット: 独自関数であるため、将来的に他のDBMS（PostgreSQLやMySQLなど）へ移行する際に修正が必要になる場合があります。
COALESCE関数との違い（重要）
標準SQL（ANSI）準拠の COALESCE 関数も似た動きをしますが、以下の違いがあります。
- 引数の数:
NVLは2つまで。COALESCEは複数指定可能（左から順に最初の非NULLを返す）。 - 評価プロセス（パフォーマンス）:
NVL: 第1引数がNULLでなくても、第2引数を評価（計算）します。COALESCE: 第1引数が非NULLなら、第2引数は評価しません（ショートサーキット）。- ※第2引数に重い関数処理を入れる場合は
COALESCEの方が高速な場合があります。
テーブル設計での回避（推奨）
SQLで都度 NVL するよりも、テーブル設計時点で DEFAULT 0 制約を付け、NOT NULL 制約を課す方が、データの整合性とパフォーマンスの観点で望ましいです。
-- 推奨されるテーブル定義例
CREATE TABLE NEW_EMP (
SALARY NUMBER DEFAULT 0 NOT NULL,
BONUS NUMBER DEFAULT 0 NOT NULL
);
7. FAQ：よくある質問
Q1. NVLの中でNVLを使うことはできますか？（ネスト）
A. 可能です。しかし、複数の候補値を順にチェックしたい場合は COALESCE(列A, 列B, 列C, 0) を使う方が可読性が高く、標準的です。
Q2. 空文字（”）はNVLで置換できますか？
A. はい。Oracle Databaseでは「空文字（長さ0の文字列）」は NULL と同等に扱われる仕様のため、NVL('', '置換') は「置換」を返します（他DBでは挙動が異なるので注意）。
Q3. WHERE句でNVLを使うと遅くなりますか？
A. インデックスが効かなくなる可能性があります。WHERE NVL(COL, 0) = 0 とすると、COL列のインデックスが使用されません。これを避けるには、ファンクション索引を作成するか、条件式を WHERE COL IS NULL OR COL = 0 のように工夫する必要があります。
8. まとめ
- NVL はNULLを特定の値（0や文字列）に変換し、計算誤差や表示欠けを防ぐ基本関数。
- NVL2 は「ある時」「ない時」の分岐ロジックをシンプルに書ける。
- 型合わせ は必須。数値項目に文字列を置換しようとするとエラーになる。
- 標準SQL を意識するなら
COALESCEの利用も検討する。
NULLハンドリングは正確なデータ集計の第一歩です。まずは NVL(列, 0) のパターンからマスターしましょう。
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
コメント