何故、「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: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: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: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: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: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:SQL*Plus

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

レコードが選択されませんでした。
```
主張としては、[カラム名] = NULLが0件なのであれば、
その否定は全件なのではないかといったお話でした。
![730d57244fcbf5325b5ee094b3c78eea.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/326021/6e27b43f-aae2-2a27-757c-9ef4a1e3ef6b.png)

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

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

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

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

**②「タモリの目の色」 や 「星の数」 や 「一部アイドルの実年齢」のような、わからない（観測できない）値**
※こちらも、「タモリ サングラス 外す」等で検索すると画像が出てきますが、[^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: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]: タモリの目の色は何色なのか、画像から解析できませんでした。