nullifとcoalesce関数の徹底解説と実践活用法

Oracle Master Silver
2025.01.13

1. はじめに

Oracleデータベースを扱う上で、NULL値の扱いは避けて通れません。本記事では、NULL値を操作する上で便利な2つの関数、nullifとcoalesceについて、その基本から業務での応用までを丁寧に解説します。

OracleデータベースのNULLについて
Oracleデータベースでは、NULLは「値が不明」や「無の状態」を意味します。これは「ゼロ」とは異なり、データが入れられていない状態を示します。この記事では、NULLの特徴や使用方法、さらにはNULLを正しく扱うための具体的なSQLの例と...
oracle-master00000.com
2024.12.23

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？

2. nullif関数

nullifとは？

nullifは、2つの値が等しい場合にNULLを返し、異なる場合にはそのまま最初の値を返すシンプルな関数です。

図解

値A: 3000   値B: 3000
↓ 等しい場合
結果: NULL

値A: 3000   値B: 2000
↓ 異なる場合
結果: 3000

構文

NULLIF(expr1, expr2)

用途例

  • 重複データを特定し無効化する。
  • デフォルト値を排除する。

実践例: 重複値を排除する

例として、給与(salary)と歩合(commission)が同じ場合に歩合をNULLとして扱います。

SELECT 
    emp_id,
    emp_name,
    salary,
    commission,
    NULLIF(salary, commission) AS adjusted_commission
FROM 
    employees;

3. coalesce関数

coalesceとは？

coalesceは、複数の値を順に評価し、最初にNULLでない値を返します。

図解

値A: NULL   値B: 2000   値C: 3000
↓ 最初にNULLでない値を選ぶ
結果: 2000

構文

COALESCE(expr1, expr2, ..., exprn)

用途例

  • デフォルト値の設定。
  • 動的な値の優先順位付け。

実践例: 歩合がない場合の代替値

歩合が設定されていない場合に給与を代わりに使用し、それもNULLなら0を返す例。

SELECT 
    emp_id,
    emp_name,
    salary,
    commission,
    COALESCE(commission, salary, 0) AS final_value
FROM 
    employees;

4. サンプル表とデータの準備

以下のSQLを使用して、employees表を作成し、データを挿入してください。

表の作成

CREATE TABLE employees (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    salary NUMBER,
    commission NUMBER
);

データの挿入

INSERT INTO employees (emp_id, emp_name, salary, commission) VALUES (1, 'John', 3000, NULL);
INSERT INTO employees (emp_id, emp_name, salary, commission) VALUES (2, 'Jane', 4000, 500);
INSERT INTO employees (emp_id, emp_name, salary, commission) VALUES (3, 'Alice', NULL, NULL);
INSERT INTO employees (emp_id, emp_name, salary, commission) VALUES (4, 'Bob', 2000, 300);
COMMIT;

5. 実行結果

以下のSQLを実行し、結果を確認してください。

nullifの結果

SELECT 
    emp_id,
    emp_name,
    salary,
    commission,
    NULLIF(salary, commission) AS adjusted_commission
FROM 
    employees;
SQL> SELECT
  2      emp_id,
  3      emp_name,
  4      salary,
  5      commission,
  6      NULLIF(salary, commission) AS adjusted_commission
  7  FROM
  8      employees;

    EMP_ID EMP_NAME                 SALARY COMMISSION ADJUSTED_COMMISSION
---------- -------------------- ---------- ---------- -------------------
         1 John                       3000                           3000
         2 Jane                       4000        500                4000
         3 Alice
         4 Bob                        2000        300                2000
実行結果の解説：NULLIF

ここでのポイントは、「2つの値が等しいかどうか」という判定基準です。

NULLIF(salary, commission) という式は、以下のロジックで動いています。

  1. 値が異なる場合：第1引数（salary）の値をそのまま返します。
  2. 値が等しい場合：NULL を返します。

今回のデータでは、給与（salary）と歩合（commission）が同額の社員がいなかったため、結果には基本的に給与が表示されています。

  • John, Jane, Bobの場合 給与と歩合の値が異なる（または片方がNULL）ため、第1引数である salary の値（3000, 4000, 2000）がそのまま ADJUSTED_COMMISSION 列に表示されています。
  • Aliceの場合 第1引数の salary 自体が NULL です。NULLIF は第1引数が NULL の場合、比較を行わずに結果として NULL を返します。

