3値論理とNULL

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

実践編

1．比較述語とNULL　その1：排中律が成立しない

　ジョンを人間とします。このとき、次の記述文（以後、「命題」と呼びます）の真偽はどうなるでしょう。

ジョンは20歳か、20歳でないか、どちらかである。 ………… P

　正しいと思いますか？ そう、現実世界では文句なく正しい命題です。ジョンが誰かは知りませんが、人間であれば年齢を持ちます。そして年齢を持つなら、20歳か20歳でないか、どちらかに決まります。他にも、「カエサルはルビコン川を渡ったか、渡らなかったか、どちらかだ」、「宇宙人はいるか、いないか、どちらかだ」などもやはり正しい命題です。このように、「命題とその否定を「または」でつなげて出来る命題は全て真である」という命題を、2値論理で排中律（excluded middle）と呼びます。名前の通り、中間を認めず、白黒はっきり命題の真偽が定まるという意味で、古典論理学の重要な原理です。この原理を認めるか否かが、古典論理と非古典論理の分かれ目になるほどです。

　さて、もし排中律がSQLにおいても成立するなら、次のクエリはテーブルの全行を選択するはずです。

--年齢が20歳か、20歳でない生徒を選択せよ
SELECT *
  FROM Students
 WHERE age = 20
    OR age <> 20;

　ところが、SQLでは排中律が成立しません。「Students」テーブルが次のような状態を考えてください。

「Students」テーブル

　このSQLは、年齢不詳のジョンを選択できません。理論編で述べたように、NULLを含む比較ではunknownが生じるからです。具体的には、ジョンの行は次のようなステップを踏んで評価されます。

-- 1．ジョンは年齢がNULL（未知のNULL！）
SELECT *
  FROM Students
 WHERE age = NULL
    OR age <> NULL;

-- 2．比較述語にNULLを適用するとunknownになる
SELECT *
  FROM Students
 WHERE unknown
    OR unknown;

-- 3．「unknown OR unknown」はunknownになる
--（理論編のマトリックスを参照）
SELECT *
  FROM Students
 WHERE unknown;

　SQLで選択結果に含まれるのは、trueに評価される行だけです。彼を結果に含めるには、次のような「第3の条件」を追加する必要があります。

--第3の条件を追加：「年齢が20歳か、20歳でないか、または年齢が分からない」
SELECT *
  FROM Students
 WHERE age = 20
    OR age <> 20
    OR age IS NULL;

　このように、現実世界では正しいことが、SQLでは正しくないという事態が、しばしば起こります。ジョンは実際には年齢を持ちます。しかし、このテーブルを利用する私たちは、彼が何歳かを知りません。言い換えるならば、関係モデルは、現実を記述するモデルではなく、人間の認識状態を記述する心（知識）のモデルです。そのため、私たちの有限で不完全な知識が、ダイレクトにテーブルにも反映されます。

　私たちにとってジョンの年齢は不明でも、彼が現実世界において「20歳か、または20歳でない」ことは確実だ――私たちは、ごく自然にそう考えがちです。しかしその直観は、3値論理においては保証されません。

2．比較述語とNULL　その2：CASE式とNULL

　以前、「CASE式のススメ（後編）」でも取り上げた例なので、覚えている人もいるかもしれません。そのときは詳しい説明を省略したので、ここで再度おさらいしておきましょう。次の単純CASE式を見てください。

--col_1が 1 なら「○」を、NULLなら「×」を返すCASE式？

CASE col_1
  WHEN 1     THEN '○'
  WHEN NULL  THEN '×'
END

　このCASE式は、絶対に「×」を返しません。その理由は、二つ目のWHEN句が「col_1 = NULL」の省略形だからです。既にお分かりのように、この式は常にunknownに評価されます。そしてCASE式の評価方法も、WHERE句の場合と同じく、trueの場合のみ有効となります。

　この種の間違いは頻繁に見かけますが、これが起こる原因は、NULLを値だと勘違いしているからです。一つ目のWHEN句の「1」と同列に書き並べていることからも、それが分かります。NULLが値だという誤った思い込みを、ここでもう一度振り払っておきましょう。

3．NOT INとNOT EXISTSは同値ではない

　INをEXISTSで書き換えることは、パフォーマンス・チューニングのテクニックとしてよく行なわれます。これは、問題のない同値変換です。問題は、NOT INをNOT EXISTSで書き換える場合には、必ずしも結果が一致しないことです。

