SQL 的LEFT JOIN 和WHERE的用法分析

这段时间对SQL的操作挺频繁的，先记下这个方法了：

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
                                                                                              1 1 1 1
                                                                                              1 1 1 2
                                                                                              1 1 1 5
                                                                                              1 1 1 6
                                                                                              2 1 2 1
                                                                                              2 1 2 3                  
                                                                                              1 2 NULL NULL
                                                                                              1 3 NULL NULL
                                                                                              1 4 NULL NULL
                                                                                              2 2 NULL NULL

select t1.*,t2.* from t1 left join t2 on t1.id=t2.id  where t1.feild=1     先执行where后连接查询

                                                                                    执行where后表为  1 , 1
                                                                                                            2 , 1
                                                                                          用它来left join t2.
 

 --下面三条语句查询结果是一样的,当为右表加条件的时候,可以把left join 改为inner jin, 因为inner join比left join 要快!

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