「特定の値を無効化（NULL化）したい」というケースで、この関数が非常に役立ちます。

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？

coalesceの結果

SELECT 
    emp_id,
    emp_name,
    salary,
    commission,
    COALESCE(commission, salary, 0) AS final_value
FROM 
    employees;
SQL> SELECT
  2      emp_id,
  3      emp_name,
  4      salary,
  5      commission,
  6      COALESCE(commission, salary, 0) AS final_value
  7  FROM
  8      employees;

    EMP_ID EMP_NAME                 SALARY COMMISSION FINAL_VALUE
---------- -------------------- ---------- ---------- -----------
         1 John                       3000                   3000
         2 Jane                       4000        500         500
         3 Alice                                                0
         4 Bob                        2000        300         300
実行結果の解説：COALESCE

こちらは「NULLじゃない値を探して採用する」という強力な関数です。

COALESCE(salary, commission, 0) という式は、左から順番に値をチェックし、最初に見つかった「非NULL」の値を返します。まるで「第1希望、第2希望、ダメなら第3希望…」と候補を探していくようなイメージです。

このSQLを実行すると、以下のような結果が得られます。

Jane, Bobの場合（歩合がある人） 最初に commission をチェックします。ここに値が入っている（500, 300）ため、即座にその値が採用されます。後ろにある salary は無視されます。

Johnの場合（歩合がない人） commission は NULL です。そこで次の候補 salary を見に行きます。3000という値があるため、これが採用されます。

Aliceの場合（給与も歩合もない人） これが COALESCE の真骨頂です。

  1. commission は NULL → 次へ
  2. salary も NULL → 次へ
  3. 最後の砦である 0 を採用！

結果として 0 が返ります。NVL 関数を入れ子にするよりもスッキリ書けるのが、COALESCE の大きなメリットですね。

6. 業務での応用例

ケース1: ボーナス計算

ボーナスを給与の10%とするが、歩合がある場合はそれを優先。

SELECT 
    emp_id,
    emp_name,
    COALESCE(commission, salary * 0.1, 0) AS bonus
FROM 
    employees;
SQL> SELECT
  2      emp_id,
  3      emp_name,
  4      COALESCE(commission, salary * 0.1, 0) AS bonus
  5  FROM
  6      employees;

    EMP_ID EMP_NAME                  BONUS
---------- -------------------- ----------
         1 John                        300
         2 Jane                        500
         3 Alice                         0
         4 Bob                         300

ケース2: 顧客データの整備

連絡先がない場合に「情報なし」と表示。

SELECT 
    customer_id,
    COALESCE(email, phone, '情報なし') AS contact_info
FROM 
    customers;

7. 注意点とベストプラクティス

  • nullifの注意点
    NULLが返されると後続の計算でエラーが発生する場合があるため、注意が必要です。
  • coalesceのパフォーマンス
    引数が多い場合、最初にNULLでない値が見つかるまで全て評価されるため、パフォーマンスを考慮する必要があります。

8. まとめ

nullifとcoalesceは、データベースにおけるNULL値処理の強力なツールです。これらを適切に活用することで、データの品質向上や業務効率化が可能です。ぜひ、この記事を参考に実践に取り入れてみてください！

[参考]
Oracle Database SQL言語リファレンス 19c

【Oracle SQL】NVL/NVL2関数でNULLを置換！計算誤差を防ぐ
Oracle Databaseにおいて、SQLでの集計や計算結果が合わない最大の原因は「NULL」の扱いにあります。OracleではNULLを含む算術演算は結果がすべてNULLになってしまうため、NVLやNVL2関数を用いた適切なハンドリン...
oracle-master00000.com
2025.01.12
数値データと算術演算子ガイド
Oracleデータベースで数値データを操作する際、算術演算子を正しく活用することは非常に重要です。本記事では、基本的な概念から実用例、さらに初心者がつまずきやすいポイントやトラブルシューティングまで、詳しく解説します。なお、本記事ではSCO...
oracle-master00000.com
2024.12.19

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？

コメント

タイトルとURLをコピーしました