SQL join 片方のテーブルに存在しないデータをカウント0で集計したい

Question

前提・実現したいこと

[商品(マスタ)]と[購入(データ)]のテーブルから、返品されなかった商品の数量を
count()関数で求めるＳＱＬ文を作成しています。
[購入(データ)]に商品のレコードがない場合にでも、[期待する結果]のように数量を0として
結果を求めたいのですが、条件に返品なしの商品(返品=0)を追加すると、
数量0の結果が表示されなくなります。
JOIN部分を色々と変えて試してみたのですが期待する結果が得られません。
JOINを使うのは間違えなのでしょうか？ご教授いただければ幸いです。

[商品(マスタ)]

商品番号	商品名
1	リンゴ
2	バナナ
3	ミカン

[購入(データ)]

購入番号	商品番号	返品
1	1	0
2	1	0
3	1	1
4	3	0
5	3	1

[期待する結果]
商品(マスタ)にある商品で、返品されなかった商品（購入.返品=0）の集計を求める

数量	商品番号	商品名
2	1	リンゴ
0	2	バナナ
1	3	ミカン

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

期待する結果を求めるSQLを実行したのですが、下記の結果のように、バナナの数量０のレコードが出力されません。

数量	商品番号	商品名
2	1	リンゴ
1	2	ミカン

該当のソースコード

SELECT COUNT(k.購入番号) AS 数量,  s.商品番号, s.商品名,  FROM  購入 AS k RIGHT OUTER JOIN 商品 AS s ON k.商品番号 = s.商品番号 WHERE k.返品 = 0 GROUP BY k.商品番号

試したこと

WHERE条件を除くとバナナの数量０のレコードが出力されます。

SELECT COUNT(k.購入番号) AS 数量,  s.商品番号, s.商品名,  FROM  購入 AS k RIGHT OUTER JOIN 商品 AS s ON k.商品番号 = s.商品番号  GROUP BY k.商品番号

数量	商品番号	商品名
3	1	リンゴ
0	2	バナナ
2	3	ミカン

補足情報（FW/ツールのバージョンなど）

サーバのバージョン： 10.4.11-MariaDB - mariadb.org binary distribution
データベースクライアントのバージョン： libmysql - mysqlnd 7.4.2

Answer 1

SELECT sum(case when k.返品=0 then 1 else 0 end) AS 数量,  s.商品番号, s.商品名  
FROM  購入 AS k RIGHT OUTER JOIN 商品 AS s ON k.商品番号 = s.商品番号 
GROUP BY S.商品番号, s.商品名  

こっちでも大丈夫かも ← 駄目ですね。。

SELECT count(k.返品=0) AS 数量,  s.商品番号, s.商品名  
FROM  購入 AS k RIGHT OUTER JOIN 商品 AS s ON k.商品番号 = s.商品番号 
GROUP BY S.商品番号, s.商品名  

count()ならこうですね。

SELECT count(k.返品) AS 数量,  s.商品番号, s.商品名,  
FROM  購入 AS k RIGHT OUTER JOIN 商品 AS s ON k.商品番号 = s.商品番号 and k.返品=0
GROUP BY k.商品番号

Comments

[kitsu]

sazi様
お教えいただいたＳＱＬで無事に望む結果が得られました。本当にありがとうございます。
恐れ入りますが、後学のために、ここで教えてほしいのですが、今回のような場合、
whereで条件を設定すると、数量０のような集計は無理なのでしょうか？
というのはレコード件数が多くなった場合、sum()やcase分を1行ごとに行うのは、
スピード的に遅くなったりしないものなのでしょうか？

[kitsu]

SELECT count(k.返品=0)～ の分でも正しい結果が得られました。ありがとうございます。

[sazi]

inner join なら返品=0でも良いですけど、outer joinの場合はNull考慮が必要になります。
coalesce(返品, 1)=0とかしないと駄目です。
若しくはインラインビューで返品=0のものとの結合にするとか。

> レコード件数が多くなった場合、sum()やcase分を1行ごとに行うのは、スピード的に遅くなったりしないものなのでしょうか？

データの分布やインデックスへの依存の方が大きいかと思います、

[sazi]

SELECT count(k.返品=0)はk.返品=1の場合もカウントするから駄目ですね。
SELECT count(k.返品)と同義です

[kitsu]

今回の案件では頂きましたご回答で十分な結果が得られております。今後修正することを考慮して、インラインビューを使ってする方法にチャレンジしてみます。

[kitsu]

SELECT count(k.返品=0)で再確認したら、カウントがリンゴ：3,バナナ：0,ミカン：2になってました。バナナ：０が出てて安心しちゃいました。

[sazi]

結合条件だけでも大丈夫だと思います。（追記しました)

[kitsu]

結合条件にしたら行けるんですね！勉強になります。私はwhere条件にk.返品=0を設定していたので、ダメだったんですね。
さっきヒントを頂いた、インラインビューで試したら、こちらでもできました。その時のＳＱＬです。
select s.商品名, count(k.購入番号) from (SELECT * FROM `購入` WHERE `返品` = 0) as k RIGHT JOIN 商品 as s ON k.商品番号 = s.商品番号 group by s.商品番号
色々と勉強させた頂いて感謝です。

Answer 2

とりあえず、

RIGHT OUTER JOIN
  ↓
LEFT OUTER JOIN

それと、

                   ↓ , があるとエラーになります。
SELECT ... s.商品名,  FROM  購入  ... 

最初から質問のテーブルの定義はCREATE TABLE文に変更し、テーブル中のデータはINSERT文で提示できたがSQLで動作確認できるので適切なコメントが付き易いです。

Comments

[sazi]

商品マスタを基準にするから、Right Joinは正しいと思いますよ。

[kitsu]

Orlofsky様
早速のご回答ありがとうございました。
ご教授頂きました、LEFT OUTER JOINと、
「SELECT ... s.商品名, FROM 購入 」の「s.商品名, 」カンマと削除して
試してみましたが、やはり数量０の集計が出てきませんでした。
インデックスとか関係あるのでしょうか？
ちなみに、商品(マスタ)は商品番号に購入(データ)は購入番号に、
PRIMARYインデックスを付けております。

[Orlofsky]

saziさん、ご指摘ありがとうございます。勘違いしていました。

[Orlofsky]

kitsuさん
質問は修正できます。
CREATE TABLE文とINSERT文の提示と余計な , を削っては？

[kitsu]

Orlofsky様
失礼しました。次回から質問文の間違いは訂正いたしますね。
CREATE TABLE文とINSERT文の提示も、回答者様にお手間を取らせないように
するためには大切ですね。次回から提示させていただきます。
勉強になりました。