LEFT OUTER JOINでAND句を使ったときの挙動がはじめてだったので、備忘録にブログに書きたい思います。
データベースはMYSQL5.6です。
LEFT OUTER JOINでAND句を使う
bugs_productsテーブル
| bug_id | product_id |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 1 | 2 |
| 2 | 2 |
bugsテーブル
| bug_id | status |
| 1 | FIXED |
| 2 | FIXED |
| 3 | OPEN |
| 4 | OPEN |
| 5 | FIXED |
| 6 | OPEN |
SELECT * FROM bugs_products bp LEFT OUTER JOIN bugs b ON b.bug_id = bp.bug_idこちらはなんてことないただの外部結合です。
| bug_id | product_id | bug_id | 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句を使うとこうなります。
SELECT * FROM bugs_products bp LEFT OUTER JOIN bugs b ON b.bug_id = bp.bug_id AND status = 'FIXED';| bp.bug_id | bp.product_id | b.bug_id | b.status |
| 1 | 1 | 1 | FIXED |
| 2 | 1 | 2 | FIXED |
| 3 | 1 | NULL | NULL |
| 4 | 1 | NULL | NULL |
| 5 | 1 | 5 | FIXED |
| 1 | 2 | 1 | FIXED |
| 2 | 2 | 2 | FIXED |
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_id | count(bp.product_id) |
| 1 | 5 |
バグの個数を数えているのに、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_id | count(b.bug_id) |
| 1 | 3 |
INNER JOINを使えばどのテーブルを使っても同じ結果になります。
さいごに
当たり前といっては当たり前の話でしたが、SQL力のなさを痛感しました。
