片方のテーブルにデータが存在しない場合の、JOIN方法

Question

データベース初心者です。

ヘルスケアマスタ、会員マスタと、ユーザーマスタ(ID情報を一元管理したもの)で結合させて、下記アウトプットビューにあるように、日付および個人データ(会員番号等)ごとに、ヘルスケアマスタと会員マスタのデータ(歩数、作業時間等)を出力させたいです。

アウトプットビューに出力する際の条件として、ヘルスケアマスタと会員マスタで一致する日付データが存在しなくても、ヘルスケアマスタと会員マスタから抽出してきたカラム部分についてはデータがない片側はnullでよいので出力させたく、ユーザーマスタから抽出してきたカラム部分については、nullではなく、対象のレコードに関わる個人データをかならず表示させるようにしたいです。
下記の表のようなイメージです。

ユーザーマスタカラム	ヘルスケアカラム	会員カラム
ヘルスケアマスタ、会員カラムでどちらかが欠けているレコードがあっても、nullではなく個人データを出力	データがなければnullでok	データがなければnullでok

下記のSQL文のように、それぞれのマスタをfull outer joinで二重に結合させ、ヘルスケアか会員マスタのどちらかでデータがなくても出力させるようにOR文で条件指定しましたが、②のアウトプットビューにあるように、ユーザーマスタから抽出してきたカラム部分が nullになってしまうようです。

ヘルスケアか会員マスタのどちらかでデータがなくても、ユーザーマスタから抽出してきたカラム部分については、必ず出力させるようにするためには、下記SQL文をどのように修正が必要でしょうか？
ご存じの方いましたら、ご教示いただけると幸いです。

