T字形ER手法でnullを認めない理由(3) - 極北データモデリング

T字形からどんどん離れていくが、SQLでの算術演算について整理する。

算術演算のnull

しつこいようだがおさらい。
SQLでのnullは「何か値があるはずだけど、今は分からない」という意味。
なので、nullに対して四則演算すると、何でもnullになる。
「何だか分からない数」に100を足しても、答えは「何だか分からない数」にしかならないから。

... 上記の説明にはごまかしがある。
例えば

customer テーブルの age 列には顧客の年齢が収容されている
年齢の分からない顧客の age 列には、nullが入っている
顧客IDが 100 番の人の年齢は不明である

とする。
nullが本当に「何か値があるはずだけど、今は分からない数」であれば、

select age - age from customer where customer_id=100

の答えは0になるはずだし、

select age / 0 from customer customer_id=100

は0divideの例外が発生しそうなものだが、SQLではいずれもnullが返ってくる*1。
とにかく、SQLの算術演算では、nullが絡むと答えはnullにしかならないらしい。

集計関数とnull

＜ss2004さんのコメントを受けて追記＞
SQLの集計関数では、集計対象の列がnullの行は無視される。
このことは、nullの回避のため代わりに0を設定する、みたいなアプローチとの兼ね合いで注意が必要。
sum(), max() については特に問題ないが...
min()は、nullの代わりに設定した0を最小値として検出する（マイナスの値が無ければ）。
これをよしとするかどうか、アプリケーションの仕様の確認が必要。
avg()は、不明な値を表すのにnullを使うのと0を使うのとで結果が変わる。
「値が不明」の行も含めた平均を出すべきかどうか、アプリケーションの仕様の確認が必要。

値がundefinedの場合

プログラマが期待するnullの意味には、「値が不明」=unknownだけではなく「値があり得ない」=undefinedもある。
プログラマがundefinedのつもりでnullを使っている時、SQLのnullの挙動は「直感に反する」ものになる。

以下の従業員給与テーブルがあるとする。

従業員給与 = { 従業員コード, 支給年月, 給与額, 賞与額 }

2006年6月の、従業員ごとの給与・賞与合計を以下のクエリで求める場合、

select 従業員コード, 給与額 + 賞与額 as 支給総額 from 従業員給与 where 支給年月=200606

賞与額未定のため賞与額列がnull(=unknown)の該当レコードでは、支給総額もnullになる。
これはクエリの発行者が期待した結果ではないかもしれないが、「従業員ごとの支給総額を知りたい」という意図に対する一つの正しい回答ではある。
「支給することは決まっているが額が分からない」ものを足したのだから、結果も「額が分からない」にしかなりようがない。

一方、パート社員なので賞与が支給されない、という理由で賞与額列がnull(=undefined)の該当レコードがあった場合も、支給総額がnullになる。
これは「従業員ごとの支給総額を知りたい」という目的には合わない挙動。この場合のnullは0のように扱われることが期待される。
このケースでは賞与額について、nullの代わりに0を設定するのがベター。
ただし、avg(賞与額)やmin(賞与額)の結果が変わってしまうことに注意。

まとめ

結論、数値項目にnullなんて許可したくないが、許可する場合は unknown の意味でnullを使用すべき。
undefined な意味でnullを許可すると、算術演算対象にした時に「直感に反する」結果になる。
また、null回避のため不明な値の代理として0などを設定する場合は、その列が avg() や min() の対象になったときに問題が起きないか検証しておく必要がある。

*1:PostgreSQL 7.3.6で確認