NULL と Unknown を分かり易く説明すると。

はじめに

SQLを勉強し始めた際、NULL と Unknown を理解するのが難しいと感じました。調べても分かり易い説明が見つかりません。そこで、自然に解釈できるような説明を試みます。

コアイメージ

一言で表現すると、

• unknownは、true/false のどちらになるか未確定な状態
• NULLは、カラムに値が未入力の状態。

になります。こちらのイメージを元に、関連する定義を振り返ってみましょう。

論理演算(not/and/or)の評価

最初に、unknownを含む場合の論理演算の評価について説明します。一般的な論理は２値論理(true/false)ですが、SQLでは３値論理（ture/false/unknown)が使われます。

３値論理における not/and/or の定義は以下になります。

x	not x
true	false
false	true
unknown	unknown

x	y	x and y
true	true	true
true	false	false
true	unknown	unknown
false	true	false
false	false	false
false	unknown	false
unknown	true	unknown
unknown	false	false
unknown	unknown	unknown

x	y	x or y
true	true	true
true	false	true
true	unknown	true
false	true	true
false	false	false
false	unknown	unknown
unknown	true	true
unknown	false	unknown
unknown	unknown	unknown

ピンときませんね。x や y が unknown の場合、true/false/unknownどの値になるでしょう。そこで、コアイメージをもとに考えてみます。例を挙げます。

例1

下記のケースを考えます。

x	y	x and y
true	unknown	unknown

"x and y"はunknownと解釈されます。なぜでしょう。unknown の y はtrue/falseのどちらになるか未確定の状態です。この場合、もし

• y がtrueであれば、"x and y" はtureになります。
• yがfalseであれば、"x and y" はfalseになります。

つまり、"x and y" も true/false のどちらになるか未確定な状態です。従って、"x and y" もunknownと解釈すればよいことになります。

例2

次に、下記のケースを考えます。

x	y	x and y
false	unknown	false

今度は"x and y"はfalseと解釈されます。なぜでしょう。この場合、

• y がtrueであれば、"x and y" はfalseになります。
• yがfalseであれば、"x and y"はfalseになります。

y がtrue/falseどちらの値だったとしても、"x and y"の値はfalseで確定しています。確定している場合は、確定している値として解釈しましょう。従って、"x and y" はfalseとなります。

まとめ

andについて２ケースを見てきました。andの残りの３ケースや not/or についても、コアイメージをもとに説明すれば、同じように自然な解釈ができます。

条件文の評価

NULLのコアイメージは、カラムに値がまだ入力されていない状態です。条件文がNULLとなるカラムを含む場合、値が入力されるまで、条件文の評価(true/false)は未確定な状態です。その場合、unknownと評価されます。例1を参照ください。

例1

条件文 col1 == col2 を考えます。ただし、col1 = 'aaa' で col2 = NULL とします。この場合、col2 に'aaa'が入力されれば、条件文はtrueですが、それ以外が入力されれば、falseです。条件文に未入力な項目がある場合は、評価(true/false)を確定できないため、unknownになります。

ただし、条件文にNULLが含まれる場合でも、評価が確定する場合があります。例２を参照ください。

例2

下記の条件文3は、true と評価されます。

• 条件文3 を 条件文1 or 条件文2 とする。
• 条件文1 を col1 == col2 とする。
• 条件文2 を 'aaa' == 'aaa' とする。

なぜなら、例1のように条件文1がunknownと評価されたとしても、条件文2は常にtrueです。論理演算の評価で説明したとおり、条件3はtrueとなるからです。

where句の動作(TODO)

where句において、unknownの取り扱いを解説します。
下記のようなSQLを考えます。

select 略 from テーブル名 where 条件文

このとき、下記のルールでレコードがヒットします。

1. 条件文がtrueと評価されたレコードはヒット。
2. 条件文がfalseと評価されたレコードはヒットしない。
3. 条件文がunknownと評価されたレコードはヒットしない。

見逃しがちなのが3番目です。3番目の理解が必要な場面があります。例えば、直感的に、あるテーブルTに含まれるレコードは 下記どちらかにヒットしそうです。

1. select * from T where col1 in {略}
2. select * from T where col1 not in {略}

しかし、どちらにも含まれないレコードが存在する場合があります。それは、unknownと評価されるレコードがある場合です。なぜなら、inがunknownと評価された場合、not in もunknownと評価され、1. 2. どちらにもヒットしないためです。

なお、in がunkownと評価される場合は以下のようなケースがあります。

• col1 が NULL
• col1 in {NULL}

つまり、NULLを含む条件では、直感に反して、排中律「 A or not A 」が成り立たない場合があるということです。

例えば、TODO

述語の評価(TODO)

述語(exists,in 句など)にNULLが含まれる場合の自然な解釈について説明します。
exisitsとinの結果が一致にも関わらず、not exsistとnot in の結果は一致しない場合についての自然な解釈を試みます。

in

in句の評価結果は、true/false/unknown になります。
下記のようなSQLを考えます。

select * from A where A.id in ( select B.id from B )

in句の評価は下記の通りです。Aの各レコードaに対して、

1. a.id が 集合B.id に含まれる場合、in句はtrue
2. a.id が 集合B.id に含まれない場合、in句はfalse
3. a.id が 集合B.id に含まれるか/まれないか確定しない場合、in句はunknown

なお、a.idはレコードaのidを、B.idは「テーブルBのレコードのidの値」を集めた集合とします。

exists

exists句の評価結果は、true/false になります。
下記のようなSQLを考えます。

select * from A where exists( select * from B where A.id = B.id )

exists句はの評価は下記の通りです。Aの各レコードaに対して、

1. 条件(A.id = B.id)がtrueになるBのレコードが1件以上の場合、exists句はtrue
2. 条件(A.id = B.id)がtrueになるBのレコードが0件の場合、exists句はfalse

この定義により、in句とexists句の差異が生まれます。exists句は、評価がtrueが確定しているレコードの数をカウントします。言い換えれば、評価(true/false)が定まらないunknownなレコードは切り捨てることになります。一方、in句は、unknownはunknownのまま残しておくイメージになります。例を示します。

notが一致しない理由

exists句はin句で書き換えれる場合があります。その際の注意点として、in と exists でヒットするレコードが一致するにも関わらず、not in とnot exists ではヒットするレコードが一致しない場合があります。この違いがどこから来るのでしょう。

その理由は、in句がtrue/false/unknownの３値なのに対し、exists句の評価結果がtrue/falseは２値であることに起因します。下記の例を示します。

例

inとexistsのヒットする結果が同じだったとします。
例えば、テーブルAのレコードa,b,cが下表のように評価され、inもexistsもレコードaがヒットする場合を考えます。

レコード	in評価	exists評価
a	true	true
b	false	false
c	unknown	false

その場合、not in句の評価は下記のようになります。

レコード	in評価	not in 評価
a	true	false
b	false	true
c	unknown	unknown

一方で、not existsは下記のようになります。

レコード	exists評価	not exists評価
a	true	false
b	false	true
c	false	true

このとき、not は一致しません。なぜなら、not inでレコードbが、not exists句ではレコードb,cがヒットするためです。

レコード	not in 評価	not exists評価
a	false	false
b	true	true
c	unknown	true