3値論理とNULL

SQLの動作を混乱させる第3の真理値「unknown」とNULLの危険性

実践編（続き）

4．限定述語とNULL

　SQLは、ALLとANYという2つの限定述語を持っています。もっとも、ANYはINと同値なのであまり使われません。そこで本稿でも、よく使われるALLの注意点を見ていきます。

　ALLは、比較述語と併用して「～すべてと等しい」や「～すべてよりも大きい」という意味を表します。さっき使ったBクラスのテーブルからNULLを排除したケースで、「Bクラスの東京在住の誰よりも若いAクラスの生徒」を取得するSQLを考えます。

「Class_A」テーブル

名前（name）	年齢（age）	住所（city）
ブラウン	22	東京
ラリー	19	埼玉
ボギー	21	千葉

「Class_B」テーブル

名前（name）	年齢（age）	住所（city）
斎藤	22	東京
田尻	23	東京
山田	20	東京
和泉	18	千葉
武田	20	千葉
石川	19	神奈川

　ALL述語を使うと、次のようにストレートに表現できます。

--Bクラスの東京在住の誰よりも若いAクラスの生徒を選択する
SELECT *
  FROM Class_A
 WHERE age < ALL ( SELECT age
                     FROM Class_B
                    WHERE city = '東京' );

結果

name      age        city
--------  --------   --------
ラリー          19   埼玉

　20歳の山田君より若いラリーだけが選択されます。ここまでは、何の問題もありません。問題が生じるのは、例によって山田君が年齢不詳のときです。直観に従えば、今度は22歳の斎藤君より若いラリーとボギーの二人が選択されるように思われます。ところが、このSQLの結果はまたもや空になります。これは、ALL述語が条件をANDで連結した論理式の省略形として定義されているからです。具体的には、次のようなステップで評価されます。

--1．サブクエリを実行して年齢のリストを取得
SELECT *
  FROM Class_A
 WHERE age < ALL ( 22, 23, NULL );

--2．ALL述語をANDで同値変換
SELECT *
  FROM Class_A
 WHERE (age < 22) AND (age < 23) AND (age < NULL);

--3．NULLに < を適用すると unknown になる
SELECT *
  FROM Class_A
 WHERE (age < 22)  AND (age < 23) AND unknown;

--4．AND の演算にunknownが含まれると結果がtrueにならない
SELECT *
  FROM Class_A
 WHERE false または unknown;

　いかがでしょう。随所で見せるNULLの暴れっぷりが、お分かりいただけたでしょうか。

4．限定述語と極値関数は同値ではない

　ALL述語を極値関数で代用している人も、少なくないと思います。さっきのSQLを極値関数で書き直すと次のようになります。

--Bクラスの東京在住の最も若い生徒より若いAクラスの生徒を選択する
SELECT *
  FROM Class_A
 WHERE age < ( SELECT MIN(age)
                 FROM Class_B
                WHERE city = '東京' );

結果

name      age        city
--------  --------   --------
ラリー          19   埼玉
ボギー          21   千葉

　素晴らしいことに、このSQLは山田君の年齢がNULLの場合でも、ちゃんとラリーとボギーの二人を選択します。これは、極値関数が集計の際にNULLを排除するという特性を持っているからです。極値関数を使うことで、「Class_B」テーブルは、あたかもNULLが存在しないかのような扱いを受けます。

　「なんだ、それならいつでも極値関数の方を使えば安全じゃないか」と思った方、残念ながら3値論理の世界では、ことはそう単純に運びません。ALL述語と極値関数が表現する命題を書き並べると、次のようになります。

• ALL述語：彼は東京在住の生徒の誰よりも若い 　　　………… Q₁
• 極値関数：彼は東京在住の最も若い生徒よりも若い　………… Q₂

　現実の世界では、この2つの命題は同じことを言っています。テーブルにNULLが含まれるときに同値性が崩れることも、既に見た通りです。ところで、実はもう一つ、Q₁とQ₂が同値ではなくなるケースがあります。それがどんなケースか、分かりますか？

　それは、述語と関数の入力が空集合だった場合です。例えば、「Class_B」テーブルが次のような状態を考えます。