SELECT * FROM 
(("ヘルスケアマスタ" 
FULL OUTER JOIN "会員マスタ ON "ヘルスケアマスタ"."スマートウオッチ測定日" = "会員マスタ"."日") 
FULL OUTER JOIN "ユーザーマスタ" ON "ヘルスケアマスタ"."スマートウオッチID" = "ユーザーマスタ"."会員番号" OR "会員マスタ"."会員番号" = "ユーザーマスタ"."会員番号")

①正解アウトプット用ビュー

スマートウオッチID	会員番号	ユーザ名	スマートウオッチ測定日	歩数	睡眠時間	作業日	作業時間
5700000	dummy0001	田中太郎	2022-12-06	10000	9.7	null	null
5700023	dummy0002	佐藤一郎	2022-12-07	7000	5.6	null	null
5700056	dummy0003	山田花子	2022-12-08	1200	7.0	null	null
5700000	dummy0001	田中太郎	null	null	null	2022-01-09	9.0
5700023	dummy0002	佐藤一郎	null	null	null	2022-01-10	12.0
5700056	dummy0003	山田花子	null	null	null	2022-01-11	11.0
5700000	dummy0001	田中太郎	2022-01-12	15000	8.0	2022-01-12	8.0

②ユーザーマスタのカラムが nullになってしまう現在のアウトプット用ビュー

スマートウオッチID	会員番号	ユーザ名	スマートウオッチ測定日	歩数	睡眠時間	作業日	作業時間
5700000	null	田中太郎	2022-12-06	10000	9.7	null	null
5700023	null	佐藤一郎	2022-12-07	7000	5.6	null	null
5700056	null	山田花子	2022-12-08	1200	7.0	null	null
null	null	null	null	null	null	2022-01-09	9.0
null	null	null	null	null	null	2022-01-10	12.0
null	null	null	null	null	null	2022-01-11	11.0
5700000	dummy0001	田中太郎	2022-01-12	15000	8.0	2022-01-12	8.0

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
↓下記はマスタ一覧です。

ヘルスケアマスタ

スマートウオッチ測定日	スマートウオッチID	歩数	睡眠時間
2022-12-06	5700000	10000	9.7
2022-12-07	5700023	7000	5.6
2022-12-08	5700056	1200	7.0
2022-01-12	5700000	15000	8.0

会員マスタ

作業日	会員番号	作業時間
2022-01-09	dummy0001	9.0
2022-01-10	dummy0002	12.0
2022-01-11	dummy0003	11.0
2022-01-12	dummy0001	8.0

ユーザマスタ

スマートウオッチID	会員番号	ユーザ名
5700000	dummy0001	田中太郎
5700023	dummy0002	佐藤一郎
5700056	dummy0003	山田花子

Comments

[maisumakun]

アイデアレベルなのでこちらに書きますが、まずユーザーマスタから始めてJOINさせてみたらどうなりますか？

[revoiot]

maisumakunさんのご指摘の通り、
下記のSQL文のように、ユーザーからjoinした場合、ユーザーマスタから抽出してきたカラム部分が nullになってしまう現象は変わらないようです。今回データがない片方はnullとして出力させたいので、Full outer joinとして結合しています。

SELECT *
FROM (("ユーザマスタ" FULL OUTER JOIN "ヘルスケアマスタ" ON "ヘルスケアマスタ"."スマートウオッチID" = "ユーザマスタ"."スマートウオッチID")
FULL OUTER JOIN "会員マスタ" ON "ヘルスケアマスタ"."スマートウオッチ測定日" = "会員マスタ"."作業日")

Answer 1

PK情報や実際の仕様などがわからないため、工夫次第ではもう少し単純化できそうですが、たとえば以下のようなクエリはどうでしょうか?

WITH
A AS (
  SELECT U.スマートウオッチID, U.会員番号, U.ユーザ名, H.スマートウオッチ測定日, H.歩数, H.睡眠時間
  FROM ユーザマスタ U
  JOIN ヘルスケアマスタ H ON U.スマートウオッチID = H.スマートウオッチID
),
B AS (
  SELECT U.スマートウオッチID, U.会員番号, U.ユーザ名, K.作業日, K.作業時間
  FROM ユーザマスタ U
  JOIN 会員マスタ K ON U.会員番号 = K.会員番号
)
SELECT S.スマートウオッチID, S.会員番号, S.ユーザ名, A.スマートウオッチ測定日, A.歩数, A.睡眠時間, B.作業日, B.作業時間
FROM (
  SELECT A.スマートウオッチID, A.会員番号, A.ユーザ名, A.スマートウオッチ測定日 FROM A
  UNION
  SELECT B.スマートウオッチID, B.会員番号, B.ユーザ名, B.作業日 FROM B
) AS S (スマートウオッチID, 会員番号, ユーザ名, 基準日)
LEFT OUTER JOIN A ON S.スマートウオッチID = A.スマートウオッチID AND S.会員番号 = A.会員番号 AND S.ユーザ名 = A.ユーザ名 AND S.基準日 = A.スマートウオッチ測定日
LEFT OUTER JOIN B ON S.スマートウオッチID = B.スマートウオッチID AND S.会員番号 = B.会員番号 AND S.ユーザ名 = B.ユーザ名 AND S.基準日 = B.作業日

Answer 2

アウトプット用ビューを作るときに、
どっちのテーブルにあるデータを使っていますか？
データが存在しない方のテーブルのデータを参照するとnullですよね。

mysql8だと
COALESCE
postgresql13だと
COALESCE
が使えそうです。
引数に与えるデータのうち、NULLではない最初のものが使われるので、
テーブルAにもBにも存在するデータで基本的に同じってことであれば、
COALESCE(テーブルA.名前, テーブルB.名前) AS "名前"みたいにするとよいです。

と書いてから気づきましたが、
maisumakunさんの指摘にあるように、
(ユーザマスタ LEFT JOIN ヘルスケアマスタ) LEFT JOIN 会員マスタ
っていうJOINの仕方をすれば、ナナシさんにはならないはずですよね。

Comments

[revoiot]

回答ありがとうございます！
COALESCEというもの初めて知りました。
今回ヘルスケアマスタと会員マスタでは、別々のID情報で管理されているのですが、早速、COALESCE("ヘルスケアマスタ"."スマートウオッチID" , "会員マスタ"."会員番号")で出力してみたところ、1つの列に異なるID情報を集約させることができました。しかし、スマートウオッチIDと会員番号では、全く種類が異なるものであり、できれば、それぞれのID情報を別々に列を出力させたいです。
ユーザーマスタに全てのマスタのID情報を持たせているので、その情報をうまく参照して、
ヘルスケアマスタと会員マスタのユーザーマスタのID情報を出力させることは可能だったりするのでしょうか？

maisumakunさんのご指摘の通り、
下記のSQl文のように、ユーザーからjoinした場合、ユーザーマスタから抽出してきたカラム部分が nullになってしまう現象は変わらないようです。今回データがない片方はnullとして出力させたいので、Full outer joinとして結合しています。

SELECT *
FROM (("ユーザマスタ" FULL OUTER JOIN "ヘルスケアマスタ" ON "ヘルスケアマスタ"."スマートウオッチID" = "ユーザマスタ"."スマートウオッチID")
FULL OUTER JOIN "会員マスタ" ON "ヘルスケアマスタ"."スマートウオッチ測定日" = "会員マスタ"."作業日")