OracleデータベースのNULLについて

Oracle Master Silver

Oracleデータベースでは、NULLは「値が不明」や「無の状態」を意味します。これは「ゼロ」とは異なり、データが入れられていない状態を示します。この記事では、NULLの特徴や使用方法、さらにはNULLを正しく扱うための具体的なSQLの例と業務シナリオを詳しく解説します。

NULLの特徴

  1. 計算から除外
    • NULLは計算の中で無視されますが、NULLを含む計算結果はNULLになります。たとえば、5 + NULLの結果はNULLです。
  2. 等しくない個体
    • NULLは他のNULLとも等しくないとみなされます。そのため、NULL = NULLという式の結果はFALSEではなくUNKNOWNになります。
  3. IS NULLやIS NOT NULLの利用が必要
    • NULLを評価する場合はIS NULLまたはIS NOT NULLを使用します。通常の比較演算子（=や!=）では正しく評価されません。
  4. 算術式での動作
    • NULLを含む算術式の結果はことごとくNULLになります。
  5. デフォルト値の設定
    • NULLを避けるために、NVLやCOALESCE関数を使用してデフォルト値を指定することが一般的です。
  6. 性能の観点
    • NULLが含まれるカラムを頻繁に評価するクエリは、インデックスが効かない場合があるため、パフォーマンスに注意が必要です。

NULLに関するSQLのサンプル

以下はSCOTTスキーマを使用した実際のサンプルです。

データにNULLが含まれているかを検索

SELECT empno, ename, comm
FROM emp
WHERE comm IS NULL;
  • このクエリは、COMMカラムがNULLの示す職員を検索します。
【実行結果】

SQL> SELECT empno, ename, comm
  2  FROM emp
  3  WHERE comm IS NULL;

     EMPNO ENAME                                COMM
---------- ------------------------------ ----------
      7369 SMITH
      7566 JONES
      7698 BLAKE
      7782 CLARK
      7839 KING
      7900 JAMES
      7902 FORD
      7934 MILLER

8行が選択されました。

データがNULLではない場合

SELECT empno, ename, comm
FROM emp
WHERE comm IS NOT NULL;
  • COMMカラムに値がある職員を検索します。
【実行結果】

SQL> SELECT empno, ename, comm
  2  FROM emp
  3  WHERE comm IS NOT NULL;

     EMPNO ENAME                                COMM
---------- ------------------------------ ----------
      7499 ALLEN                                 300
      7521 WARD                                  500
      7654 MARTIN                               1400
      7844 TURNER                                  0

NVL関数の使用

NVL関数を使用すると、NULLの場合に代替値を指定できます。

SELECT empno, ename, NVL(comm, 0) AS comm_with_default
FROM emp;
  • このクエリはCOMMカラムがNULLの場合に0を返します。
【実行結果】

SQL> SELECT empno, ename, NVL(comm, 0) AS comm_with_default
  2  FROM emp;

     EMPNO ENAME                          COMM_WITH_DEFAULT
---------- ------------------------------ -----------------
      7369 SMITH                                          0
      7499 ALLEN                                        300
      7521 WARD                                         500
      7566 JONES                                          0
      7654 MARTIN                                      1400
      7698 BLAKE                                          0
      7782 CLARK                                          0
      7839 KING                                           0
      7844 TURNER                                         0
      7900 JAMES                                          0
      7902 FORD                                           0
      7934 MILLER                                         0

12行が選択されました。

COALESCE関数の使用

COALESCE関数は、複数の値の中で最初にNULLでない値を返します。

SELECT empno, ename, COALESCE(comm, 0, sal) AS effective_comm
FROM emp;
  • COMMがNULLの場合、SALの値が返され、どちらもNULLなら0が返されます。
【実行結果】

SQL> SELECT empno, ename, COALESCE(comm, 0, sal) AS effective_comm
  2  FROM emp;

     EMPNO ENAME                          EFFECTIVE_COMM
---------- ------------------------------ --------------
      7369 SMITH                                       0
      7499 ALLEN                                     300
      7521 WARD                                      500
      7566 JONES                                       0
      7654 MARTIN                                   1400
      7698 BLAKE                                       0
      7782 CLARK                                       0
      7839 KING                                        0
      7844 TURNER                                      0
      7900 JAMES                                       0
      7902 FORD                                        0
      7934 MILLER                                      0

