今すぐ使えるSQLのNULLとunknownの考え方

# 初めに
こんにちは。
株式会社HRBrainでバックエンドエンジニアインターンをしている、蔭山といいます。

株式会社HRBrainではアドベントカレンダーに参加しています。

https://qiita.com/advent-calendar/2022/hrbrain/

本記事は4日目になります。

業務でDBデータを取り扱う機会もあり、改めてDBとSQLに関して学び直しました。
今回は、その中からすぐにプログラマとして業務に活かせて、とくに気をつけたほうが良いと感じたSQLでのNULLの取り扱いについて書きたいと思います。
何番煎じか分かりませんが、順を追って説明します。

# SQLにおけるNULLとは何か
> 行のある列の値がない場合、その列はNULLである、またはNULLを含むといいます。NOT NULL整合性制約またはPRIMARY KEY整合性制約によって制限されていない列の場合は、どのデータ型の列でもNULLを含むことができます。実際のデータ値が不定または値に意味がない場合に、NULLを使用してください。
参考：[SQL言語リファレンス](https://docs.oracle.com/cd/E82638_01/sqlrf/Nulls.html#GUID-B0BA4751-9D88-426A-84AD-BCDBD5584071)

レコードに値が含まれていない状態をNULLであると言います。
通常NOT NULL制約等をしない限り、どこにでもNULLは存在します。
NULLがあってもデータベースとしてはおかしくないのですが、比較条件といった真理値を扱う際には気をつけないといけません。

その理由を説明する前に、先にSQLでの真理値型について説明します。
通常、どの言語にも真理値型がありますが、SQLの真理値型の特徴として3値論理であることが挙げられます。
3値論理とは**true**, **false**に加えて、**unknown**で表されるものです。
## 真理値表でのunknownの取り扱い
trueとfalseの2つであれば以下のようになります。
![真理値表.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605179/8ba3dc71-3ce8-7aa1-ea3e-839e3022d800.png)
**unknown**が含まれると以下のようになります。
![真理値表(unknown含む).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605179/8d24bb1c-34fc-b855-892b-727f4fb7f557.png)


このように、**unknown**は**trueかfalseかどちらか分からない状態である**と言えます。
3値論理の倫理条件については以下のサイトが詳しく解説してくださっています。

https://docs.oracle.com/cd/F19136_01/sqlrf/Logical-Conditions.html#GUID-C5E48AF2-3FF9-401D-A104-CDB5FC19E65F

## 比較条件でのNULLとunknownの関係
また、比較条件でのNULLと**unknown**の関係も見ておきましょう。何気なく使っている`IS NULL`と`=`の違いも大切です。
|    比較条件   | Aの値 |   結果  |  比較条件 | Aの値 |   結果  |
|:-------------:|:-----:|:-------:|:---------:|:-----:|:-------:|
|   A IS NULL   |   10  |  F  |  A = NULL |   10  | unknown |
| A IS NOT NULL |   10  |   T  | A <> NULL |   10  | unknown |
|   A IS NULL   |  NULL |   T  |  A = NULL |  NULL | unknown |
| A IS NOT NULL |  NULL |  F  | A <> NULL |  NULL | unknown |
|     A = 10    |  NULL | unknown |  A <> 10  |  NULL | unknown |

参考：[SQL言語リファレンス](https://docs.oracle.com/cd/E82638_01/sqlrf/Nulls.html#GUID-C785FE74-5F9C-4F70-AC4B-CA5D3010162A)

しっかりと`IS NULL`等を適切に使用しないと、すぐ`unknown`になってしまうことがわかります。
# 思わぬ挙動になってしまうSQL
ここまでで、SQLの3値論理と**unknown**の振る舞いを見てきました。
ここからは具体例を交えながら、実際にNULLが含まれることでどのような挙動になるのかを説明します。

使用した環境は以下になります。
- PostgreSQL 14


PostgreSQLでは、**unknown**の代わりにNULLが使用されます。以降はそれに従い、**unknown**をNULLと置き換えます。
## 集合関数 NOT IN と NOT EXIST
ある集合の中に含まれているかどうかを判断したい場合は`IN`を使うと思います。たとえば、以下のようなお店ごとに商品と売れた日付を管理するテーブルがあるとします。
![店舗商品テーブル.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605179/38839690-4eb0-2923-46ff-abc2faff5552.png)



B店で9月以降に売れた商品のA店での売れた日付を表示させるSQLはこちらです。
```SQL
SELECT name, date FROM StoreA WHERE name IN (SELECT name FROM StoreB WHERE date >= '2022/9/1');
-- A 2022/10/1
-- B 2022/9/1
```
出力は`A 2022/10/1, B 2022/9/1`になります。
NULLが含まれていようとも、正しく出力できています。ここで1つ注意なのですが、`NULL IN {NULL}`は正しく動作しないです。`NULL IS NULL`を使用しましょう。

では、9月以降に売れた商品を含まない場合はどうなるでしょうか？`NOT IN`を使ってみます。
```SQL
SELECT name, date FROM StoreA WHERE name NOT IN (SELECT name FROM StoreB WHERE date >= '2022/9/1');
-- 何も表示されない
```
予想なら`D 2022/6/1`となるはずでしたが、何も表示されません。
これは比較条件がNULLになってしまっているのが原因です。順を追って見てみましょう。

 1. サブクエリが実行されて`{A, B, NULL}`が出力される。
 2. `NOT IN`をそれぞれの要素の比較条件のANDに変換する。
 3. `(name <> NULL) = NULL`でありAND真理値表から比較条件はNULLとなる。
```sql
SELECT name, date FROM StoreA WHERE name NOT IN {A, B, NULL};
SELECT name, date FROM StoreA WHERE name <> A AND name <> B AND name <> NULL;
SELECT name, date FROM StoreA WHERE TorF AND TorF AND NULL;
```
`IN`も`NOT IN`もNULLを含むので、この様になってしまいます。
### 回避策
`NOT EXISTS`関数を使用することで、NULLを除外して実行できます。
```sql
SELECT name, date FROM StoreA WHERE name NOT EXISTS (SELECT name FROM StoreB WHERE date >= '2022/9/1');
-- D 2022/6/1
```
### 覚えておくこと
- `NOT IN`にはNULLが渡らないようにする。`NOT EXISTS`か`NOT NULL`制約を使用する。
- WHERE句にNULLが入ると出力がなくなる。
- `NULL IN {NULL}`はNULL。`NULL IS NULL`を使用する。

# 実践例
NULLが含まれているテーブルに対して、実用的にSQLを書く例を紹介します。
## HavingによるNULL排除
店舗で日用品の売れた日付を管理するテーブルがあり、売り切れとなった品目を探したいとします。
![商品帳簿.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605179/c8293a50-78ed-cbca-130e-48bddf245820.png)
ここで覚えておいてほしいのは、`COUNT(*)`はNULLを数えて、`COUNT("any")`はNULLを排除するということです。

また、`MAX`といった集約関数の結果と値を比較したい場合、`WHERE`では上手く行きません。
```sql
SELECT '町名' FROM '天気' WHERE '気温' = MAX('気温');
-- 上手く行かない
```
集約した結果に対して、何か絞り込みをかけたい場合は、サブクエリ（副問い合わせ）を利用するか、`HAVING`, `GROUP BY`を利用する必要があります。
(参考:[PostgreSQL 9.21. 集約関数](https://www.postgresql.jp/document/14/html/functions-aggregate.html), [PostgreSQL 2.7 集約関数](https://www.postgresql.jp/document/14/html/tutorial-agg.html))
### 結果
この違いを利用して、`GROUP BY`, `HAVING`と組み合わせることで`NULL`排除が可能になります。
```sql
SELECT '品目' FROM '商品帳簿' GROUP BY '品目' HAVING COUNT(*) = COUNT ('発売日');
```
`GROUP BY`で品目ごとにグループ化し、`COUNT`の結果が異なるかどうかを`HAVING`で判定しています。
### 覚えておくこと
- 集約関数の結果を条件指定したい場合、`WHERE`ではエラーになる。サブクエリか`HAVING`,`GROUP BY`を使用する。
- `COUNT(*)`はNULLを数えて、`COUNT("any")`はNULLを排除する。
- 集約関数はサブクエリとCASE文で置き換えることもできる。

# あとがき
本当は他にもCASE文や外部結合、副問い合わせの話、DB設計の話も勉強になって面白かったのですが、今回はNULLに関するものに留めました。
データベース専門ではないプログラマがデータベースと触れ合う一番の機会は、データを扱うクエリを考える際だろうと今回のテーマを選んだ所存です。
NULLの意識は常に持っておかないと、知らぬ間にまったく異なる挙動が生まれる可能性もあるんだな、と戒めつつ業務に励んでいきます。

最後に株式会社HRBrainでは新しいメンバーを募集中です。

https://www.hrbrain.co.jp/recruit

僕自身、新卒で入社予定なのですが、社内の雰囲気もよく、エンジニアとしても尊敬できる先輩がたくさんいます。
興味がありましたら、ぜひご応募下さい！

# 参考文献
- [PostgreSQL 14.5文書](https://www.postgresql.jp/document/14/html/index.html)
- [達人に学ぶSQL徹底指南書](https://www.amazon.co.jp/dp/4798115169?tag=shoeishacom-22)
- [達人に学ぶDB設計 徹底指南書](https://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6DB%E8%A8%AD%E8%A8%88-%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8-%E5%88%9D%E7%B4%9A%E8%80%85%E3%81%A7%E7%B5%82%E3%82%8F%E3%82%8A%E3%81%9F%E3%81%8F%E3%81%AA%E3%81%84%E3%81%82%E3%81%AA%E3%81%9F%E3%81%B8-%E3%83%9F%E3%83%83%E3%82%AF/dp/4798124702)
- [SQLアンチパターン](https://www.amazon.co.jp/SQL%E3%82%A2%E3%83%B3%E3%83%81%E3%83%91%E3%82%BF%E3%83%BC%E3%83%B3-Bill-Karwin/dp/4873115892/ref=pd_lpo_2?pd_rd_w=3u8wy&content-id=amzn1.sym.d769922e-188a-40cc-a180-3315f856e8d6&pf_rd_p=d769922e-188a-40cc-a180-3315f856e8d6&pf_rd_r=FR9NNTWZ1RK6YJF6BHG7&pd_rd_wg=CbUFr&pd_rd_r=dc30f28c-baba-4b11-a0b3-6f4fca03296c&pd_rd_i=4873115892&psc=1)
- [SQL言語リファレンス](https://docs.oracle.com/cd/E82638_01/sqlrf/Nulls.html#GUID-C785FE74-5F9C-4F70-AC4B-CA5D3010162A)