検索条件をWHEREで指定する場合とJOIN ONで指定する場合の違い

mysql

mysqlにおいてWHEREとJOIN ONで条件を指定した場合の違いがよくわかっていなかったのでまとめておく。

映画を表すmoviesテーブルと映画の日毎の再生回数を表すplaycountsテーブルがあるとする。

moviesテーブル

+----+------------+
| id | title      |
+----+------------+
|  1 | MI3        |
|  2 | Super Man  |
|  3 | Spider Man |
+----+------------+

playcountsテーブル

+----+-------+----------+------------+
| id | count | movie_id | date_at    |
+----+-------+----------+------------+
|  1 |    10 |        1 | 2012-12-16 |
|  2 |     5 |        3 | 2012-12-16 |
|  3 |     4 |        1 | 2012-12-15 |
|  4 |    22 |        2 | 2012-12-15 |
|  5 |    30 |        3 | 2012-12-15 |
+----+-------+----------+------------+

2012/12/16の人気映画のランキングを再生回数が0回(つまりまだ登録されていない)のものも含めて出力したい場合、OUTER JOINしてからWHEREで日付を絞ればいいと考える。

WHEREで指定する場合

> SELECT movies.*,playcounts.* FROM movies LEFT OUTER JOIN playcounts ON playcounts.movie_id = movies.id WHERE playcounts.date_at = '2012-12-16' ORDER BY count DESC;

しかしこの場合結果は以下のようになる。
人気のないSuper Manがランキングに表示できない残念な結果となってしまった。

+----+------------+------+-------+----------+------------+
| id | title      | id   | count | movie_id | date_at    |
+----+------------+------+-------+----------+------------+
|  1 | MI3        |    1 |    10 |        1 | 2012-12-16 |
|  3 | Spider Man |    2 |     5 |        3 | 2012-12-16 |
+----+------------+------+-------+----------+------------+

JOINの右側として指定したplaycounts.date_atが2012/12/16以外のレコードは結果に含まれない。
なぜならWHERE句はJOINが終わってから評価されるからである。

このSQLの動きを順番に見ていくと頭を整理しやすい。

1. まずJOINしたところでの中間結果は下記のようになり、

+----+------------+------+-------+----------+------------+
| id | title      | id   | count | movie_id | date_at    |
+----+------------+------+-------+----------+------------+
|  1 | MI3        |    1 |    10 |        1 | 2012-12-16 |
|  1 | MI3        |    3 |     4 |        1 | 2012-12-15 |
|  2 | Super Man  |    4 |    22 |        2 | 2012-12-15 |
|  3 | Spider Man |    2 |     5 |        3 | 2012-12-16 |
|  3 | Spider Man |    5 |    30 |        3 | 2012-12-15 |
+----+------------+------+-------+----------+------------+

2. WHERE句によって2012/12/16以外のレコードは結果から消えてしまうのである。

+----+------------+------+-------+----------+------------+
| id | title      | id   | count | movie_id | date_at    |
+----+------------+------+-------+----------+------------+
|  1 | MI3        |    1 |    10 |        1 | 2012-12-16 |
|  3 | Spider Man |    2 |     5 |        3 | 2012-12-16 |
+----+------------+------+-------+----------+------------+


この問題を回避するには、WHERE句に条件を指定するのでなくJOIN ONの追加条件として指定する。

ON 条件文は WHERE 条項の中で利用する事ができる形の条件文です。通常、テーブルをどのように接合するのかを指定する条件には ON 条項を、結果セットの中にどの行が必要であるかを制限するには WHERE 条項を利用する必要があります。

http://dev.mysql.com/doc/refman/5.1/ja/join.html

JOIN ONで指定する場合

> SELECT movies.*,playcounts.* FROM movies LEFT OUTER JOIN playcounts ON playcounts.movie_id = movies.id AND playcounts.date_at = '2012-12-16' ORDER BY count DESC;


JOINしたところでの中間結果でdate_atが2012/12/16ものが結合され、
かつLEFT OUTER JOINを行なっているため2012/12/16のplaycountsレコードを持たないmovieも結果に残ってくれる。

+----+------------+------+-------+----------+------------+
| id | title      | id   | count | movie_id | date_at    |
+----+------------+------+-------+----------+------------+
|  1 | MI3        |    1 |    10 |        1 | 2012-12-16 |
|  2 | Super Man  | NULL |  NULL |     NULL | NULL       |
|  3 | Spider Man |    2 |     5 |        3 | 2012-12-16 |
+----+------------+------+-------+----------+------------+

上記の結果からORDER BY COUNTした最終結果が下記。
2012/12/16に誰も見なかったSuper Manもランキング結果に含めることができる。

+----+------------+------+-------+----------+------------+
| id | title      | id   | count | movie_id | date_at    |
+----+------------+------+-------+----------+------------+
|  1 | MI3        |    1 |    10 |        1 | 2012-12-16 |
|  3 | Spider Man |    2 |     5 |        3 | 2012-12-16 |
|  2 | Super Man  | NULL |  NULL |     NULL | NULL       |
+----+------------+------+-------+----------+------------+

まとめ

JOIN ONとWHEREの条件が評価されるタイミングは異なる。
そのため結果も全く違うものになるので注意が必要である。