SQL知识：图解SQL中的where和on的区别
SQL知识：图解SQL中的where和on的区别
在SQL查询中，where和on都是用于条件过滤的关键字，但它们在不同类型的连接中使用时会有不同的效果。本文将通过具体的测试样表和图形化的方式，详细解释在内连接、左连接和右连接中，where和on的使用区别。
测试样表
我们新建两张测试表Customers和Orders，表结构如下：
表Customers
表Orders
要求：使用SQL查询广州客户的相关信息(客户ID，姓名，地址，城市，邮编，省份，订单ID)
我们直接写出正确的SQL查询语句：
SELECT a.*,b.订单ID FROM Customers a
LEFT JOIN Orders b ON a.客户ID=b.客户ID
WHERE a.城市='广州'
结果如下：
OK，我们的问题是：
既然ON和WHERE都是过滤筛选条件的，那么能不能将WHERE后面的条件 a.城市='广州' 放在 ON条件后面呢？
这里要分情况来看：
如果是内连接(inner join)是可以直接放ON后面的，与放在WHERE后面的效果是相同的。
我们以下面的例题来讲解：
因为在sql的内连接阶段，左表(a)和右表(b)通过笛卡尔积生成的虚表VT-A1，
VT-A1
在经过内连接后会将虚表VT-A1中符合条件
(a.CustomerID=b.CustomerID)的左右表完成筛选，最后得到虚表VT-A2，如下图
VT-A2
这样就没有添加外部行的动作了，不管其它过滤条件是放在ON后面还是WHERE 后面，都只是对VT-A2进行进一步的过滤，ON和WHERE的效果没有任何差别。
但如果是左右连接则只能放在WHERE后面，这是为什么呢？
回到最开始的例题，我们可以先看下将 a.城市='广州' 条件放到 ON后面看下结果：
SELECT a.*,b.订单ID FROM Customers a
LEFT JOIN Orders b ON a.客户ID=b.客户ID AND a.城市='广州'
结果为：
与你想的结果是不是完全不同？
这是因为在执行sql左连接的时候，是先执行的ON后面的所有条件。
从上述的代码我们可以知道，先将Customers表与Orders表的客户ID进行内连接，这个时候得到的是下面的这个虚表VT-B1的结果
虚表VT-B1
然后再接着加上了一个 and a.城市='广州' 这个条件，那么我们会对上面的这个虚表VT-B1进一步过滤，得到虚表 VT-B1-1
虚表VT-B1-1
执行完虚表VT-B1-1后，根据我们LEFT JOIN的原理，我们是要将左表(Customers表)未关联上的其它所有数据都要添加到虚表VT-B1-1中的，所以在执行完LEFT动作之后，它的结果变成了虚表VT-B2。
这个时候除了虚表VT-B1-1的订单ID非空，其它Customers表中被添加的数据行对应的订单ID均为空。
虚表VT-B2
即我们上述sql代码的错误结果。
那为什么 a.城市='广州' 放在WHERE后面结果又是正确的呢？
因为在LEFT JOIN结束后的阶段后：
SELECT a.*,b.订单ID FROM Customers a
LEFT JOIN Orders b ON a.客户ID=b.客户ID
我们得到的虚表VT-C1是如下这样的：
虚表VT-C1
注意看：
虚表VT-C1与上面错误结果虚表VT-B2的区别，上面的虚表VT-B2在订单ID列，只有城市为广州的非空，而VT-C1只要匹配上客户ID的订单ID均非空
而我们的WHERE条件 a.城市='广州' 就是对VT-C1进行筛选，得到如下虚表VT-C2
虚表VT-C2
因为是最后一步，所以sql的查询分析器会直接将VT-C2的结果返回给查询发起者，所以我们得到的最终正确结果就是虚表VT-C2.
在理解了LEFT JOIN，RIGHT JOIN和INNER JOIN的原理后，再来看WHERE和ON的区别就比较容易理解了。
结论
1、对于内连接(inner join)，sql过滤条件放在where或者on后面没有区别
2、对于左右连接(left/right join)，sql过滤条件放在where或者on后面有很大的区别。
重要声明：本文来自SQL数据库开发，经授权转载，版权归原作者所有，不代表锐成观点，转载的目的在于传递更多知识和信息。