12行が選択されました。

NULLを含むデータの判断

NULLをデータの相互判断で使用する場合、DECODEやCASEを使用します。

DECODEの例

SELECT empno, ename, DECODE(comm, NULL, 'No Commission', 'Has Commission') AS comm_status
FROM emp;
  • このクエリはCOMMがNULLの場合「No Commission」を表示します。
【実行結果】

SQL> SELECT empno, ename, DECODE(comm, NULL, 'No Commission', 'Has Commission') AS comm_status
  2  FROM emp;

     EMPNO ENAME                          COMM_STATUS
---------- ------------------------------ ------------------------------------------
      7369 SMITH                          No Commission
      7499 ALLEN                          Has Commission
      7521 WARD                           Has Commission
      7566 JONES                          No Commission
      7654 MARTIN                         Has Commission
      7698 BLAKE                          No Commission
      7782 CLARK                          No Commission
      7839 KING                           No Commission
      7844 TURNER                         Has Commission
      7900 JAMES                          No Commission
      7902 FORD                           No Commission
      7934 MILLER                         No Commission

12行が選択されました。

CASE文の例

SELECT empno, ename, 
       CASE WHEN comm IS NULL THEN 'No Commission'
            ELSE 'Has Commission'
       END AS comm_status
FROM emp;
  • CASE文でも同様の実装が可能です。
【実行結果】

SQL> SELECT empno, ename,
  2         CASE WHEN comm IS NULL THEN 'No Commission'
  3              ELSE 'Has Commission'
  4         END AS comm_status
  5  FROM emp;

     EMPNO ENAME                          COMM_STATUS
---------- ------------------------------ ------------------------------------------
      7369 SMITH                          No Commission
      7499 ALLEN                          Has Commission
      7521 WARD                           Has Commission
      7566 JONES                          No Commission
      7654 MARTIN                         Has Commission
      7698 BLAKE                          No Commission
      7782 CLARK                          No Commission
      7839 KING                           No Commission
      7844 TURNER                         Has Commission
      7900 JAMES                          No Commission
      7902 FORD                           No Commission
      7934 MILLER                         No Commission

12行が選択されました。

NULLを含む算術式

NULLを含む算術式は結果がNULLになるので注意が必要です。例を下記します。

SELECT empno, ename, sal + comm AS total_salary
FROM emp;
  • COMMがNULLの場合、sal + commの結果はNULLとなります。
【実行結果】

SQL> SELECT empno, ename, sal, comm
  2  FROM emp;

     EMPNO ENAME                                 SAL       COMM
---------- ------------------------------ ---------- ----------
      7369 SMITH                                 800
      7499 ALLEN                                1600        300
      7521 WARD                                 1250        500
      7566 JONES                                2975
      7654 MARTIN                               1250       1400
      7698 BLAKE                                2850
      7782 CLARK                                2450
      7839 KING                                 5000
      7844 TURNER                               1500          0
      7900 JAMES                                 950
      7902 FORD                                 3000
      7934 MILLER                               1300

12行が選択されました。

SQL> SELECT empno, ename, sal + comm AS total_salary
  2  FROM emp;

     EMPNO ENAME                          TOTAL_SALARY
---------- ------------------------------ ------------
      7369 SMITH
      7499 ALLEN                                  1900
      7521 WARD                                   1750
      7566 JONES
      7654 MARTIN                                 2650
      7698 BLAKE
      7782 CLARK
      7839 KING
      7844 TURNER                                 1500
      7900 JAMES
      7902 FORD
      7934 MILLER

12行が選択されました。

NVL関数を使用することで回避できます。

SELECT empno, ename, sal + NVL(comm, 0) AS total_salary
FROM emp;
  • COMMがNULLの場合に0を代替して計算を行います。
【実行結果】

SQL> SELECT empno, ename, sal + NVL(comm, 0) AS total_salary
  2  FROM emp;

     EMPNO ENAME                          TOTAL_SALARY