「Class_B」テーブル： 東京在住の生徒がいない！

名前（name）	年齢（age）	住所（city）
和泉	18	千葉
武田	20	千葉
石川	19	神奈川

　見ての通り、Bクラスには東京在住の生徒が一人もいません。このとき、ALL述語を使ったSQLはAクラスの全員を選択します。一方、極値関数を使うと一行も選択されません。これは、入力が空テーブル（空集合）だった場合はNULLを返すという極値関数の仕様によります。従って、極値関数を使ったSQLの評価は、次のようなステップを踏みます。

--1．極値関数がNULLを返す
SELECT *
  FROM Class_A
 WHERE age < NULL;

--2．NULLに < を適用すると unknown になる
SELECT *
  FROM Class_A
 WHERE unknown;

　比較対象がそもそも存在しない場合、全行を返すのと一行も返さないのと、どちらが望ましいかは業務要件にもよります。もし全行を返す必要のある場合は（イメージとしては「不戦勝」のようなものでしょうか）、ALL述語を使うか、COALESCE関数を使って極値関数の返すNULLを適当な値に変換せねばなりません。

5．集約関数とNULL

　入力が空テーブルだった場合にNULLを返すのは、極値関数だけではありません。COUNT関数以外の集約関数もそうです。そのため、次のようなごく平凡なSQLでさえ、奇妙な振舞いを見せます。

--東京在住の生徒の平均年齢より若いAクラスの生徒を選択するSQL？
SELECT *
  FROM Class_A
 WHERE age < ( SELECT AVG(age)
                 FROM Class_B
                WHERE city = '東京' );

　東京在住の生徒がいない場合、AVG関数はNULLを返します。そのため外側のWHERE句が常にunknownになり、一行も選択されません。SUMの場合も同様です。対処としては、やはりNULLを何らかの値に変換するか、目をつむって結果を受け入れるかの二択です。NOT NULL制約のついた列に平均値や合計値をINSERTするような場合には、値へ変換するほかありません。

　集約関数と極値関数についてまわるこのトラップは、関数の仕様が原因で生じるタイプのものなので、テーブルの列にNOT NULL制約を付加するぐらいでは根絶できません。そのため、システム開発の際にはよく注意する必要があります。

おわりに

　以上、NULLと3値論理がSQLコーディングにもたらす問題点を、理論と実践の両面から見てきました。もう一度、要点をまとめておきます。

1. NULLは値ではない。
2. 値ではないので、述語もまともに適用できない。
3. 無理やり適用するとunknownが生じる。
4. unknownが論理演算に紛れ込むと、SQLが直観に反する動作をする。
5. これに対処するには、段階的なステップに分けてSQLの動作を追うことが有効。

　そして最後に、NULLがもたらす問題に対する最善の対策を挙げるなら、テーブルにNOT NULL制約をつけて極力NULLを排除することです。そうすれば、美しい2値論理の世界を（完全にではないけど）取り戻すことができます。そのための具体的な方策については、また回を改めてお話しましょう。

参考資料

1. 『プログラマのためのSQL 第2版』 J. セルコ 著、ピアソンエデュケーション、2001年4月
   特に、第6章「NULL―SQLの失われたデータ」および14.3「NULLとIN述語」、15.1「EXISTSとNULL」は必読。

2. 『C. J. Dateのデータベース実践講義』 C. J. デイト 著、オライリージャパン、2006年2月
   NULLと3値論理の使用に反対するデイトの主張が明確に出ています。セルコの本に比べて理論的な解説に重点が置かれています。冒頭のデイトの言葉はこの本からの引用。

3. 『論理学をつくる』 戸田山和久 著、名古屋大学出版会、2000年10月
   論理学の入門書としては珍しく、3値論理に少し触れています。ただし、同じ3値論理でもSQLが採用している論理体系とは少し異なるので、その点は要注意。述語論理の入門書としてもお薦め。

4. 初級C言語Q&A（3） 『【0とNULL】』
   Q：「NULLポインタの代わりに0を使ったコードを見たことがある。なぜNULLの代わりに0を使ってもよいのか」