　例として、次のような学校の2つのクラスを表現するテーブルを見てください（1テーブルにまとめることもできますが、サブクエリを分かりやすくするために2テーブルに分けています）。

「Class_A」テーブル

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

「Class_B」テーブル

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

　Bクラスの山田君の年齢がNULLになっている点に注目してください。このテーブルを使って、「Bクラスの東京在住の生徒と年齢が一致しないAクラスの生徒」を選択するクエリを考えます。つまり、欲しい結果はラリーとボギーの二人です。ブラウンは斎藤君と年齢が一致するので対象外です。素直にこの条件をSQLにすると、次のようになります。

--Bクラスの東京在住の生徒と年齢が一致しないAクラスの生徒を選択するSQL？
SELECT *
  FROM Class_A
 WHERE age NOT IN ( SELECT age
                      FROM Class_B
                　   WHERE city = '東京' );

　さて、このSQLは本当に二人を選択するでしょうか？ 残念ながら、そうはなりません。結果は空、つまり一行も選択されません。

　確かに、山田君の年齢がNULLでなければ（そして二人と年齢が一致しなければ）、このSQLは問題なく二人を返します。しかし、ここでもNULLが悪事を働きます。どんな動作をしているのか、段階的に見てみましょう。

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

--2．NOT INを NOTとINを使って同値変換
SELECT *
  FROM Class_A
 WHERE NOT age IN (22, 23, NULL);

--3．IN述語をORで同値変換
SELECT *
  FROM Class_A
 WHERE NOT ( (age = 22) OR (age = 23) OR (age = NULL) );

--4．ド・モルガンの法則を使って同値変換
SELECT *
  FROM Class_A
 WHERE NOT (age = 22) AND NOT(age = 23) AND NOT (age = NULL);

--5．NOTと = を <> で同値変換
SELECT *
  FROM Class_A
 WHERE (age <> 22) AND (age <> 23) AND (age <> NULL);

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

--7．AND の演算にunknownが含まれると結果がtrueにならない
--（理論編のマトリックス参照）
SELECT *
  FROM Class_A
 WHERE falseまたはunknown;

　Aクラスの全行について、この面倒なステップを踏んだ評価が行なわれます。結果として、WHERE句は一行もtrueに評価されません。すなわち、NOT INのサブクエリで使用されるテーブルの選択列にNULLが存在する場合、SQL全体の結果は常に空になります。これは恐ろしい現象です。

　正しい結果を得るには、EXISTS述語を使って書きます。

--正しいSQL：ラリーとボギーが選択される。
SELECT *
  FROM Class_A  A
 WHERE NOT EXISTS ( SELECT *
                      FROM Class_B B
                     WHERE A.age = B.age
                       AND B.city = '東京' );

結果

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

　こちらも、年齢がNULLの行の評価プロセスを段階的に追ってみましょう。

--1．サブクエリにおいてNULLとの比較を行う
SELECT *
  FROM Class_A A
 WHERE NOT EXISTS ( SELECT *
                      FROM Class_B B
                     WHERE A.age = NULL
                       AND B.city = '東京' );

--2．NULL に = を適用すると unknown になる
SELECT *
  FROM Class_A A
 WHERE NOT EXISTS ( SELECT *
                      FROM Class_B B
                     WHERE unknown
                       AND B.city = '東京' );


--3．AND の演算にunknownが含まれると結果がtrueにならない
SELECT *
  FROM Class_A A
 WHERE NOT EXISTS ( SELECT *
                      FROM Class_B B
                     WHERE falseまたはunknown);

--3．サブクエリが結果を返さないので、反対にNOT EXSITSは true になる
SELECT *
  FROM Class_A A
 WHERE true;

　いわば山田君は「誰とも年齢が一致しない人物」として扱われます（ただし、これが最終的な結果ではなく、斎藤君や田尻君との比較結果とANDで結ばれます）。このような結果になる理由は、EXISTS述語が絶対にunknownを返さないからです。EXISTSは、trueとfalseしか返しません。そのために、INとEXISTSは同値変換が可能なのに、NOT INとNOT EXISTSは同値ではないという紛らわしい状況が生じています。プログラミングの際に直観に頼ることができないというのは困難な条件ですが、DBエンジニアはこの現象をよく理解しておく必要があります。