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

背景

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

やりたいこと

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

環境

テーブルは3つです。

1. 設定マスター：設定の種類を保持
2. 設定情報：ユーザーごとに設定情報を保持
3. ユーザー：ユーザー基本情報を保持
   

設定マスター　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 [e-mail]
          2 愛知　花子       2001-09-15 [e-mail]
          3 大阪　一郎       1987-12-01 [e-mail]

SQL

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

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します。

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)も連結します。

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でもとになるテーブルを決める