学步园

经常有同事讨论在写SQL时应该把过滤条件写在on上还是写在where上效率高？两者得到的结果是不是一样？在解释这个问题前我们需要先了解SQL的逻辑查询顺序， SQL不是简单的按编码顺序进行处理，尽管Select第一个出现，但第一个被处理的是FROM字句，下面是逻辑查询的步骤序号。

      从这个顺序我们可以看到on和where分别在第二步和第四步执行，中间的第三步做了些什么？我们用下面两个表来解释一下。

　　在执行这句SQL前大家先猜想一下结果，很多同学会想结果是一条记录也没有，但上面的语句得到结果如下

　　是不是有点想不通了。现在我们就分析sql执行的每一步结果，
第一步，将from子句的两个表进行笛卡尔乘积，得到虚拟表XLT1

第二步，应用on筛选器对XLT1进行过滤，“国家表.国家名称=城市表.国家 and 国家名称='美国'”得到虚拟表XLT2，大家可以看到过滤得结果当然是什么记录也没有。
第三步，添加外部行，就是用户根据FULL JOIN、LEFT JOIN、RIGHT JOIN，将保留表的记录添加到XLT2的记录上。我们的语句是“国家表 LEFT JOIN城市表”，就是将国家表设为保留表，国家表的所有记录都要返回，如果在连接表中没有找到匹配项，连接表就用NULL填充，所以第三步得到的虚拟表XLT3就是在XLT2的基础上添加上保留表没有匹配的行，所以得到

如果有where语句，再在XLT3的基础上进行过滤，现在我们可以知道把过滤语句写在where和on上的区别了，就是在where前和on后增加了添加外部行的步骤。如果两个表使用内部连接（INNER JOIN），步骤三将会被跳过，两个筛选器相继被应用，所以在哪指定过滤语句是没有区别的。只有进行外部连接时，两者才有逻辑上的差别。