何故、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テーブル

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

SQL記述例：

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

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

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

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

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

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

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

～(おさらい)WHEREでnullを=で比較するとどうなるんだっけ～

SQLで例えば、[カラム名] = NULLのように比較演算子を利用し、NULLと比較をした場合、
カラムにNULLが入っているレコードがあった場合でも、結果は0件です。

--※１の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例
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は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> 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かもしれませんが、わかりません！！というのが最適解になってしまいます。

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

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

おまけ

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

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件になります。