SQL中JOIN操作后接ON和WHERE关键字的区别

本文主要介绍SQL(Structured Query Language)中JOIN操作后接ON和WHERE关键字的区别。

说明：本文的用法示例是面向MySQL数据库的。

1 概述

当使用连接（JOIN）操作，关联两张或多张表返回记录时，数据库都会生成一张临时表，最后将这张临时表返回给用户。

这里以LEFT JOIN为例。在使用LEFT JOIN时，ON和WHERE过滤条件的区别如下：

• ON条件是在生成临时表时使用的条件，它不管ON中的条件是否为真，都会返回左边表中的全部记录；
• WHERE条件是在临时表已经生成后，对临时表进行的过滤条件。因为此时已经没有LEFT JOIN的含义（必须返回左侧表的记录）了，所以WHERE条件不为真的记录就会被过滤掉。

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 总结

下面是两条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> 

通过对比上述两条结果，能够发现在LEFT JOIN模式下，ON过滤条件相当于只增加了对于右侧表的过滤条件，而左侧表的内容，总是会全部返回的。

出现上述情况的原因在于LEFT JOIN（以及RIGHT JOIN、FULL JOIN）的特殊性，不管ON条件是否为真，数据库都会返回左侧（或右侧、左右两侧）表中的全部记录。此外，由于INNER JOIN没这样的特殊性，所以过滤条件放在ON中或WHERE中，其返回的结果是一样的。