NULL の罠 ～前編～

## 【序文】
NULL には落とし穴がいっぱい。直観と反する動作をする。
初学者が陥りやすい部分に焦点を当てる。

## 【環境】

| SQLServer | SSMS |
| ---- | ---- |
| 2017 | v18.2 |

※ 但し、基本的な原理の解説なのでバージョンには依存しない筈

## 【前提】
検証用にテーブル作成とかまどろっこしいので、VALUESコンストラクタや CTE（共通テーブル式）で代用している。
従って、検証用クエリは原則単体で動作する。
実際に動かしたり書き換えてみたりして試して欲しい。


## 【否定は肯定の補集合ではない】
以下のようなテーブルがあるとする。

| 性別 |
| ---- |
| '男' |
| '女' |
| '' |
| ... |

以下クエリで全件数を確認したら 10 件だったとする。

	SELECT COUNT(*) FROM <テーブル名>;

次に以下クエリを実行したら、[性別] が '男' のレコードが 5 件だったとする。

	SELECT COUNT(*) FROM <テーブル名> WHERE [性別] = '男';

それでは、以下クエリを実行したら、[性別] が '男' 以外のレコードは何件か？

	SELECT COUNT(*) FROM <テーブル名> WHERE [性別] <> '男';

以下をイメージし、消去法で残りは 10 - 5 = 5 と思うかもしれない。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254888/30d24ee9-93a6-e0c3-6896-c5633d78387f.png" width=30%>

それは正しい場合と正しくない場合がある。
正しくない場合とは、NULL のレコードが存在する場合。

## 【検証1】
以下クエリ実行。

```SQL
;WITH [CTE_Base] AS (
    SELECT * FROM (
        VALUES('男'), ('男'), ('男'), ('男'), ('男'), ('女'), ('女'), ('女'), (''), (NULL)
    )t([性別])
)
SELECT
      (SELECT COUNT(*) FROM [CTE_Base])                         [COUNT(*)]
    , (SELECT COUNT(*) FROM [CTE_Base] WHERE [性別] =  '男')    [性別='男']
    , (SELECT COUNT(*) FROM [CTE_Base] WHERE [性別] <> '男')    [性別<>'男']
;
```

実行結果は以下。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254888/025b928d-39d7-911a-9612-153b5e203091.png" width=20%>

10 - 5 が 4？
と、わざとらしい感じになってしまったけど、原因は NULL。

## 【検証2】
`` [性別] = '男' `` `` [性別] <> '男' `` がどのレコードに作用するのか確認する為に以下のクエリ実行。
条件に該当すれば「〇」、非該当は「×」が表示される。

```SQL
;WITH [CTE_Base] AS (
    SELECT * FROM (
        VALUES('男'), ('女'), (''), (NULL)
    )t([性別])
)
SELECT
      [性別]
    , IIF([性別] =  '男', '〇', '×')   [性別='男']
    , IIF([性別] <> '男', '〇', '×')   [性別<>'男']
FROM [CTE_Base]
;
```

実行結果は以下。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254888/9493fdf9-5fab-fbcd-1939-1bef36aaae00.png" width=20%>

`` [性別] = '男' `` は問題ないだろう。``'男'`` のみ該当、それ以外は該当しない。
当然の結果。

問題は `` [性別] <> '男' ``。
``'男'`` は非該当。
``'女'`` は該当。
``''`` も該当。
しかし、 ``NULL`` は非該当？
``NULL`` は ``'男'`` とは一致しないんじゃないの？
これは直観に反する動作だと思う。

## 【NULL の正体】
何故こんな動作になるのか。
それは、`` = `` や `` <> `` は「値」を判定する為に用意されている演算子だから。
``NULL`` は「値」ではないので判定できない、条件式として「真」を返さないから。

では ``NULL`` とは何か？
それは「状態」。値そのものが格納されていない状態を表している。

そもそも、`` [性別] = '男' `` で、``'女'``、``''`` が該当しない理由と、``NULL`` が該当しない理由は異なる。
``'女'``、``''`` が該当しないのは異なる値だから。
``NULL`` が該当しないのは、判定できないから。
しかし、WHERE句で `` [性別] = '男' `` を指定する場合、結果として ``'男'`` だけ抽出できれば良いので、該当しないレコードの内訳など意識する必要はなく、上記のような認識はなくても実質問題にはならない。
 `` [性別] <> '男' `` で問題が表面化する。

しかし、実際に業務でクエリを書く場合、`` <> `` より `` = `` での条件式を書くケースが多いので、初学者の場合、この問題に気付きにくい。

## 【検証3】
では ``NULL`` を判定したい場合はどうするか？
その為に用意されている予約語が `` IS NULL ``。
【検証2】のクエリに ``IS NULL`` を追加し実行。

```SQL
;WITH [CTE_Base] AS (
    SELECT * FROM (
        VALUES('男'), ('女'), (''), (NULL)
    )t([性別])
)
SELECT
      [性別]
    , IIF([性別] =  '男', '〇', '×')   [性別='男']
    , IIF([性別] <> '男', '〇', '×')   [性別<>'男']
    , IIF([性別] IS NULL, '〇', '×')   [性別 IS NULL]
FROM [CTE_Base]
;
```

