【SQL】外部結合でNULLの行が消えてしまう

Question

前提

MySQLを使用。

実現したいこと

外部結合にてstudent_nameごとにすべてのcredit_catを結合させたい。

発生している問題・エラーメッセージ

問題

外部結合を実施した際にすべてのcredit_catが存在しないstudent_nameにおいて
存在しないcredit_catが消えてしまう。(NULLにならない)
例)CreditsEarnedテーブルのBobはcredit_catはA, BのみでCがない。
外部結合をした際にBobのcredit_catのCはNULLであって欲しい。
(student_nameごとにcredit_catはA, B, Cが割り当てられて欲しい。)

該当のソースコード

SELECT 
CR.student_name
,CR.credit_cat
FROM  CreditsEarned AS CR
RIGHT JOIN Categories AS CA
ON CR.credit_cat = CA.credit_cat
GROUP BY CR.student_name, CR.credit_cat
;

CREATE TABLE Categories
(credit_cat CHAR(1) NOT NULL,
 rqd_credits INTEGER NOT NULL);

CREATE TABLE CreditsEarned --主キーなし
(student_name CHAR(10) NOT NULL,
 credit_cat CHAR(1) NOT NULL,
 credits INTEGER NOT NULL);

INSERT INTO Categories VALUES ('A', 10);
INSERT INTO Categories VALUES ('B', 3);
INSERT INTO Categories VALUES ('C', 5);

INSERT INTO CreditsEarned
VALUES ('Joe', 'A', 3), ('Joe', 'A', 2), ('Joe', 'A', 3),
       ('Joe', 'A', 3), ('Joe', 'B', 3), ('Joe', 'C', 3),
       ('Joe', 'C', 2), ('Joe', 'C', 3),
       ('Bob', 'A', 2), ('Bob', 'C', 2), ('Bob', 'A', 12),
       ('Bob', 'C', 4),
       ('John', 'A', 1), ('John', 'B', 1),
       ('Mary', 'A', 1), ('Mary', 'A', 1), ('Mary', 'A', 1),
       ('Mary', 'A', 1), ('Mary', 'A', 1), ('Mary', 'A', 1),
       ('Mary', 'A', 1), ('Mary', 'A', 1), ('Mary', 'A', 1),
       ('Mary', 'A', 1), ('Mary', 'A', 1), ('Mary', 'B', 1),
       ('Mary', 'B', 1), ('Mary', 'B', 1), ('Mary', 'B', 1),
       ('Mary', 'B', 1), ('Mary', 'B', 1), ('Mary', 'B', 1),
       ('Mary', 'C', 1), ('Mary', 'C', 1), ('Mary', 'C', 1),
       ('Mary', 'C', 1), ('Mary', 'C', 1), ('Mary', 'C', 1),
       ('Mary', 'C', 1), ('Mary', 'C', 1);

Bob  A
     B
     C
------------------------------
joe  A
     B
     C
-------------------------------
John  A
      B
      C
------------------------------
Mary  A
      B
      C

Answer 1

CreditsEarnedからstudent_nameの一覧を取得し、CategoriesとCROSS JOINしてstudent_nameとcredit_catの全組み合わせを作ります。
全組み合わせ側がすべて出力されるようにLEFT JOINを使って、例えばcreditsの合計を算出した結果と結合します。

SELECT * FROM
  (
    SELECT * FROM
      (SELECT DISTINCT student_name FROM CreditsEarned) AS CR
      CROSS JOIN
      Categories AS CA
  ) T1
  LEFT JOIN
  (
    SELECt student_name, credit_cat, SUM(credits) credits_sum
      FROM CreditsEarned
      GROUP BY student_name, credit_cat
  ) T2
  ON T1.student_name=T2.student_name AND
     T1.credit_cat=T2.credit_cat

DB Fiddle

Answer 2

JOINとUUTER OUTER JOINの違い を参考に。
混乱しにくいようにLEFTだけに限定してRIGHTは使わない、ってSQLルールのお客様も多いです。

Comments

[YT0014]

リンクのタイトル部は、誤字(OUTER=>UUTER)だと思われますので、お手数ですが修正をお願いします。

[shishi_maru440]

コメントありがとうございます。
BobにはCは存在しないのできないのは理解できました。
BobのCをNULLにするにはCASE式でなんとかするしかないでしょうか。

[Orlofsky]

ちゃんと回答を読んでその内容を反映したSQLを実行しましたか？
それを質問に追記してください。