SQLでのONとWHEREの違い

本文は主にSQL文中のONとWHEREの違いを紹介する.
1概要
接続操作を使用して、2つ以上のテーブルを関連付けてレコードを返すと、データベースは一時テーブルを生成し、最後にこの一時テーブルをユーザーに返します.
LEFTJOINを例にとると、LEFTJOINを使用する場合、ONとWHEREフィルタ条件の違いは以下の通りである.

ON条件は、仮テーブルを生成する際に使用される条件であり、ON中の条件が真であるかどうかにかかわらず、左のテーブルのレコードに戻る.

WHERE条件は、テンポラリテーブルが生成された後にテンポラリテーブルをフィルタリングする条件である.このときすでにLEFTJOINの意味がない(左の表の記録に戻らなければならない)ので、WHERE条件が本物でない場合はフィルタリングされます.

2例
この文書の2つのテーブルrolesとmount_infoで操作して、ONとWHEREの違いを紹介します.
2.1 ON+WHEREろ過条件
ON+WHEREフィルタ条件文は以下の通りです.

SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id) WHERE mount_info.mount_name="sheep";

上記SQL文の実行結果は次のとおりです.

mysql> SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id) WHERE mount_info.mount_name="sheep";
+---------+------------+----------+----------+------------+---------+
| role_id | occupation | camp     | mount_id | mount_name | role_id |
+---------+------------+----------+----------+------------+---------+
|       1 | warrior    | alliance |        2 | sheep      |       1 |
+---------+------------+----------+----------+------------+---------+
1 row in set (0.00 sec)

mysql> 

上記のSQL文の実行手順を分析します.
1.まず、ONフィルタ条件「roles.role_id=mount_info.role_id」(後のWHEREフィルタ条件を除く)に従って、中間表を生成する.

mysql> SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id);
+---------+------------+----------+----------+------------+---------+
| role_id | occupation | camp     | mount_id | mount_name | role_id |
+---------+------------+----------+----------+------------+---------+
|       1 | warrior    | alliance |        1 | horse      |       1 |
|       1 | warrior    | alliance |        2 | sheep      |       1 |
|       2 | paladin    | alliance |     NULL | NULL       |    NULL |
|       3 | rogue      | Horde    |     NULL | NULL       |    NULL |
+---------+------------+----------+----------+------------+---------+
4 rows in set (0.00 sec)

mysql> 

2.次に、上記で生成した中間テーブルについて、WHEREフィルタ条件「mount_info.mount_name=「sheep」に従って、次のような最終クエリ結果を生成する.

mysql> SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id) WHERE mount_info.mount_name="sheep";
+---------+------------+----------+----------+------------+---------+
| role_id | occupation | camp     | mount_id | mount_name | role_id |
+---------+------------+----------+----------+------------+---------+
|       1 | warrior    | alliance |        2 | sheep      |       1 |
+---------+------------+----------+----------+------------+---------+
1 row in set (0.00 sec)

2.2 ONろ過条件
ONフィルタ条件文は以下の通りです.

SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id AND mount_info.mount_name = "sheep");

上記SQL文の実行結果は次のとおりです.

mysql> SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id AND mount_info.mount_name = "sheep");
+---------+------------+----------+----------+------------+---------+
| role_id | occupation | camp     | mount_id | mount_name | role_id |
+---------+------------+----------+----------+------------+---------+
|       1 | warrior    | alliance |        2 | sheep      |       1 |
|       2 | paladin    | alliance |     NULL | NULL       |    NULL |
|       3 | rogue      | Horde    |     NULL | NULL       |    NULL |
+---------+------------+----------+----------+------------+---------+
3 rows in set (0.01 sec)

mysql> 

上記のSQL文の実行手順を分析します.
ONフィルタ条件「roles.role_id=mount_info.role_id AND mount_info.mount_name=「sheep」に従って中間テーブルを生成し、ONフィルタ条件が真実でなくても左テーブルのすべてのレコードを返します.
3まとめ
次の2つのONフィルタ条件文とその実行結果を比較します.

mysql> SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id);
+---------+------------+----------+----------+------------+---------+
| role_id | occupation | camp     | mount_id | mount_name | role_id |
+---------+------------+----------+----------+------------+---------+
|       1 | warrior    | alliance |        1 | horse      |       1 |
|       1 | warrior    | alliance |        2 | sheep      |       1 |
|       2 | paladin    | alliance |     NULL | NULL       |    NULL |
|       3 | rogue      | Horde    |     NULL | NULL       |    NULL |
+---------+------------+----------+----------+------------+---------+
4 rows in set (0.01 sec)

mysql> SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id AND mount_info.mount_name = "sheep");
+---------+------------+----------+----------+------------+---------+
| role_id | occupation | camp     | mount_id | mount_name | role_id |
+---------+------------+----------+----------+------------+---------+
|       1 | warrior    | alliance |        2 | sheep      |       1 |
|       2 | paladin    | alliance |     NULL | NULL       |    NULL |
|       3 | rogue      | Horde    |     NULL | NULL       |    NULL |
+---------+------------+----------+----------+------------+---------+
3 rows in set (0.01 sec)

mysql> 

ONフィルタ条件は、LEFTJOINモードでは、右側テーブルに対するフィルタ条件のみが追加されることに相当し、左側テーブルの内容は、常にすべて返されることがわかる.
上記の理由は、LEFT JOIN(およびRIGHT JOIN、FULL JOIN)の特殊性にあり、ON条件が真であるかどうかにかかわらず、データベースは左側(または右側、左右両側)表のすべての記録を返します.INNER JOINはこのような特殊性がないため、フィルタ条件はON中またはWHERE中に置かれ、その戻りの結果は同じである.