NULLの動作仕様に注意

time 2016/04/04

folder ORACLE

プログラマを何かと悩ませるNULL、扱いを誤ったために障害となることもあります。
そして、ORACLEにおいてもNULLは存在し、動作仕様に注意が必要です。

本記事では、この悩ましいNULLの動作仕様について、まとめました。

ORACLEにおけるNULL

ORACLEを始めとするデータベースのNULLは、その項目がカラであることを指します。

具体的には、以下の2つのパターンでNULLとして設定します。
未知／不明…例.性別がよく分からない個人客の顧客マスタの性別項目
適用不能／非存在…例.法人客の顧客マスタの性別項目

いずれしろ、そのNULLの取り扱いには注意が必要になります。

NULLの取り扱いで注意すべき点

比較する際に、通常の比較演算子が使えない

比較を行う際に通常であれば、等しいかどうかを検査する際は【=】を使用しますし、等しくないかどうかを検査する際は【<>】や【!=】を使用します。

しかし、NULLを上記で比較するとUNKNOWN（不明）となり対象外となります。

NULLかどうかを検査する場合には【IS NULL】を使用し、NULL以外かどうかを検査する場合には【IS NOT NULL】を使用します。

A
-----
11111 …①
22222 …②
NULL  …③

SELECT a FROM test_table WHERE a = NULL;
⇒対象なし

SELECT a FROM test_table WHERE a <> NULL;
⇒対象なし

SELECT a FROM test_table WHERE a IS NULL;
⇒③が抽出

SELECT a FROM test_table WHERE a IS NOT NULL;
⇒①と②が抽出

-----

11111 …①

22222 …②

NULL …③

SELECT a FROM test_table WHERE a = NULL;

⇒対象なし

SELECT a FROM test_table WHERE a <> NULL;

⇒対象なし

SELECT a FROM test_table WHERE a IS NULL;

⇒③が抽出

SELECT a FROM test_table WHERE a IS NOT NULL;

⇒①と②が抽出

インデックスが効かない

上記のようにWHERE句でNULLかどうか評価した場合、インデックスが効きません。

それはインデックスは、NULL以外のデータを対象として構成されているためです。

SELECT a FROM test_table WHERE a = '11111';
⇒インデックスは有効

SELECT a FROM test_table WHERE a IS NULL;
⇒インデックスは使えない

SELECT a FROM test_table WHERE a = '11111';

⇒インデックスは有効

SELECT a FROM test_table WHERE a IS NULL;

⇒インデックスは使えない

演算で結果がNULLになる

NULLが一部に含まれた演算を行うと、結果もNULLになります。

SELECT a
      ,b
      ,a + b calc1
      ,a - b calc2
      ,a * b calc3
      ,a / b calc4
FROM test_table
;

-- 検索結果（NULLは【NULL】と表現）
A     B     CALC1 CALC2 CALC3 CALC4
----- ----- ----- ----- ----- -----
    6     2     8     6    12     3
    6  NULL  NULL  NULL  NULL  NULL
 NULL     2  NULL  NULL  NULL  NULL
 NULL     0  NULL  NULL  NULL  NULL ← ゼロ割エラーも発生しない

SELECT a

,a + b calc1

,a - b calc2

,a * b calc3

,a / b calc4

FROM test_table

;

-- 検索結果（NULLは【NULL】と表現）

A B CALC1 CALC2 CALC3 CALC4

----- ----- ----- ----- ----- -----

6 2 8 6 12 3

6 NULL NULL NULL NULL NULL

NULL 2 NULL NULL NULL NULL

NULL 0 NULL NULL NULL NULL ← ゼロ割エラーも発生しない

標準関数の戻り値が想定と異なることも

ORACLEの標準関数も、NULLが引数で渡した際に、関数によって動作が異なるので、想定と異なってしまうことが考えられます。

例えば、文字列の長さを戻すLENGTHは、引数としてNULLを渡すとNULLを戻します。（0ではない）
また、文字列を連結するCONCATは、2つの引数が両方ともNULLの場合はNULLを戻しますが、どちらか一方がNULL以外の場合は、その値を戻します。

SELECT a
      ,LENGTH(a)  func
FROM   test_table
;

-- 検索結果（NULLは【NULL】と表現）
A       FUNC
------ -----
ABCDE      5
NULL    NULL

SELECT a

,LENGTH(a) func

FROM test_table

;

-- 検索結果（NULLは【NULL】と表現）

A FUNC

------ -----

ABCDE 5

NULL NULL

SELECT a
      ,b
      ,CONCAT(a,b) func
FROM   test_table
;

-- 検索結果（NULLは【NULL】と表現）
A     B     FUNC
----- ----- ------
ABC   DEF   ABCDEF
NULL  DEF   DEF
ABC   NULL  ABC
NULL  NULL  NULL