データアナリストがよく出会うnullについてまとめてみた。

## はじめに
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`が混じったサンプルデータを使います。


```sql:with句で書いたサンプルデータ
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
)
```
<br>
スカラー演算とはなにか、といえばいわゆる普通の計算をイメージすればよいです。サンプルデータのvar_1とvar_2をつかってスカラー演算してみましょう。

```sql:スカラー演算の実験
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`を除いて計算されます（アウトプットはコメントアウトで示し、表は省略します）。

```sql:集約関数の実験
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値理論というものになります。

```sql: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
```
<br>
このクエリの結果は次のとおりです（＝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`をはじく）クエリを書くとします。
```sql
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`に振り回されることなく、データ分析の精度をあげていきたいですね！