---------- ------------------------------ ------------
      7369 SMITH                                   800
      7499 ALLEN                                  1900
      7521 WARD                                   1750
      7566 JONES                                  2975
      7654 MARTIN                                 2650
      7698 BLAKE                                  2850
      7782 CLARK                                  2450
      7839 KING                                   5000
      7844 TURNER                                 1500
      7900 JAMES                                   950
      7902 FORD                                   3000
      7934 MILLER                                 1300

12行が選択されました。

NULLを考慮した集計関数

NULLを含むデータを集計する場合、NULLは無視されます。ただし、COUNT(*)はNULLも含めてレコードをカウントします。

SUM関数の例

SELECT SUM(comm) AS total_commission
FROM emp;
  • COMMがNULLの場合、それらは無視されます。
【実行結果】

SQL> SELECT empno, ename, comm
  2  FROM emp;

     EMPNO ENAME                                COMM
---------- ------------------------------ ----------
      7369 SMITH
      7499 ALLEN                                 300
      7521 WARD                                  500
      7566 JONES
      7654 MARTIN                               1400
      7698 BLAKE
      7782 CLARK
      7839 KING
      7844 TURNER                                  0
      7900 JAMES
      7902 FORD
      7934 MILLER

12行が選択されました。

SQL> SELECT SUM(comm) AS total_commission
  2  FROM emp;

TOTAL_COMMISSION
----------------
            2200

COUNT関数の例

SELECT COUNT(comm) AS non_null_comm, COUNT(*) AS total_rows
FROM emp;
  • COUNT(comm)はNULLを除外し、COUNT(*)はすべての行をカウントします。
【実行結果】

SQL> SELECT COUNT(comm) AS non_null_comm, COUNT(*) AS total_rows
  2  FROM emp;

NON_NULL_COMM TOTAL_ROWS
------------- ----------
            4         12

業務シナリオでのNULLの活用例

売上管理システム

売上データを管理するシステムでは、コミッションがNULLの場合にデフォルト値を適用する必要があります。

SELECT order_id, customer_id, NVL(commission, 0) AS commission_amount
FROM sales;
  • コミッションが未設定の場合、0を適用して計算を簡略化します。

従業員情報の更新

従業員データベースで、NULL値を持つカラムにデフォルト値を設定する。

UPDATE emp
SET comm = NVL(comm, 0)
WHERE comm IS NULL;
  • COMMカラムがNULLの従業員に対して、一律で0を設定します。

月次レポート作成

月次売上の集計時にNULLを無視して計算を行う。

SELECT TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(NVL(sales_amount, 0)) AS total_sales
FROM sales
GROUP BY TO_CHAR(order_date, 'YYYY-MM');
  • 売上がNULLの場合は0として集計を行い、正確な月次データを生成します。

視覚的補足

以下の図は、CASE文によるNULL処理の流れを表しています。

+-----------+        IS NULL?         +----------------+
|   VALUE   | ----------------------> |  Return 'X'    |
+-----------+                        +----------------+
       |
       | IS NOT NULL
       v
+----------------+
| Return 'Y'     |
+----------------+

まとめ

  • NULLはOracleデータベースでは重要な概念です。
  • NULLの計算や判断を正しく理解して使用することで、正確なSQLを構築できます。
  • NVLやCOALESCEを活用して、NULLを適切に処理しましょう。
  • 集計関数の挙動や算術式での扱いを理解することが、より正確なデータ処理につながります。
  • 現実の業務シナリオでのNULLの使用例を参考にして、データ管理を効率化しましょう。

SCOTTスキーマのテーブルを使って実際に手を動かして覚えることをおすすめします。これで、NULLに関する知識をしっかり身につけることができました。実際のデータでも試してみましょう！

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

Oracleデータベースの基礎：サンプルスキーマで簡単に始める方法
サンプルスキーマって何？まず、サンプルスキーマとは何かから始めましょう。サンプルスキーマは、Oracleデータベースに含まれる「学習用のデータベース構造とデータ」のことで、インストールすることで学習に使用できるユーザーや表が作成されます。O...
oracle-master00000.com
2024.09.23

コメント

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