実行結果は以下。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254888/d27c7517-0378-d757-f0ef-c0dd36980e0c.png" width=20%>
美しい。マトリックス的にやっと埋まった。
前述のベン図も正しくは以下ということになる。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254888/cede8d93-5b78-5a49-d806-8dce6a75fce6.png" width=20%>

## 【検証4】
COUNT関数の引数は ``*`` を指定するものと思い込んでいる人がいるけど、列名を指定することもできるし、寧ろその方が高速とされている。

```SQL
SELECT COUNT([列名]) FROM <テーブル名>
```

``SELECT * FROM <テーブル名>`` より ``SELECT [列名1], [列名2], ... FROM <テーブル名>`` と明示的に列名を指定した方が速いという話と同じ。
【検証1】のクエリの COUNT関数で列名を指定する形に修正し、再実行。

```SQL
;WITH [CTE_Base] AS (
    SELECT * FROM (
        VALUES('男'), ('男'), ('男'), ('男'), ('男'), ('女'), ('女'), ('女'), (''), (NULL)
    )t([性別])
)
SELECT
      (SELECT COUNT([性別]) FROM [CTE_Base])                        [COUNT(*)]
    , (SELECT COUNT([性別]) FROM [CTE_Base] WHERE [性別] =  '男')   [性別='男']
    , (SELECT COUNT([性別]) FROM [CTE_Base] WHERE [性別] <> '男')   [性別<>'男']
;
```

実行結果は以下。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254888/ca9f2d72-4ac2-5583-7ba5-e57585ff35db.png" width=20%>

おや、結果が変わったね。
そう、``COUNT([列名])`` の場合、指定した列が　``NULL`` の場合、カウントされない仕様。要注意。
しかし、9 = 5 + 4 で辻褄は合うんだよなぁ。これはこれで。

## 【NULL の罠の回避】
NULL は色々とややこしい。それは「仕様」なので仕方がないが、実際の業務システム上ではどうなんだろう。
``NULL`` と ``''`` は文字列が未入力（未設定）という意味で考えれば実質同じ。
同様に扱いたい。という場合は以下のような実装となる。

```SQL
WHERE ISNULL([列名], '') = ''           -- NULL の場合は '' に置き換え判定
WHERE ([列名] = '' OR [列名] IS NULL)   -- '' または NULL と判定
```

実際、業務システムでのクエリの実装は ``ISNULL()``関数や ``IS NULL`` だらけだったりする。
しかし、それもそれでどうだろうって話。
テーブル定義には、NOT NULL制約というものがある。
[ALTER TABLE (Transact-SQL)](https://docs.microsoft.com/ja-jp/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2017 "ALTER TABLE (Transact-SQL)")
> NULL | NOT NULL
> 列に null 値を使用できるかどうかを指定します。

または列の規定値に '' を指定する方法もある。
[列の既定値の指定](https://docs.microsoft.com/ja-jp/sql/relational-databases/tables/specify-default-values-for-columns?view=sql-server-2017 "列の既定値の指定")

とにかく最初から、NULL が入らないよう定義しておけば、NULL が入っている場合は～と悩まなくて済む。
DB、テーブルは単なる入れ物という程度の認識しかない人が多いけど、本来はこういったことを考慮し、テーブルを設計（定義）するもの。

## 【余談1】
DB や プログラミング言語を学び、初めて「null」という単語を知った人は、それが専門用語と勘違いしている人がいるが、これは一般名詞。
[nullの意味 - goo辞書 英和和英](https://dictionary.goo.ne.jp/word/en/null/#ej-58015 "nullの意味 - goo辞書 英和和英")

存在しない、空、といった意味。
日本人にも馴染みがある単語なら empty や nothing が近いか。
そうした単語本来の意味を知っていれば、DB での ``NULL`` の理解も深まる。

## 【余談2】
「NULL値」という表現を時々見かけるが、厳密には間違い。
前述の通り、NULL は「値」ではないから。
※ 少なくとも DB の世界では。プログラミング用語での null はまた意味が異なり、話が長くなるのでここでは言及しない

ところが困ったことに本丸である SQLServer公式サイトでも「NULL値」という表現が散見される。
[ISNULL (Transact-SQL)](https://docs.microsoft.com/ja-jp/sql/t-sql/functions/isnull-transact-sql?view=sql-server-2017 "ISNULL (Transact-SQL)")
> NULL 値を、指定された値に置き換えます。

NULL は値ではないとあれほど……。ところが本家を確認すると以下。
[ISNULL (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-2017 "ISNULL (Transact-SQL)")
> Replaces NULL with the specified replacement value.

単に「NULL」としか書かれていないし。
日本語訳がダメってことか。

## 【余談3】
NULL を `=` `<>`比較演算子で判定できるかは、`SET ANSI_NULLS`設定で変化する。
[SET ANSI_NULLS (Transact-SQL)](https://docs.microsoft.com/ja-jp/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-2017 "SET ANSI_NULLS (Transact-SQL)")
> SQL Server 2017 で = (等号) 比較演算子と <> (不等号) 比較演算子を NULL 値に対して使用した場合の ISO 準拠動作を指定します。

しかし
> SQL Server の今後のバージョンでは、ANSI_NULLS が ON になり、このオプションを明示的に OFF に設定するすべてのアプリケーションでエラーが発生します。 新規の開発作業ではこの機能を使用しないようにし、現在この機能を使用しているアプリケーションは修正することを検討してください。

ともある。
原則、`=` `<>` で NULL は判定できないという考え方で良いだろ。