はじめに
SinkCapitalの田中です。ここ最近クエリをかいてデータ抽出する機会がかなり増えましたが、それと同時に、nullについて「あれ」っとすることも多くなり、調べてまとめました。
nullは 不思議な挙動をするように感じられますが、基本となる考え方さえ押さえておけば、しっかりとした筋が通っていることがわかりました。
アドベントカレンダーをいい機会として一旦整理してみましたので、既知かもしれませんが書きます。
nullの意味
nullの読み方はヌル？ナル？
これはどっちもありますがヌルの方が多いですね。
僕は「幽☆遊☆白書」のボスキャラの女性人格の名前と中学生の頃の初恋の相手に敬意を表し、ナルと呼ぶことが多いです。
3種類の意味がある
『リレーショナルデータベース入門』（増永良文著）にでは、nullの意味が3種類に分けられることを示しています（本当はもっと細分化できるようなのですが、実務上この3種としてシンプルに考えられるみたいです）。それは
- dne (= does not exist)
- unk (= unknown)
- ni (= no-information)
です。これらは、情報量の多さの順番と一致しており、それぞれ、
- 値が存在しないことがわかっている（例：独身社員の配偶者欄を
nullにする） - 値は存在するが、具体的には不明なので入れられない（例：既婚社員の配偶者名がまだ不明なので、配偶者欄を
nullにする） - 値が存在するかしないのかもわからない（例：社員が既婚なのか未婚なのかわからないので、配偶者欄を
nullにする）
ことを意味します。nullは総じて、値をいれられない時に使われます。
したがって、ゼロとも空文字とも違う、ある種の特別なデータと言えます。
nullの登場シーン
nullが登場するシーンは大体以下の場合ではないでしょうか。
クエリの場合
-
left outer join…お馴染み外部結合で、相手方にキーが存在しない場合は、もう一方のテーブルのカラムはnullになります。 -
case when…条件一致しない場合（when以下がtrueにならずelseがない場合）はnullが返されます。else nullという記述も有効ですが、個人的には冗長なきがするのでelseは省くことが多いです。
データ基盤
データ基盤に至ってはあらゆるところに法則性なくnullが潜んでいます。たとえばゼロの代用として利用されるのはもちろんですが、データウェアハウスのテーブルには数字がはいっているのに、それを参照しているはずのマートのテーブルにはnullが入っていることもありますね。これは、かなり厄介と感じることでしょう。非常に気を遣います。
nullの演算
そもそもnullに注意すべき理由の最たるものが、演算の特殊性といえます。演算については、nullが絡むと少し厄介です。ここを理解していないと、思わぬアウトプトをだしてしまいます。クエリを書く人であれば誰もが通ったorこれから通る道なのではないでしょうか。
スカラー演算
実験してみるために、nullが混じったサンプルデータを使います。
with
test as (
select 160 as var_1, 61 as var_2 union all
select 175 as var_1, 73 as var_2 union all
select 181 as var_1, 74 as var_2 union all
select null as var_1, 58 as var_2 union all
select 154 as var_1, null as var_2
)
スカラー演算とはなにか、といえばいわゆる普通の計算をイメージすればよいです。サンプルデータのvar_1とvar_2をつかってスカラー演算してみましょう。
select
var_1
, var_2
, var_1 + var_2 as proc_add -- 四則演算の足し算
, var_1 * var_2 as proc_multiple -- 四則演算の掛け算
, mod(var_1, var_2) as proc_mod -- 剰余演算（割り算した余り）
, concat(var_1 , '/', var_2) as proc_concat -- 文字を連結
from
test
このクエリの結果は以下のとおりです。
| var_1 | var_2 | proc_add | proc_multiple | proc_mod | proc_concat |
|---|---|---|---|---|---|
| 160 | 61 | 221 | 9760 | 38 | 160/61 |
| 175 | 73 | 248 | 12775 | 29 | 175/73 |
| 181 | 74 | 255 | 13394 | 33 | 181/74 |
| null | 58 | null | null | null | null |
| 154 | null | null | null | null | null |
僕はnullのことを"ミイラ"だと思うようにしています。"ミイラ取りがミイラになる"というイメージで、nullが引数に入ってくると結果はnullになってしまいます。
ただし、sumやavgのような集約関数では、一部にnullがあったとしても結果はnullにはなりません。以下のように、nullを除いて計算されます（アウトプットはコメントアウトで示し、表は省略します）。
select
count(1) as cnt_const /* 行数である5 */
, count(*) as cnt_asta /* 行数である5 */
, count(var_1) as cnt_var /* 値の数である4（nullを除外） */
, max(var_1) as max_var /* 値のみの最大（nullを除外） */
, min(var_1) as min_var /* 値のみの最小（nullを除外） */
, sum(var_1) as sum_var /* 値のみの合計（nullを除外） */
, avg(var_1) as avg_var /* 値のみの平均（nullを除外） */
, stddev(var_1) as stddev_var /* 値のみの標準偏差（nullを除外） */
from
test
論理演算
nullをつかったANDやOR（いわゆる"かつ"と"または"）の論理演算に関しては、最初に学ぶであろう真・偽の論理演算（{真・偽}{真・偽}の順列で表現される4種類にたいして論理演算子ごとにアウトプットが定まる）の概念が拡張され、真・偽にnullを加えた3値理論というものになります。
with
test_2 as (
select true as bool_1, true as bool_2 union all
select true as bool_1, false as bool_2 union all
select false as bool_1, false as bool_2 union all
select null as bool_1, true as bool_2 union all
select null as bool_1, false as bool_2 union all
select null as bool_1, null as bool_2
)
select
bool_1, bool_2, bool_1 and bool_2 as logic_and, bool_1 or bool_2 as logic_or
from
test_2
このクエリの結果は次のとおりです（＝3値理論の真理値表）。
| bool_1 | bool_2 | logic_and | logic_or |
|---|---|---|---|
| true | true | true | true |
| true | false | false | true |
| false | false | false | false |
| null | true | null | true |
| null | false | false | null |
| null | null | null | null |
1から3行目は普通の真・偽の演算です。4行目以降はnullの演算ですが、注目すべきは4行目のlogic_orと5行目のlogic_andです。
スカラー演算ではnullが絡むとその時点でnullになりましたが、この二か所はtrueとfalseになりました。
解説
慣れないうちは「なんでこんな変な結果になるんだ」と思うかもしれませんが、nullの根本的な意味を考えれば、ある意味合理的であることに気づきます。nullの根本的な意味とは、最初に示した「存在しない」、「存在するが具体的な値はわからない」、「存在するかしないかがわからない」です。nullは、ゼロや空文字、false（偽）とは根本的に異なり、値がないことを示します。
したがって、足し算や引き算などの四則演算をふくめ、スカラー演算は、結果が定まらないのでnullになります。10という特定の数字に"わからない"数字を足したり引いたりしたら、結果は"わからない"はずなのです。文字列の結合も同様です。
nullを引数に含む場合の集約関数（sumやcountなど）がなぜnullにならないのかという厳密な理由は分かりませんでしたが、実務上この仕様はかなり都合がよいので深く立ち入らないことにします。
論理演算では、nullはtrueかfalseかがわからないと言うことを示し、それをベースとして3値理論が定義されます。AND演算については、片方がfalseであった時点でもう片方は関係ないため、null and falseはfalseになりますし、OR演算については、片方がtrueの時点でもう片方がtrueであろうがfalseであろうが関係ないため、null or trueはtrueになります。それ以外は、結果がtrueかfalseかはさだまらず揺らいでいる状態のため、nullとなります。
null用の文法
nullは、'null'という文字列や''という空文字とは違うため、SQLにはnullに特化した文法があります。
-
<COLUMN> is null
その列がnullだとtrueを返してくれます。その逆は<COLUMN> is not nullです。 -
coalesce(a, b[,...])
nullではない最初の引数を返してくれます。nullを'-'という文字列にしたいときなどに活躍し、使い勝手がかなりよくいつもお世話になっています。
nullの予期せぬ挙動
col_1という列がnullではない行を拾う（＝nullをはじく）クエリを書くとします。
select
*
from
table
where
col_1 <> 'null'
これはnull用の文法を無視した例ですが（nullは文字列’null’ではない）、正確にnullではない行をひろってくれます。nullの比較演算は基本的にnullになります（大きさがわからないnullがあると比較結果がわからないから）が、それはすなわち、trueではないということです。whereにより、そのnull行は正しくはじかれます。ですが、これは偶然ですのですぐに不正確なデータ抽出に直面することになるはずです。
nullとうまく付き合う方法
過度に気にする必要はないかもしれませんが、原則は以下の2点につきます。それは、is nullやcoalesceといったnull用の文法を正しく用いることと、"nullをその意味に照らしてみて利用が適切なシーンで使う"です。
条件合致がしているか否かのフラグを立てるときに 1 / null ではなく、丁寧に1 / 0にすべきです（case when ~ then 1 endというようにサボるのではなく、case when ~ then 1 when ~ then 0 endとする）。なぜならnullは、dne, unk, niの意味だからです。条件に一致していることがわかっているのなら1（あるいはtrueなど）として、一致していないことがわかっているのなら0（あるいはfalseなど）とすべきです。
またある顧客の収益が0円だということがわかっているようなシーンでも、同様にその顧客の収益はnullではなく0にすべきです。
nullに振り回されることなく、データ分析の精度をあげていきたいですね！