OracleデータベースにおけるNULLは、データの欠損や不明な値を表す特別な概念です。NULLの扱い方を正しく理解することで、データの正確性や整合性が向上します。本記事では、SCOTTスキーマのEMPテーブルを用いて、NULLの基本概念、実用例、NULLが引き起こすトラブルを回避するためのテクニックを詳しく解説します。
SCOTTスキーマは以下を実行することでインストールが可能です。
SQL> @?/rdbms/admin/utlsampl.sql
また、本記事で使用するため、EMP表に以下の行をinsertしています。
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, deptno)
VALUES (9999, ‘NULL_SAL’, ‘CLERK’, 7902, TO_DATE(‘2024-11-01’, ‘YYYY-MM-DD’), NULL, 20);
SQL> SELECT empno, ename, mgr, sal FROM emp;
EMPNO ENAME MGR SAL
---------- -------------------- ---------- ----------
7369 SMITH 7902 800
7499 ALLEN 7698 1600
7521 WARD 7698 1250
7566 JONES 7839 2975
7654 MARTIN 7698 1250
7698 BLAKE 7839 2850
7782 CLARK 7839 2450
7839 KING 5000
7844 TURNER 7698 1500
7900 JAMES 7698 950
7902 FORD 7566 3000
7934 MILLER 7782 1300
9999 NULL_SAL 7902 ★
13行が選択されました。
NULLとは？何が「無」なのか？
NULLは「データが存在しない状態」を意味します。これは0や空文字とは異なり、まったくの「不明な状態」を示します。たとえば、給与情報がまだ入力されていない従業員の場合、その給与（sal列）はNULLとなります。
SELECT empno, ename, sal
FROM emp
WHERE sal IS NULL;
上記のSQLでは、給与が未入力の従業員（sal列がNULL）を抽出します。
SQL> SELECT empno, ename, sal
2 FROM emp
3 WHERE sal IS NULL;
EMPNO ENAME SAL
---------- -------------------- ----------
9999 NULL_SAL
NULLの特性：データがあってもなくても違う扱い
NULLには通常の値と異なる特性があります。
- 比較できない：
=や!=のような演算子ではNULLを判定できません。 - 他のNULLとも等しくない：NULLは、他のNULLとも等しくないとみなされます。
これらの特性により、特別な検索方法が必要です。
NULLの扱い方：基本的なクエリ
NULLに関する操作には、特別な記述が必要です。以下に基本的なクエリをいくつか紹介します。
NULLを条件に検索する：IS NULL と IS NOT NULL
NULLを含むデータを検索する場合、IS NULLやIS NOT NULLを使います。-- 上司（mgr列）が不明な従業員を表示
SELECT empno, ename, mgr
FROM emp
WHERE mgr IS NULL;
これは上司の情報がない従業員を表示します。NULLの有無によってビジネスロジックを分ける場合にも有用です。
SQL> SELECT empno, ename, mgr
2 FROM emp
3 WHERE mgr IS NULL;
EMPNO ENAME MGR
---------- -------------------- ----------
7839 KING
SQL> SELECT empno, ename, mgr
2 FROM emp
3 WHERE mgr = NULL;
レコードが選択されませんでした。 ★mgr = NULLとしてもselectできない
-- 給与が設定されている従業員を抽出
SELECT empno, ename, sal
FROM emp
WHERE sal IS NOT NULL;
給与情報がある従業員のみを検索したい場合に便利です。
SQL> SELECT empno, ename, sal
2 FROM emp
3 WHERE sal IS NOT NULL;
EMPNO ENAME SAL
---------- -------------------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7839 KING 5000
7844 TURNER 1500
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
12行が選択されました。
NULLとLIKEの違いに注意
NULLは文字列とみなされないため、LIKE句でのパターン検索には一致しません。以下のSQLはename列がNULLの行を対象にしません。SELECT empno, ename
FROM emp
WHERE ename LIKE 'A%';
たとえば、特定の条件でNULLを含むかどうかも意識してフィルタリングを行う必要があります。
SQL> SELECT empno, ename
2 FROM emp
3 WHERE ename LIKE 'A%';
EMPNO ENAME
---------- --------------------
7499 ALLEN
NULLを含む計算の注意点
NULLを含む数値の計算では、結果もNULLとなります。たとえば、給与に100を加算する計算では、給与がNULLの従業員は計算結果もNULLになります。SELECT empno, ename, sal, sal + 100 AS sal_with_bonus
FROM scott.emp;
ここではsalがNULLの従業員に対してsal_with_bonusもNULLとして表示されます。この性質を意識して計算することが重要です。
SQL> SELECT empno, ename, sal, sal + 100 AS sal_with_bonus
2 FROM scott.emp;
EMPNO ENAME SAL SAL_WITH_BONUS
---------- -------------------- ---------- --------------
7369 SMITH 800 900
7499 ALLEN 1600 1700
7521 WARD 1250 1350
7566 JONES 2975 3075
7654 MARTIN 1250 1350
7698 BLAKE 2850 2950
7782 CLARK 2450 2550
7839 KING 5000 5100
7844 TURNER 1500 1600
7900 JAMES 950 1050
7902 FORD 3000 3100
7934 MILLER 1300 1400
9999 NULL_SAL ★NULL
13行が選択されました。
NULLの並び替え：ORDER BY NULLS FIRST / NULLS LAST
デフォルトでは、OracleではNULLがソート時に最後に配置されます。特定の状況では、NULLを先頭に配置したり、逆に最後に配置したい場合があるでしょう。-- NULLを先頭に並べる
SELECT empno, ename, sal
FROM emp
ORDER BY sal NULLS FIRST;
NULLを最後に配置するにはNULLS LASTを指定します。
SQL> SELECT empno, ename, sal
2 FROM emp
3 ORDER BY sal NULLS FIRST;
EMPNO ENAME SAL
---------- -------------------- ----------
9999 NULL_SAL
7369 SMITH 800
7900 JAMES 950
7521 WARD 1250
7654 MARTIN 1250
7934 MILLER 1300
7844 TURNER 1500
7499 ALLEN 1600
7782 CLARK 2450
7698 BLAKE 2850
7566 JONES 2975
7902 FORD 3000
7839 KING 5000
13行が選択されました。
-- NULLを最後に並べる
SELECT empno, ename, sal
FROM emp
ORDER BY sal NULLS LAST;
NULLの配置によってデータの表示が大きく変わるため、分析やレポート作成時に考慮する価値があります。
SQL> SELECT empno, ename, sal
2 FROM emp
3 ORDER BY sal NULLS LAST;
EMPNO ENAME SAL
---------- -------------------- ----------
7369 SMITH 800
7900 JAMES 950
7521 WARD 1250
7654 MARTIN 1250
7934 MILLER 1300
7844 TURNER 1500
7499 ALLEN 1600
7782 CLARK 2450
7698 BLAKE 2850
7566 JONES 2975
7902 FORD 3000
7839 KING 5000
9999 NULL_SAL
13行が選択されました。
実用的なNULLの使用例とポイント
NULLを適切に扱うためには、特定のクエリやデータ処理における配慮が必要です。
集計時のNULLの考慮
NULLは集計計算に影響を与えることがあるため、SUMやCOUNTなどの集計関数を使う際には注意が必要です。たとえば、以下のような場合、給与がNULLのデータを意図せずに除外してしまう可能性があります。SELECT deptno, COUNT(sal) AS num_of_employees
FROM emp
GROUP BY deptno;
このクエリでは、salがNULLの従業員はカウントから除外されます。部門の社員数や総給与額などの集計に影響が出るため、NULLに対する扱いを検討しましょう。
SQL> SELECT deptno, COUNT(sal) AS num_of_employees
2 FROM emp
3 GROUP BY deptno;
DEPTNO NUM_OF_EMPLOYEES
---------- ----------------
30 6
10 3
20 3
WHERE句とNULLのトリッキーな関係
NULLは特殊な存在のため、WHERE句での条件設定が他のデータと異なります。たとえば、次のクエリでは、salがNULLの場合はフィルタリングが行われません。SELECT empno, ename, sal
FROM emp
WHERE sal = 0;salが0の従業員を取得したい場合はこのクエリで良いですが、NULLのデータも含めたい場合は、条件にOR sal IS NULLを追加する必要があります。
SQL> SELECT empno, ename, sal
2 FROM emp
3 WHERE sal = 0;
レコードが選択されませんでした。
SELECT empno, ename, sal
FROM scott.emp
WHERE sal = 0 OR sal IS NULL;
このように、NULLに関する特殊な条件設定を理解しておくと、誤ったデータを取得するリスクが減ります。
SQL> SELECT empno, ename, sal
2 FROM scott.emp
3 WHERE sal = 0 OR sal IS NULL;
EMPNO ENAME SAL
---------- -------------------- ----------
9999 NULL_SAL
まとめ
NULLは「不明」または「欠損値」を意味する特別な値であり、Oracleデータベースでは特別な扱いが求められます。NULLに関する特性や操作方法を理解することで、データの正確な管理が実現します。今回の内容を参考にしながら、SCOTTスキーマを使ってNULLに関するさまざまな操作を試してみてください。
NULLの存在を意識することで、データの信頼性や精度を大幅に向上させることができるでしょう。
[参考]
SQL言語リファレンス – NULL
コメント