left join 过滤条件写在on后面和写在where 后面的区别

http://blog.csdn.net/jim110/article/details/6923934

http://blog.csdn.net/muxiaoshan/article/details/7617533

create table t1(id int, feild int);
insert into t1 values(1 , 1);
insert into t1 values(1 , 2);
insert into t1 values(1 , 3);
insert into t1 values(1 , 4);
insert into t1 values(2 , 1);
insert into t1 values(2 , 2);
create table t2(id int, feild int);
insert into t2 values(1 , 1);
insert into t2 values(1 , 2);
insert into t2 values(1 , 5);
insert into t2 values(1 , 6);
insert into t2 values(2 , 1);
insert into t2 values(2 , 3);

 

select t1.*,t2.* from t1 left join t2 on t1.id=t2.id   

--取t1表的第一行,扫瞄t2表,按条件做对比,如果满足条件,就加入返回结果表.
   然后取t1表的第二行,扫瞄t2表,按条件做对比,如果满足条件,就加入返回结果表.
   重复以上过程,直到t1表扫描结束.

select t1.*,t2.* from t1 left join t2 on t1.id=t2.id  and t1.feild=1  

--给左表加条件的时候,左表满足条件的,按上面的过程返回值,左表不满足条件的,直接输出,右表的列补null