SQL中ON和WHERE的区别及使用建议
引言
SQL中的ON和WHERE均可用于声明条件并实现连接表和筛选数据的功能。虽然它们在某些情形下可以混用，但在其它情形下混用却有可能造成问题。为了能够在面对特定情形时选用正确的方法，我结合示例数据从以下几个方面对它们进行了比较：
- 语义与可读性；
- 查询结果；
- 逻辑顺序与执行效率。
此文将记录比较的结果以及我的一些个人理解，最后提供使用建议。
示例数据
假设两个表wechat_users和weibo_users分别存储了微信和微博两款应用的用户信息，且它们均含有person_id和register_year两个字段；其中，person_id是用户所属人的标识（同一个人在两个数据集中使用同一个标识），register_year是用户注册年份。下列SQL语句构造了这两个表，并且插入了一些示例数据：
CREATE TABLE wechat_users (
person_id INT,
register_year INT
);
INSERT INTO wechat_users
VALUES
(1, 2011),
(2, 2012),
(3, 2013),
(4, 2014);
SELECT * FROM wechat_users;+---------+-------------+
|person_id|register_year|
+---------+-------------+
|1 |2011 |
|2 |2012 |
|3 |2013 |
|4 |2014 |
+---------+-------------+CREATE TABLE weibo_users (
person_id INT,
register_year INT
);
INSERT INTO weibo_users
VALUE
(3, 2015),
(4, 2016),
(5, 2017);
SELECT * FROM weibo_users;+---------+-------------+
|person_id|register_year|
+---------+-------------+
|3 |2015 |
|4 |2016 |
|5 |2017 |
+---------+-------------+示例查询
下文将对以下语句及其所返回的结果进行比较和分析。其中，语句#1-语句#3可以用于比较ON和WHERE用作连接条件时的异同，而语句#4-语句#7可以用来比较两者在用作筛选条件时的异同。
-- 语句#1
SELECT
*
FROM
wechat_users t1, weibo_users t2
WHERE
t1.person_id = t2.person_id;+---------+-------------+---------+-------------+
|person_id|register_year|person_id|register_year|
+---------+-------------+---------+-------------+
|3 |2013 |3 |2015 |
|4 |2014 |4 |2016 |
+---------+-------------+---------+-------------+-- 语句#2
SELECT
*
FROM
wechat_users t1
INNER JOIN weibo_users t2 ON t1.person_id = t2.person_id;+---------+-------------+---------+-------------+
|person_id|register_year|person_id|register_year|
+---------+-------------+---------+-------------+
|3 |2013 |3 |2015 |
|4 |2014 |4 |2016 |
+---------+-------------+---------+-------------+-- 语句#3
SELECT
*
FROM
wechat_users t1
LEFT JOIN weibo_users t2 ON t1.person_id = t2.person_id;+---------+-------------+---------+-------------+
|person_id|register_year|person_id|register_year|
+---------+-------------+---------+-------------+
|1 |2011 |NULL |NULL |
|2 |2012 |NULL |NULL |
|3 |2013 |3 |2015 |
|4 |2014 |4 |2016 |
+---------+-------------+---------+-------------+-- 语句#4
SELECT
*
FROM
wechat_users t1
INNER JOIN weibo_users t2 ON t1.person_id = t2.person_id AND t1.register_year = 2014;+---------+-------------+---------+-------------+
|person_id|register_year|person_id|register_year|
+---------+-------------+---------+-------------+
|4 |2014 |4 |2016 |
+---------+-------------+---------+-------------+-- 语句#5
SELECT
*
FROM
wechat_users t1
INNER JOIN weibo_users t2 ON t1.person_id = t2.person_id
WHERE
t1.register_year = 2014;+---------+-------------+---------+-------------+
|person_id|register_year|person_id|register_year|
+---------+-------------+---------+-------------+
|4 |2014 |4 |2016 |
+---------+-------------+---------+-------------+-- 语句#6
SELECT
*
FROM
wechat_users t1
LEFT JOIN weibo_users t2 ON t1.person_id = t2.person_id AND t1.register_year = 2014;+---------+-------------+---------+-------------+
|person_id|register_year|person_id|register_year|
+---------+-------------+---------+-------------+
|1 |2011 |NULL |NULL |
|2 |2012 |NULL |NULL |
|3 |2013 |NULL |NULL |
|4 |2014 |4 |2016 |
+---------+-------------+---------+-------------+-- 语句#7
SELECT
*
FROM
wechat_users t1
LEFT JOIN weibo_users t2 ON t1.person_id = t2.person_id
WHERE
t1.register_year = 2014;+---------+-------------+---------+-------------+
|person_id|register_year|person_id|register_year|
+---------+-------------+---------+-------------+
|4 |2014 |4 |2016 |
+---------+-------------+---------+-------------+语义与可读性
从语义上讲，ON主要用来声明两个表中的各行数据之间的连接条件，而WHERE则主要用来声明表中每行数据的筛选条件。因此，统一使用前者进行表连接、使用后者进行数据筛选，可以使语句逻辑更加清晰易读；否则，可能会降低可读性。
先分析语句#1和语句#2，看看ON和WHERE在用于表连接时的可读性区别。语句#1中的表连接是通过WHERE实现的隐式连接（implicit join），用于连接的两个表仅以逗号相隔，容易让人忽略此处存在连接操作；而即使读者注意到FROM从句中有两个表，也可能因为不熟悉这种不常见的连接方式而无法一下子明白语句的意图。相比之下，语句#2和语句#3就不存在前述问题，因为它通过JOIN和ON关键字显式声明了连接操作。
再来分析语句#4和语句#5，看看ON和WHERE在用于筛选数据时的可读性区别。语句#4把表连接条件和数据筛选条件合并到一起写在WHERE后，会在一定程度上降低两者的区分度；相比之下，读者会更容易判断语句#5的表连接条件是哪个、数据筛选条件是哪个，因为它们分别被写在了ON和WHERE后。
查询结果
同样的连接条件能够通过ON搭配不同的连接类型（内连接、外连接）进而返回不同的结果，语句#2和语句#3的结果说明了这点。而另一方面，通过WHERE声明表连接条件只能实现内连接的效果，如语句#1结果所示。
先分析语句#1-语句#3，看看ON和WHERE在用于表连接时返回结果的区别。可以看出，WHERE用作表连接条件时，只能实现内连接的效果；但是，ON通过搭配不同类型的连接关键字，会返回不同的结果。
再来分析语句#4-语句#7，看看ON和WHERE在用于筛选数据时返回结果的区别。可以看出，两者在内连接的情形下，返回的结果没有区别。而在左外连接的情形下，返回的结果会有区别，ON中的数据筛选条件只会筛选右表的数据，而WHERE中的条件会同时对左右表的数据进行筛选。值得特别注意的是，即使ON中的数据筛选条件（例如语句#6的t1.register_year = 2014）是针对左表写的，左表中数据的去留也不会受影响；这种行为可能出乎一些人的预料，而且平时工作中也很少遇到需要这么写的情况，所以应该尽量避免用ON声明数据筛选条件。
逻辑顺序与执行效率
逻辑上，ON里的条件判断发生在连接之前，而WHERE中的条件判断发生在连接之后。按照这个顺序，无论是表连接还是数据筛选，把条件放在ON里都会更加高效。但是，数据库SQL引擎常常会对查询进行优化、生成执行计划，所以实际执行顺序并不一定与逻辑顺序相同，具体要看使用的是什么DBMS软件。但是一般来说，在做连接时，两者的执行计划（效率）是相同的；但是在做数据筛选时，效率很可能会不同，因此最好进行测试。
结论
综上所述，在选用ON和WHERE时，除非执行效率不可接受，否则只应该使用ON声明表连接条件、使用WHERE声明数据筛选条件。