SQL Join语句中应用on与where的区别

在上一篇文章《数据库SQL语法多种Join类型介绍》中，我们介绍了4种类型的JOIN语句：Inner Join、Left Join、Right Join、Full Join。它们的目的都是基于两个或多个表之间的共同字段，把这些表中的字段通过某种方式结合起来。

我们先抛出一个带有Inner Join语法 以及 ON关系的SQL语句：

SELECT * 
FROM table1 
INNER JOIN table2 
ON table1.column = table2.column;

在这个语法中，ON后面关联着两张表之间的关联条件，是一个或多个逻辑表达式，用来判断两个表中的记录是否相等或满足其他条件。上面这条语句中，会返回两个表中满足ON条件的所有记录，即table1.column和table2.column的值相等的记录。

除了ON之外，我们还可以通过WHERE来关联两张或多个表之间。比如上面那个SQL语句，我们也可以用下面含有WHERE的语句来表示：

SELECT * 
FROM table1, table2 
WHERE table1.column = table2.column;

这条语句会返回两个表中满足WHERE条件的所有记录，即table1.column和table2.column的值相等的记录。上述两个语句（ON和WHERE）的效果是一样的，即返回的记录是一致的，因为它们都是内连接（INNER JOIN）类型，会返回两个表中匹配的记录。但这对于所有Join类型都是一致的效果吗？

在对比说明之前，我们需要明确一点：数据库在通过连接两张或多张表来返回结果记录时，都会生成一张临时表，然后再将这张临时表（部分情况下还会处理下）返回给用户。

而ON 和 WHERE 的区别就主要在于连接的类型和过滤的时机上。ON语法是在生成临时表时使用的条件，它会根据两个表中连接字段来匹配记录，不管条件是否为真，都会返回其中一个表中的记录（返回哪个表取决于连接的类型，left join, right join等）。WHERE语法则是在临时表生成后使用的条件，在临时表生成后，再对临时表进行过滤；它会根据指定的条件来筛选记录，和ON语法不同的是，条件不为真的话就会全部过滤掉。综上所述，两者最大的区别就在于，on 是在连接两个表时起作用，而 where 是在连接完成后起作用。

因此，我们需要对所有类型的JOIN语法：Inner Join、Left Join、Right Join、Full Join 进行个分类，在这4种类型语法中，ON 和 WHERE 的效果都是相同的吗？

Inner Join类内连接

如前文中所举的例子，因为内连接会返回两个表中满足条件的记录，不管条件放在哪里，因此对于内连接（Inner Join），ON和WHERE的效果是一样的。

Left Join类外连接

但对于Left Join，Right Join、Full Join这类外连接类型则不一样；它不管ON中的条件是否满足，都只会返回左表（Left Join）或则右表（Right Join）或则两张表（Full Join）的记录。

我们这里买还是拿Left Join举个简单的例子，假设有两张表。一张是id和学生号。

另外一张是学生号和学生名字的关联关系。

WHERE语句

假如我们执行这条SQL语句
SELECT * FROM table1 LEFT JOIN table2 ON table1.student_id = table2.student_id WHERE table2.student_name='小华'，我们会得到如下结果：

因为它会根据两个表中连接字段来匹配记录，不管ON里的条件是否为真，都会返回其中一个表（这里是Left Join，因此就是左表）中的记录。中间表如下：

接着再对中间表过滤where 条件：table2.student_name=‘小华’，就得到最开始的那张表了。

ON语句

接着，我们执行这条语句：SELECT * FROM table1 LEFT JOIN table2 ON table1.student_id = table2.student_id AND table2.student_name='小华'，那么此时就只会考虑在生成中间表时ON后面条件过滤（table1.student_id = table2.student_id AND table2.student_name=‘小华’）的影响了，此时中间表会变为：

而这也是最终输出的记录。

综上所述，对于对于Left Join，Right Join、Full Join这类外连接类型，因为会返回左表 或 右表 或两张表中的所有记录，即使没有匹配的记录；因此在中间表生成期间过滤条件 还是 中间表生成之后过滤条件就变得不一样了，这也导致ON和WHERE的效果不一样了。

总结

• 如果只是内连接（INNER JOIN）类型的语句，那么ON和WHERE的效果是一样的。因为不管条件放在哪里，内连接只返回两个表中满足条件的记录。
• 如果是外连接（LEFT JOIN，RIGHT JOIN，FULL JOIN）类型的语句，那么ON和WHERE的效果是不一样的。因为外连接会左表 或 右表 或两张表中的所有记录，即使没有匹配的记录；加上，ON是在生成临时表时使用的条件，而WHERE是在临时表生成好后再使用的条件；那么在中间表生成期间过滤条件 还是 中间表生成之后过滤条件就变得不一样了。