经常有同事讨论在写SQL时应该把过滤条件写在on上还是写在where上效率高?两者得到的结果是不是一样?在解释这个问题前我们需要先了解SQL的逻辑查询顺序, SQL不是简单的按编码顺序进行处理,尽管Select第一个出现,但第一个被处理的是FROM字句,下面是逻辑查询的步骤序号。
(8) Select (9) DISTINCT (11) <TOP_specification> <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) Where <where_condition> (5) GROUP BY <group_by_list> (6) WITH {CUBE|ROLLUP} (7) HAVING <having_conditon> (10) ORDER BY <order_by_list> |
从这个顺序我们可以看到on和where分别在第二步和第四步执行,中间的第三步做了些什么?我们用下面两个表来解释一下。
|
|
Select 国家名称,城市 FROM 国家表 LEFT JOIN 城市表 ON 国家表.国家名称=城市表.国家 and 国家名称='美国' |
在执行这句SQL前大家先猜想一下结果,很多同学会想结果是一条记录也没有,但上面的语句得到结果如下
国家名称 | 城市 |
中国 | NULL |
美国 | NULL |
是不是有点想不通了。现在我们就分析sql执行的每一步结果,
第一步,将from子句的两个表进行笛卡尔乘积,得到虚拟表XLT1
国家名称 | 国家 | 城市 |
中国 | 中国 | 北京 |
中国 | 英国 | 伦敦 |
美国 | 中国 | 北京 |
美国 | 英国 | 伦敦 |
第二步,应用on筛选器对XLT1进行过滤,“国家表.国家名称=城市表.国家 and 国家名称='美国'”得到虚拟表XLT2,大家可以看到过滤得结果当然是什么记录也没有。
第三步,添加外部行,就是用户根据FULL JOIN、LEFT JOIN、RIGHT JOIN,将保留表的记录添加到XLT2的记录上。我们的语句是“国家表 LEFT JOIN城市表”,就是将国家表设为保留表,国家表的所有记录都要返回,如果在连接表中没有找到匹配项,连接表就用NULL填充,所以第三步得到的虚拟表XLT3就是在XLT2的基础上添加上保留表没有匹配的行,所以得到
国家名称 | 城市 |
中国 | NULL |
美国 | NULL |
如果有where语句,再在XLT3的基础上进行过滤,现在我们可以知道把过滤语句写在where和on上的区别了,就是在where前和on后增加了添加外部行的步骤。如果两个表使用内部连接(INNER JOIN),步骤三将会被跳过,两个筛选器相继被应用,所以在哪指定过滤语句是没有区别的。只有进行外部连接时,两者才有逻辑上的差别。