on和where区别以及谓词下推

假如我们有两张表,都有id和name字段,分别是a表和b表

CREATE TABLE a (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

insert into a values(1,'张三'),(2,'李四'),(3,'王二');

CREATE TABLE b (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

insert into b values(1,'zahgnsan'),(2,'李四'),(4,'wanger');

测试语句

select a.*,b.* from
a 
join
b
on a.id=b.id and a.name=b.name

select a.*,b.* from
a 
join
b
on a.id=b.id 
where a.name=b.name

在join中这两个结果是一样的,看不出来什么,好像条件放到on和where都是差不多的
但其实on和where的执行顺序是不一样的
如果是left join就不一样了,left join 首先要返还左边表所有内容,然后再返还符合条件的右边表内容,如果上面的语句换成

select a.*,b.* from
a 
left join
b
on a.id=b.id and a.name=b.name

那么明显,最后的结果应该是a表的全部数据+b表符合a.id=b.id and a.name=b.name的数据,不符合的地方会是null

select a.*,b.* from
a 
left join
b
on a.id=b.id 
where a.name=b.name

这条语句返回的是什么呢,顺序就在这体现了,会先执行on条件去关联出一张表,就像我们上面说的左表全部加上右表符合条件的
然后再筛选where条件符合a.name=b.name的数据
这样结果数据左边如果有不满足的条件的都会被去掉
就会变成下面这样

结果是不是很明显了on和where 是有顺序的吧,如果是where 在on后面那么就是先on去关联出符合条件的全部数据,再用where条件筛选出这些数据中符合where条件的数据

那么我们说的谓词下推是什么东西呢
例如下面一条sql

select a.*,b.* from
a 
left join
b
on a.id=b.id 
where a.name='张三'

这条sql按我们刚才说的应该是先on 在where ,但其实这种非条件关联筛选,只是对数据进行筛选
执行器会把where条件推到靠近数据源的地方先过滤,也就是说这条sql其实等于

select a.*,b.* from
(select* from a where name='张三')a
left join
b
on a.id=b.id 

在join之前其实就已经过滤了,因为你where后面的筛选条件是针对a表的,a表是leftjoin左边的数据,那么其实先去对a表过滤然后在join或者我先join出来结果然后在对左边的数据过滤其实结果是一样的
那么效率上呢,肯定是先过滤在join效率更快吧,因为你左表的数据少了呀,这样你去和右边表join的时候匹配次数是不是少了很多
那如果是这样的sql呢,对右边表的数据过滤呢

select a.*,b.* from
a 
left join
b
on a.id=b.id 
where b.name='zahgnsan'

按照我们的理解如果有谓词下推对吧,这个也应该把条件推到离数据源近的地方,你想想可以下推吗,如果吧条件推到数据源附近也就是join之前过滤他们的最终结果还是一样的吗
想想肯定不一样了吧
因为这个是个leftjoin,如果你吧where中过滤b表的条件推到join之前,那么你出来的结果肯定要把a表所有的数据都展示出来作为左边的数据,然后把符合条件的b表的数据作为右边的数据,不符合显示null

select a.*,b.* from
a 
left join
(select *from b where name='zahgnsan')b
on a.id=b.id 

结果是不是都不一样了,所以在这种leftjoin的情况下,是不会吧where后面的过滤右边表的条件做一个谓词下推的,因为如果做了会改变数据结果,一定是先把符合on条件的数据全查出来,然后在执行where后面的条件

是不是有点懵逼???,继续看

select a.*,b.* from
a 
left join
b
on a.id=b.id and a.name='张三'

现在我们还是对a表进行个条件过滤,但是这过滤条件放到on后面了,那现在还能执行谓词下推吗,答案是不能的,因为你的条件在on里面,如果给你推到了join前面的a表过滤的话,那么结果就像最开始出来的那样,只有符合这个条件的数据像下面这个

select a.*,b.* from
(select* from a where name='张三')a
left join
b
on a.id=b.id 

这样的结果很明显是不对的,因为left join要返回左边表所有的数据,所以刚才那个语句也是不会有谓词下推的,
如果b表的过滤条件放到on后面呢?

select a.*,b.* from
a 
left join
b
on a.id=b.id and b.name='zahgnsan'

那就要想了,如果我把b表的过滤条件放到join前去执行会影响结果吗,答案肯定是不会影响的,因为即使你把b表条件放到join前去执行,最后的结果还是会这样,所以执行器会默认使用谓词下推帮你优化这个语句

select a.*,b.* from
a 
left join
(select *from b where name='zahgnsan')b
on a.id=b.id 

得出结论,只要不改变结果执行器都会帮你默认优化进行谓词下推,至于到底有没有用谓词下推优化,可以自己测试,或者看的懂底层的看看底层
hive的话可以直接看执行计划可以很清楚的看到有谓词下推优化