何故、「where x = null」,「x <> null」は０件になるのか

#何故、NULL は = で比較したらあかんのか
新人さんたちは、不思議な値「NULL」の概念が上手く理解できず、よく躓いてしまいます。
特に、教えていてアプローチに悩むのが、SQLの「IS NULL」という比較構文です。
私も、理解していない点が多くあったので、わからない点を纏めてみました。

やさしい上級者の方は、恐れ入りますが間違いがあったらご指摘頂けますと幸いです…

##経緯
教育を行っていた際、"where x = null"と記述した際、0件なのであれば、
逆となる条件の"where x <> null"はなぜ全件じゃないのか？と聞かれたことが発端でした。

そこで、3値論理というワードを全く知らなかった自分のため、
また、NULLとは何ぞやといった点をわかりやすく教えるため、記述しました。

##～(おさらい)本題に行く前に、WHEREってなんだっけ～
SQLの概念と、SELECT,FROM,WHEREの概念を簡単に理解している前提で書きます。

WHEREはDBに保有しているデータに、条件と一致するデータか～と質問する為の構文です。

「あなたは15歳以上ですか？」と10人から確認したい場合、
10人に質問しなければいけませんよね。

DBも同じで、10行(レコード)あった場合、1件づつ、合計10回問いかけを行っています。
いくつか、想定動作と結果をイメージしながら考えてみましょう。

hogeテーブル

id	name	age
1001	たろう	10
1002	じろう	15
1003	NULL	20

例えば、hogeテーブルから、年齢が15歳以上のデータ（レコード）を取得したいとします。

SQL記述例：

SQL*Plus

--※１のSQL例
SQL> SELECT *
  1> FROM hoge
  2> WHERE age >= 15;

上記のSQLを記述した場合、各行で下記のような計算が行われます。

id	name	age	計算条件	結果
1001	たろう	10	10は15以上？	偽
1002	じろう	15	15は15以上？	真
1003	NULL	20	20は15以上？	真

DBは真の計算結果となるものを返すので、
1002,1003の2件を取得します。

また、新人さんが混乱しやすい問題として、このようなSQLがあります。

SQL*Plus

--※１のSQL例
SQL> SELECT *
  1> FROM hoge
  2> WHERE 1 = 1;

よく、1=1ってなんだ？って身構えられるのですが、
単純に「1と1」を全レコードで比較しているだけです。

id	name	age	計算条件	結果
1001	たろう	10	1は1?	真
1002	じろう	15	1は1?	真
1003	NULL	20	1は1?	真

この場合、1001,1002,1003の3件が返却されます。

###～(おさらい)WHEREでnullを=で比較するとどうなるんだっけ～
SQLで例えば、[カラム名] = NULLのように比較演算子を利用し、NULLと比較をした場合、
カラムにNULLが入っているレコードがあった場合でも、結果は0件です。

SQL*Plus

--※１のSQL例
SQL> SELECT *
  1> FROM hoge
  2> WHERE name is NULL;

ID   NAME
---- -----
1003 NULL

--※２のSQL例
SQL> SELECT * FROM hoge
  2> WHERE name = NULL;

レコードが選択されませんでした。

また、BETWEENを利用した場合についても、同様の動作をします。

SQL*Plus

--※SQL例
SQL> SELECT * FROM hoge
  2> WHERE name BETWEEN 0 and NULL;

--ANDに置き換えて記述した場合(上記と同等のSQL)
SQL> SELECT * FROM hoge
  2> WHERE name >= 0 and name <=NULL;

レコードが選択されませんでした。

(NOTをつけない)INの場合、ORでの探索条件となるので、
NULL値は出ませんが、他の比較値(下記例だと1001のこと)は正しく取得出来ます。

SQL*Plus

--このSQLは1003のNULLさんは対象外となる
SQL> SELECT * FROM hoge
  2> WHERE name IN (NULL,1001);

ID   NAME
---- -----
1003 NULL

--INで記載されている内容と同等のSQL
SQL> SELECT * FROM hoge
  2> WHERE name = NULL -- NULL値を=で比較するため、こちらの比較結果は0件
  3> OR name = "たろう"; -- 「たろう」に合致するレコードはあるので、1件返却される

ID   NAME
---- -----
1001 たろう

##問題提起(なんで「カラム名 <> null」は0件なのか)
新人さんが、この結果はおかしい、バグってる！と騒いでいました。

SQL*Plus

SQL> SELECT * FROM hoge
  2> WHERE NOT(name = NULL);

レコードが選択されませんでした。

主張としては、[カラム名] = NULLが0件なのであれば、
その否定は全件なのではないかといったお話でした。

私もこの主張は正しいな…と感じてしまったので調べました。
どうやら、3値論理という概念が深く関係しており、まず3値論理を理解しなければわからないようです。

###3値論理とは
3値論理とは真偽値をTRUE,FALSEの2つに加えて、UNKNOWN(不明)という3つの値で管理する方法です。¹

ここでいう、UNKNOWNとはどういったデータかというと、おおまかに下記の2種類に分類されます。

①「家の総移動距離」 や 「モグラの飛行時間」のような、論理的に存在できない値
※ハウルの動く城とか、タイヤの付いた家、飛ぶモグラ等の
　前提になると話がすごくややこしくなるので、一般的な概念で考えて下さい…

②「タモリの目の色」 や 「星の数」 や 「一部アイドルの実年齢」のような、わからない（観測できない）値
※こちらも、「タモリ サングラス 外す」等で検索すると画像が出てきますが、²
　そんな画像はない前提でお願いします…

NULL値は、TRUEでもなく、FALSEでもなく、このUNKNOWNという第3値に分類されます。

###UNKNOWNを比較してみよう
上記が理解できると、なんとなく分かる人もいるかもしれませんが、

WHERE 星の数 = 100000000000000のような荒唐無稽な質問は
もしかしたらTRUEかもしれませんし、FALSEかもしれませんが、**わかりません！！**というのが最適解になってしまいます。

星の数	計算条件	結果
NULL	null は100000000000000?	UNKNOWN

したがって、結果がTRUEではないため、返却されません。
逆にWHERE not(星の数 = 100000000000000)であってもこの結果はUNKNOWNです。

星の数	計算条件	結果
NULL	null は100000000000000ではない？	UNKNOWN

だってわからないんですもん。

##おまけ
少し応用的な話になってしまいますが、NOT INの挙動を聞くと、
1回目は大体まちがって認識されているのですが、上記らへんを押さえていれば、実は簡単に解けます。

SQL*Plus

SQL> SELECT * FROM hoge
  2> WHERE name NOT IN(NULL,1001);

--同等のSQL
SQL> SELECT * FROM hoge
  2> WHERE name <> NULL 
  3> AND name <> 1001;

not in はその名の通り、羅列した要素のいずれとも合致していないという条件のため、
AND と<>で構成されることになります。

このとき、<> NULLは0件で、AND演算なので、必ず0件になります。

1. https://codezine.jp/article/detail/532 ↩

2. タモリの目の色は何色なのか、画像から解析できませんでした。 ↩