SQL

【SQL】LEFT OUTER JOINでのAND句

LEFT OUTER JOINでAND句を使ったときの挙動がはじめてだったので、備忘録にブログに書きたい思います。
データベースはMYSQL5.6です。

LEFT OUTER JOINでAND句を使う

bugs_productsテーブル

bug_idproduct_id
11
21
31
41
51
12
22

bugsテーブル

bug_idstatus
1FIXED
2FIXED
3OPEN
4OPEN
5FIXED
6OPEN
SELECT * FROM bugs_products bp LEFT OUTER JOIN bugs b ON b.bug_id = bp.bug_id

こちらはなんてことないただの外部結合です。

bug_idproduct_idbug_idstatus
111FIXED
212FIXED
313OPEN
414OPEN
515FIXED
121FIXED
222FIXED

これにAND句を使うとこうなります。

SELECT * FROM bugs_products bp LEFT OUTER JOIN bugs b ON b.bug_id = bp.bug_id AND status = 'FIXED';
bp.bug_idbp.product_idb.bug_idb.status
111FIXED
212FIXED
31NULLNULL
41NULLNULL
515FIXED
121FIXED
222FIXED

3レコード目と4レコード目はstatus = ‘FIXED’のところで条件に合わなくなるので、外部結合の性質上NULLになるわけですね。

ある製品のバグの個数を数える際はINNER JOINを使ったほうが安全だと思いました。OUTER JOINだと数えるテーブルを間違えるとまちがった結果になります。

SELECT bp.product_id, COUNT(bp.product_id) FROM bugs_products bp LEFT OUTER JOIN bugs b ON b.bug_id = bp.bug_id AND status = 'FIXED' WHERE bp.product_id = 1 GROUP BY bp.product_id;
product_idcount(bp.product_id)
15

バグの個数を数えているのに、NULLの行も合計してしまっています。なので、COUNTはNULLを無視するので、それを利用してこう書く必要がありますね。

SELECT bp.product_id, COUNT(b.bug_id) FROM bugs_products bp LEFT OUTER JOIN bugs b ON b.bug_id = bp.bug_id AND status = 'FIXED' WHERE bp.product_id = 1 GROUP BY bp.product_id;
product_idcount(b.bug_id)
13

INNER JOINを使えばどのテーブルを使っても同じ結果になります。

さいごに

当たり前といっては当たり前の話でしたが、SQL力のなさを痛感しました。

ABOUT ME
sakai
三重出身の28歳。前職はメーカーで働いていて、プログラミングスクールに通って未経験からWeb業界に転職しました。Railsをメインで使っていて、AWSも少しできます。音楽を聞くこととYoutubeを見るのが好きです。最近はへきトラ劇場にハマってます