SQL:レコードがない場合もレコードを返したい

# 背景
レコードにセットされている値によって、出力や処理を変化させたい場合があります。select文のcase式が典型例です。ですがselectしたレコードが存在しない場合があります。selectしたレコードの値をチェックしようとしているのに照会結果がない場合、期待しない結果になります。

# やりたいこと
レコードがない場合でも必ずレコードを返すことにより値の真偽を確認したい。
※IBM Db2 V11.5 Windowsで確認していますが他のDBMSでもできるはずです。

# 環境
テーブルは3つです。
1. 設定マスター：設定の種類を保持
2. 設定情報：ユーザーごとに設定情報を保持
3. ユーザー：ユーザー基本情報を保持
![ER.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/698263/2502cae2-9ae0-b296-4ac8-0ad34d42491f.png)

```:設定マスター　CFG_MST
CFG_CD CFG_TEXT             CFG_DATE
------ -------------------- ----------
     1 誕生日を表示         2018-09-30
     2 メールアドレスを表示 2018-09-30
```

```:設定情報　CFG_TBL
USR_ID      CFG_CD CFG_BOOLEAN
----------- ------ -----------
          1      1           1
          1      2           0
          2      1           1
          2      2           0
          3      1           0
          3      2           1
```

```:ユーザー　USR_MST
USR_ID      USR_NAME         USR_BIRTH  USR_MAIL
----------- ---------------- ---------- --------------------
          1 東京　太郎       1999-05-07 taro@hogehoge.hoge
          2 愛知　花子       2001-09-15 hanako@hogehoge.hoge
          3 大阪　一郎       1987-12-01 ichiro@hogehoge.hoge
```

# SQL
例ではct.cfg_booleanに対してcase式を記述したselect文です。東京太郎さんの設定情報を取得します。

```sql:select_cfg_tbl_1.sql
SELECT um.usr_name, cm.cfg_cd, cm.cfg_text, ct.cfg_boolean, 
       CASE ct.cfg_boolean 
         WHEN true THEN '表示' 
         ELSE '非表示' 
       END AS display 
FROM   cfg_tbl ct, cfg_mst cm, usr_mst um 
WHERE  ct.cfg_cd = cm.cfg_cd 
       AND ct.usr_id = um.usr_id 
       AND ct.usr_id = 1 
ORDER  BY cm.cfg_cd ; 
```

実行結果は以下になります。

```:実行結果
C:\_work>db2 -tvf select_cfg_tbl_1.sql
SELECT um.usr_name, cm.cfg_cd, cm.cfg_text, ct.cfg_boolean, CASE ct.cfg_boolean WHEN true THEN '表示' ELSE '非表示' END AS display FROM   cfg_tbl ct, cfg_mst cm, usr_mst um WHERE  ct.cfg_cd = cm.cfg_cd AND ct.usr_id = um.usr_id AND ct.usr_id = 1 ORDER  BY cm.cfg_cd

USR_NAME         CFG_CD CFG_TEXT             CFG_BOOLEAN DISPLAY
---------------- ------ -------------------- ----------- -------
東京　太郎            1 誕生日を表示                   1 表示
東京　太郎            2 メールアドレスを表示           0 非表示

  2 レコードが選択されました。
```

ct.cfg_booleanがture(1)なら'表示'、そうでなければ、'非表示'を出力します。

# 問題点
アプリケーションの機能追加の結果、マスターテーブルにレコードを追加することはよくあることです。例は設定マスターに「自己紹介を表示」という3つ目のレコードを追加しています。

```:設定マスター　CFG_MST
CFG_CD CFG_TEXT             CFG_DATE
------ -------------------- ----------
     1 誕生日を表示         2018-09-30
     2 メールアドレスを表示 2018-09-30
     3 自己紹介を表示       2020-12-25 <-- 新しく追加したレコード
```

先ほどのSQLを再度実行します。

```:再度実行した結果
C:\_work>db2 -tvf select_cfg_tbl_1.sql
SELECT um.usr_name, cm.cfg_cd, cm.cfg_text, ct.cfg_boolean, CASE ct.cfg_boolean WHEN true THEN '表示' ELSE '非表示' END AS display FROM   cfg_tbl ct, cfg_mst cm, usr_mst um WHERE  ct.cfg_cd = cm.cfg_cd AND ct.usr_id = um.usr_id AND ct.usr_id = 1 ORDER  BY cm.cfg_cd

USR_NAME         CFG_CD CFG_TEXT             CFG_BOOLEAN DISPLAY
---------------- ------ -------------------- ----------- -------
東京　太郎            1 誕生日を表示                   1 表示
東京　太郎            2 メールアドレスを表示           0 非表示

  2 レコードが選択されました。
```

