【序文】

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

【環境】

SQLServer	SSMS
2017	v18.2

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

【前提】

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

【否定は肯定の補集合ではない】

以下のようなテーブルがあるとする。

性別
'男'
'女'
''
...

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

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

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

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

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

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

以下をイメージし、消去法で残りは 10 - 5 = 5 と思うかもしれない。

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

【検証1】

以下クエリ実行。

;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 [性別] <> '男')    [性別<>'男']
;

実行結果は以下。

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

【検証2】

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

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

実行結果は以下。

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

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

【NULL の正体】

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

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

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

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

【検証3】

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

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

実行結果は以下。

美しい。マトリックス的にやっと埋まった。
前述のベン図も正しくは以下ということになる。

【検証4】

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

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

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

;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 [性別] <> '男')   [性別<>'男']
;

実行結果は以下。

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

【NULL の罠の回避】

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

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

実際、業務システムでのクエリの実装は ISNULL()関数や IS NULL だらけだったりする。
しかし、それもそれでどうだろうって話。
テーブル定義には、NOT NULL制約というものがある。
ALTER TABLE (Transact-SQL)

NULL | NOT NULL
列に null 値を使用できるかどうかを指定します。

または列の規定値に '' を指定する方法もある。
列の既定値の指定

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

【余談1】

DB やプログラミング言語を学び、初めて「null」という単語を知った人は、それが専門用語と勘違いしている人がいるが、これは一般名詞。
nullの意味 - goo辞書英和和英

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

【余談2】

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

ところが困ったことに本丸である SQLServer公式サイトでも「NULL値」という表現が散見される。
ISNULL (Transact-SQL)

NULL 値を、指定された値に置き換えます。

NULL は値ではないとあれほど……。ところが本家を確認すると以下。
ISNULL (Transact-SQL)

Replaces NULL with the specified replacement value.

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

【余談3】

NULL を = <>比較演算子で判定できるかは、SET ANSI_NULLS設定で変化する。
SET ANSI_NULLS (Transact-SQL)

SQL Server 2017 で = (等号) 比較演算子と <> (不等号) 比較演算子を NULL 値に対して使用した場合の ISO 準拠動作を指定します。

しかし

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

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