【SQL】なぜデカルト積が起きたのか紐とく【SQLアンチパターン】

アンチパターンの17章でデカルト積になる理由が分からなかったので、一行ずつ実行してできるテーブルを確認していったので、それを備忘録にまとめていきたいと思います。
テーブルの構成やデータは前回の記事を見ていただけたらと思います。

一行ずつみていく

まずは元のSQL文を見てみます。

SELECT bp.product_id, count(f.bug_id) AS count_fixed, COUNT(o.bug_id) AS count_open FROM bugs_products bp 
INNER JOIN bugs f ON bp.bug_id = f.bug_id AND f.status = 'FIXED'
INNER JOIN bugs_products p2 USING (product_id)
INNER JOIN bugs o ON p2.bug_id = o. but_id AND o.status = 'OPEN'
WHERE bp.product_id = 1
GROUP BY bp.product_id;

得たい結果は次のようなテーブルとします。

product_id	count_fixed	count_open
1	3	2

ではさっそく見ていきますが、テーブルが3つ結合していてSQL初心者の自分としては訳がわからないので、一つずつ分解していきます。
まずは一番単純なものから。

SELECT * FROM bugs_products bp INNER JOIN bugs f ON bp.bug_id = f.bug_id;

bp.bug_id	bp.product_id	b.bug_id	b.status
1	1	1	FIXED
2	1	2	FIXED
3	1	3	OPEN
4	1	4	OPEN
5	1	5	FIXED
1	2	1	FIXED
2	2	2	FIXED

次はAND条件でステータスを絞ります。

bp.bug_id	bp.product_id	b.bug_id	b.status
1	1	1	FIXED
2	1	2	FIXED
5	1	5	FIXED
1	2	1	FIXED
2	2	2	FIXED

さらにbugs_productsテーブルを結合します。

SELECT * FROM bugs_products bp INNER JOIN bugs f ON bp.bug_id = f.bug_id AND f.status = 'FIXED' INNER JOIN bugs_products p2 USING (product_id);

product_idが1のレコードが15行、2のレコードが4行できてしまいました。この理由は図に書いたほうがわかりやすいのですが、テーブル結合の怖いところがでてますね。

これでさらに今度はOPENのものだけbagsテーブルを結合させます。

SELECT * FROM bugs_products bp INNER JOIN bugs f ON bp.bug_id = f.bug_id AND f.status = 'FIXED' inner join bugs_products p2 USING (product_id) INNER JOIN bugs o ON p2.bug_id = o.bug_id AND o.status = 'OPEN';

bp.product_id	bp.bug_id	b.bug_id	b.status	p2.bug_id	f.bug_id	f.status
1	1	1	FIXED	3	3	OPEN
1	2	2	FIXED	3	3	OPEN
1	5	5	FIXED	3	3	OPEN
1	1	1	FIXED	4	4	OPEN
1	2	2	FIXED	4	4	OPEN
1	5	5	FIXED	4	4	OPEN

もういよいよ意図した結果にならないのが目に見えると思います。
最後にCOUNTします。

SELECT bp.product_id, COUNT(f.bug_id), COUNT(o.bug_id) FROM bugs_products bp INNER JOIN bugs f ON bp.bug_id = f.bug_id AND f.status = 'FIXED' INNER JOIN bugs_products p2 USING (product_id) INNER JOIN bugs o ON p2.bug_id = o.bug_id AND o.status = 'OPEN' WHERE bp.product_id = 1 GROUP BY bp.product_id;