結果は変わりません。**データである設定情報(CFG_TBL)にCFG_CDが3のレコードがないため、テーブルを連結すると「自己紹介を表示」という3つ目のレコードは表示されません。** ですが、私の期待する実行結果は3つ目の設定マスターのレコードとcase式で記述したDISPLAY列の値が返ってきて欲しいです。

```:期待する実行結果
USR_NAME         CFG_CD CFG_TEXT             CFG_BOOLEAN DISPLAY
---------------- ------ -------------------- ----------- -------
東京　太郎            1 誕生日を表示                   1 表示
東京　太郎            2 メールアドレスを表示           0 非表示
-                     3 自己紹介を表示       -           非表示 <-- レコードがないなら非表示を出力
```

# 対策
Left Joinします。まずは、設定マスター(CFG_MST)を元に設定情報(CFG_TBL)とLeft Joinします。

```sql:select_cfg_tbl_1_LeftJoin.sql
SELECT ct.usr_id, cm.cfg_cd, cm.cfg_text, ct.cfg_boolean, 
       CASE ct.cfg_boolean 
         WHEN true THEN '表示' 
         ELSE '非表示' 
       END AS display 
FROM   cfg_mst cm 
       LEFT JOIN (SELECT * 
                  FROM   cfg_tbl 
                  WHERE  usr_id = 1) ct 
              ON cm.cfg_cd = ct.cfg_cd 
ORDER  BY cm.cfg_cd ;
```

実行結果は以下になります。設定情報(CFG_TBL)の**レコードがない行の列はNULL(-)** になります。

```:実行結果
C:\_work>db2 -tvf select_cfg_tbl_1_LeftJoin.sql
SELECT ct.usr_id, cm.cfg_cd, cm.cfg_text, ct.cfg_boolean, CASE ct.cfg_boolean WHEN true THEN '表示' ELSE '非表示
' END AS display FROM   cfg_mst cm LEFT JOIN (SELECT * FROM   cfg_tbl WHERE  usr_id = 1) ct ON cm.cfg_cd = ct.cfg_cd ORDER  BY cm.cfg_cd

USR_ID      CFG_CD CFG_TEXT             CFG_BOOLEAN DISPLAY
----------- ------ -------------------- ----------- -------
          1      1 誕生日を表示                   1 表示
          1      2 メールアドレスを表示           0 非表示
          -      3 自己紹介を表示       -           非表示

  3 レコードが選択されました。
```

CFG_BOOLEAN列もNULLなのでcase式はtrue(1)以外になり、**DISPLAY列は非表示**が出力されます。

# さらに
さらにユーザー(USR_MST)も連結します。

```sql:select_cfg_tbl_1_LeftJoin_mod
SELECT ct.usr_name, cm.cfg_cd, cm.cfg_text, ct.cfg_boolean, 
       CASE ct.cfg_boolean 
         WHEN true THEN '表示' 
         ELSE '非表示' 
       END AS display 
FROM   cfg_mst cm 
       LEFT JOIN (SELECT * 
                  FROM   cfg_tbl t1, usr_mst t2 
                  WHERE  t1.usr_id = t2.usr_id 
                         AND t1.usr_id = 1) ct 
              ON cm.cfg_cd = ct.cfg_cd 
ORDER  BY cm.cfg_cd ;
```
実行結果は以下になります。

```:実行結果
C:\_work>db2 -tvf select_cfg_tbl_1_LeftJoin_mod.sql
SELECT ct.usr_name, cm.cfg_cd, cm.cfg_text, ct.cfg_boolean, CASE ct.cfg_boolean WHEN true THEN '表示' ELSE '非表示' END AS display FROM   cfg_mst cm LEFT JOIN (SELECT * FROM   cfg_tbl t1, usr_mst t2 WHERE  t1.usr_id = t2.usr_id AND t1.usr_id = 1) ct ON cm.cfg_cd = ct.cfg_cd ORDER  BY cm.cfg_cd

USR_NAME         CFG_CD CFG_TEXT             CFG_BOOLEAN DISPLAY
---------------- ------ -------------------- ----------- -------
東京　太郎            1 誕生日を表示                   1 表示
東京　太郎            2 メールアドレスを表示           0 非表示
-                     3 自己紹介を表示       -           非表示

  3 レコードが選択されました。
```

期待した結果が返ってきました。設定マスター(CFG_MST)の行はすべて出力させ、他のテーブルでレコードがない場合はNULLをセットしたレコードで出力しています。**DISPLAY列は非表示**が出力されます。

# まとめ
- レコードが存在しない場合は列にNULLをセットしたレコードを返す
- Left Joinでもとになるテーブルを決める