NULL と Unknown を分かり易く説明すると
はじめに
SQLの勉強を始めると NULL と Unknown を理解するのが難しい面があります。そこで、自然に解釈できる説明を試みます。
コアイメージ
一言で表現すると、
- unknownは、true/false のどちらになるか未確定な状態
- NULLは、カラムに値が未入力の状態。
になります。こちらのイメージを元に、関連する定義を振り返ってみましょう。
論理演算(not/and/or)の評価
最初に、unknownを含む場合の論理演算の評価について説明します。一般的な論理は2値論理(true/false)ですが、SQLでは3値論理(ture/false/unknown)が使われます。
3値論理における 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について2ケースを見てきました。andの残りの3ケースや not/or についても、コアイメージをもとに説明すれば、同じように自然な解釈ができます。
条件文の評価
NULLのコアイメージは、カラムに値がまだ入力されていない状態です。条件文がNULLとなるカラムを含む場合、値が入力されるまで、条件文の評価(true/false)は未確定な状態です。その場合、unknownと評価されます。例1を参照ください。
例1
条件文 col1 == col2 を考えます。ただし、col1 = 'aaa' で col2 = NULL とします。この場合、col2 に'aaa'が入力されれば、条件文はtrueですが、それ以外が入力されれば、falseです。条件文に未入力な項目がある場合は、評価(true/false)を確定できないため、unknownになります。
ただし、条件文にNULLが含まれる場合でも、評価が確定する場合があります。例2を参照ください。
例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 条件文
このとき、下記のルールでレコードがヒットします。
- 条件文がtrueと評価されたレコードはヒット。
- 条件文がfalseと評価されたレコードはヒットしない。
- 条件文が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に対して、
- a.id が 集合B.id に含まれる場合、in句はtrue
- a.id が 集合B.id に含まれない場合、in句はfalse
- 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に対して、
- 条件(A.id = B.id)がtrueになるBのレコードが1件以上の場合、exists句はtrue
- 条件(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の3値なのに対し、exists句の評価結果がtrue/falseは2値であることに起因します。下記の例を示します。
例
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 |
Author And Source
この問題について(NULL と Unknown を分かり易く説明すると), 我々は、より多くの情報をここで見つけました https://qiita.com/nby/items/4f91abe6f8d53cd24e99著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .