1.举个例子,就清楚了。两个表tb_userstat_all,tb_userstat_imeiimsi_all
a
select * from tb_userstat_all as a left join tb_userstat_imeiimsi_all as b on a.sdate = b.sdate and b.sdate BETWEEN '20151222' and '20151225';
b
select * from tb_userstat_all as a left join tb_userstat_imeiimsi_all as b on a.sdate = b.sdate and a.sdate BETWEEN '20151222' and '20151225';
c
select * from tb_userstat_all as a left join tb_userstat_imeiimsi_all as b on a.sdate = b.sdate where a.sdate BETWEEN '20151222' and '20151225';
d
select * from tb_userstat_all as a left join tb_userstat_imeiimsi_all as b on a.sdate = b.sdate where b.sdate BETWEEN '20151222' and '20151225';
查询结果是:
a:300条记录
b:300条记录
c:2条记录
d:2条记录
结论:
left join on后面的条件对主表不起作用。主表仍然会查询出所有的记录,但是 on 后面的条件 会对从表有影响。
where 后面跟的条件 是将连接查询后的结果 进行筛选。
因此,根据需求灵活的运用 on 还是 where.