なぜNullを排除しなければならないのか？

テーブルを設計する際、Nullについてはあまり考慮されていないように思います。主キーは当然としてもむやみにNot Null制約を定義することは、テストデータの作り難さなどの理由でむしろ避けられているかもしれません。

ただし、緻密なテーブル設計を目的とした場合、Not Null制約によってNullデータを排除することは非常に重要です。

Nullには２つの意味がある

例えば

update CUSTOMERS set REMARKS = null where CUST_ID = 25000;

というDMLであたかも「Null」という値を設定できているような印象を持ってしまいますが、それではなぜ

select * from CUSTOMERS where REMARKS = null;

ではなく、

select * from CUSTOMERS where REMARKS is null;

というSQL文にしなければならないのでしょうか？プログラマのためのSQL 第4版 ジョー・セルコ著 には次のような一節があります。

「2値論理は、ONとOFFの2つしか状態を持たないバイナリコンピュータや多くの数学分野と極めて相性がいい。しかしSQLは3値論理を採用している。すなわち、TRUE、FALSE、UNKNOWNである。UNKNOWN という値は、比較述語や他の述語の戻り値として生じるものだが、れっきとした真理値であり、データがないことの目印であるNULLとは異なる。これが、SQLにおいて（x=NULL）ではなく（x IS [NOT] NULL） と書かねばならない理由である。NULL は値でも変数でもないので、値や変数にしか適用できない「＝」という述語は適用できないのだ。」（P.266）

つまり、SQLでは「状態UNKNOWN」としてのNullと、「データがないことの目印」であるNullが混在しているということが誤解を生みやすくなっています。

Nullを許すことの問題点

以下のSQLを見てみましょう。

SQL> select 1/null from dual;

    1/NULL
----------
(Null)

変数の中にNullが紛れ込むと、どんなほとんどの場合（もちろん例外もありますが）関数の結果がNullになります。

その他、外部キーの参照先にNullデータがあると参照整合性制約の定義としては問題なくても値が取得できない問題が発生します。

プログラマのためのSQL 第4版 ジョー・セルコ著 には次のような記述もあります。

「優秀なSQLプログラマならば、NULLに対処するにあたり、まずはDDLにおいてこれを排除することに全力を傾けるだろう。すべてのテーブルのすべての列に原則としてNOT NULL 制約を付与し、正当な理由がある場合にだけNULLの使用を許可する。間違っても、こういう努力をしないまま、クエリで実装依存の変換関数に頼ればいいなどと考えないでほしい。 NULLは、SQL を知らない人々を混乱させるのだ。 加えて、NULLを使うのはコストの高い行為でもある。というのも、通常、NULLはそれが現れる列の行に余計なピットフラグを持つことで実装されている。これは前述のとおり、NULLが列自身の値ではないためだ。これによって、ストレージ要件や、インデックスおよび検索条件において本来なら必要なかったことを考慮する必要がある。」（P.272）

セルコ曰く「NULLを排除した設計」とはどういうものでしょうか？

この部分は重要だと思うので、簡単な例を挙げて説明します。

顧客テーブルを例にして

どこにでもあるような顧客テーブル（CUSTOMERS）を考えてみます。

テーブル構造は以下の様な簡単なもので、50,000件のテストデータが格納されています。

性別カラムには必ず「M」または「F」の値が格納されていてそれぞれ25,000件ずつになるようにデータを作成しています。

SQL> select SEX,count(*) from CUSTOMERS group by SEX;

SEX    COUNT(*)
---- ----------
M         25000
F         25000

備考欄を追加する

ここで、顧客毎に異なる特記事項を記録する必要が生じたことを考えてみましょう。特記事項は長くなりそうなのでCLOB型で記録する必要があるものとします。

さあ、どのように変更したらよいでしょうか？

1. 単純にカラムを追加する（Nullを排除しない設計）

それでは、よくあるパターンとしてカラム追加で対応する方法を考えてみます。

元のテーブルを丸ごとコピーしたCUSTOMERS_1テーブルに、CLOB型のREMARKSカラムを追加してみましょう。

 

SQL> desc CUSTOMERS_1
 名前             NULL?    型
 ---------------- -------- ------------------
 CUST_ID          NOT NULL NUMBER(9)
 CUST_LNAME       NOT NULL VARCHAR2(30)
 CUST_FNAME       NOT NULL VARCHAR2(30)
 SEX              NOT NULL VARCHAR2(4)

SQL> alter table CUSTOMERS_1 add (REMARKS clob);

表が変更されました。

SQL> desc CUSTOMERS_1
 名前             NULL?    型
 ---------------- -------- -------------------
 CUST_ID          NOT NULL NUMBER(9)
 CUST_LNAME       NOT NULL VARCHAR2(30)
 CUST_FNAME       NOT NULL VARCHAR2(30)
 SEX              NOT NULL VARCHAR2(4)
 REMARKS                   CLOB

他のカラムはNot Null制約が付与されていますが、追加したばかりのREMARKSカラムはNullデータを許しています。

備考欄にデータを設定する

カラムを追加しただけではデータは「Null」なので、次のUpdate文でデータを設定します。

SQL> update CUSTOMERS_1 set REMARKS = ‘0' where mod(CUST_ID,5)  = 0;

10000行が更新されました。

SQL> update CUSTOMERS_1 set REMARKS = ‘1' where mod(CUST_ID,10) = 1;

5000行が更新されました。

SQL> update CUSTOMERS_1 set REMARKS = '2' where mod(CUST_ID,20) = 2;

2500行が更新されました。

SQL> update CUSTOMERS_1 set REMARKS = '3' where mod(CUST_ID,50) = 3;

1000行が更新されました。

SQL> commit;

コミットが完了しました。

SQL> select
  2   nvl(DBMS_LOB.SUBSTR(REMARKS, 1000, 1),'Null') REMARKS
  3  ,count(*)
  4  from
  5   CUSTOMERS_1
  6  group by
  7   DBMS_LOB.SUBSTR(REMARKS, 1000, 1)
  8  order by
  9   1;

REMARKS           COUNT(*)
--------------- ----------
0                    10000
1                     5000
2                     2500
3                     1000
Null                 31500

2. Nullを排除した設計

ジョー・セルコの言う「DDLにおいてこれを排除する」とは以下の様に備考欄を別テーブルに分割した構造です。

REMARKSカラムにはNot Null制約を定義し、Nullデータを入力できないようにします。

DDLは以下のようになります。（ここでは便宜上CUSTOMER_1表を元にデータと一緒に作成しています。）

SQL> create table CUST_2_REMARKS as select CUST_ID,REMARKS from CUSTOMERS_1 where REMARKS is not null;

表が作成されました。

SQL> alter table CUST_2_REMARKS add constraint FK_CUST_ID_2 FOREIGN KEY (CUST_ID) REFERENCES CUSTOMERS_2 (CUST_ID);

表が変更されました。

SQL> alter table CUST_2_REMARKS modify REMARKS not null;

表が変更されました。

SQL> desc CUST_2_REMARKS
 名前              NULL?    型
 ----------------- -------- -------------
 CUST_ID           NOT NULL NUMBER(9)
 REMARKS           NOT NULL CLOB

パッと見た感じ、２つのテーブルをジョインしなければならないしパフォーマンス的には不利なような気もしますが、次回は両者のパフォーマンスの違いを見てみましょう。

（続く）