【序文】
NULL はブラックホール。混ぜるな危険。取扱注意。
【環境】
| SQLServer | SQLCMD |
|---|---|
| 2017 | 14.0.1000.169 |
【前提】
検証用にテーブル作成とかまどろっこしいので、VALUESコンストラクタで代用している。
【検証】
+ 演算子は値に使えば加算、文字列に使えば連結として機能する。
1> SELECT
2> 1 + 2 [1 + 2]
3> , 'AA' + 'BB' [AA + BB]
4> ;
5> GO
1 + 2 AA + BB
----------- -------
3 AABB
(1 行処理されました)
これに NULL が加わると……。
1> SELECT
2> 1 + 2 [1 + 2]
3> , 1 + 2 + NULL [1 + 2 + NULL]
4> , 'AA' + 'BB' [AA + BB]
5> , 'AA' + 'BB' + NULL [AA + BB + NULL]
6> ;
7> GO
1 + 2 1 + 2 + NULL AA + BB AA + BB + NULL
----------- ------------ ------- --------------
3 NULL AABB NULL
(1 行処理されました)
結果は NULL になってしまう。
大抵の組み込み関数でも、引数に NULL が含まれていると同様。
1> SELECT
2> CHARINDEX('A', NULL)
3> , CHARINDEX(NULL, 'AA')
4> , LEFT(NULL, 1)
5> , LEFT('AA', NULL)
6> ;
7> GO
----------- ----------- - --
NULL NULL N NU
(1 行処理されました)
NULL はブラックホールみたいなもので、他の値を吸収してしまうイメージ。
混ぜるな危険。取扱注意。
そもそも、文字列や数値などの「値」と NULL を併用すること自体、無理がある。
∞（無限） + 1 の結果は？ の問題とも似ている。
禅問答みたいな話はさておき、実務上でもこれは落とし穴になる場合がある。
ストアドプロシージャで引数の値を使用（連結）して、動的に文字列を作成する処理はよくある。
この引数に NULL が混入すると、作成した文字列が NULL になってしまったり、思わぬ不具合を引き起こす。要注意。
【毒も薬】
そんな厄介者の NULL の性質にも使いどころはある。
例えば、以下の仕様で売上原価率を求めるとする。
売上原価率 = 売上原価 ÷ 売上高 × 100
式をそのまま組み込めば良いので以下のようなクエリになると思う。
1> SELECT
2> [売上原価]
3> , [売上高]
4> , [売上原価] / [売上高] * 100 [売上原価率]
5> FROM (
6> VALUES(100.0, 200.0)
7> ) t([売上原価], [売上高])
8> ;
9> GO
売上原価 売上高 売上原価率
------ ------ ----------------
100.0 200.0 50.000000
(1 行処理されました)
しかしこのクエリは売上高が 0 の場合、0除算エラーとなってしまう。
1> SELECT
2> [売上原価]
3> , [売上高]
4> , [売上原価] / [売上高] * 100 [売上原価率]
5> FROM (
6> VALUES(100.0, 200.0), (10.0, 0)
7> ) t([売上原価], [売上高])
8> ;
9> GO
売上原価 売上高 売上原価率
------ ------ ----------------
100.0 200.0 50.000000
メッセージ 8134、レベル 16、状態 1、サーバー ***、行 1
0 除算エラーが発生しました。
そこで 売上高が 0 の場合を CASE式で切り分けたりする。
1> SELECT
2> [売上原価]
3> , [売上高]
4> , CASE WHEN [売上高] <> 0 THEN [売上原価] / [売上高] * 100 END
5> [売上原価率]
6> FROM (
7> VALUES(100.0, 200.0), (10.0, 0)
8> ) t([売上原価], [売上高])
9> ;
10> GO
売上原価 売上高 売上原価率
------ ------ ----------------
100.0 200.0 50.000000
10.0 .0 NULL
(2 行処理されました)
しかし、NULLIF関数を使えば切り分ける必要はない。
1> SELECT
2> [売上原価]
3> , [売上高]
4> , [売上原価] / NULLIF([売上高], 0) * 100
5> FROM (
6> VALUES(100.0, 200.0), (10.0, 0)
7> ) t([売上原価], [売上高])
8> ;
9> GO
売上原価 売上高
------ ------ ----------------
100.0 200.0 50.000000
10.0 .0 NULL
(2 行処理されました)
NULLIF関数を通すことにより売上高が 0 の場合は NULL に差し替える、という考え方。式に NULL が含まれれば結果も NULL になるし、0 除算エラーも回避できる。
【その他 NULL の応用】
WHERE [a] IS NULL
OR [b] IS NULL
OR [c] IS NULL
;
上記クエリ（WHERE文）は以下のように書くことができる。使える局面は限られているだろうけど。
WHERE [a] + [b] + [c] IS NULL -- [a] ～ [c] のいずれかが NULL
;
ストアドプロシージャの引数の判定なら使いどころはあるかも。
IF @a + @b + @c IS NULL -- @a ～ @c のいずれかが NULL の場合
【余談】
COUNT(NULL) はエラーとなる。
1> SELECT COUNT(NULL);
2> GO
メッセージ 8117、レベル 16、状態 1、サーバー ***、行 1
オペランドのデータ型 NULL は count 演算子では無効です。
それが仕様に反することは理解できるが、以下はエラーにならない。謎である。
1> SELECT COUNT(*);
2> GO
-----------
1
(1 行処理されました)
1> SELECT COUNT(1);
2> GO
-----------
1
(1 行処理されました)
FROM句を指定してもいないのに、一体何の件数を返しているんだろう。
私の読解力が低いのか、公式ヘルプからこの挙動の説明となる情報は読み取れなかった。
ご存じの方がいたら教えて欲しい。