一.sql语句的执行顺序
(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_condition>
(10) ORDER BY <order_by_list>
二.测试left join和 where的执行顺序
创建测试表
create table t1(id int,feild int);
create table t2(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); 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
select t1.*,t2.* from t1 left join t2 on t1.id=t2.id and t1.id=1
select t1.*,t2.* from t1 left join t2 on t1.id=t2.id where t1.id=1
select t1.*,t2.* from t1 left join t2 on t1.id=t2.id and t2.id=1
select t1.*,t2.* from t1 left join t2 on t1.id=t2.id where t2.id=1
select t1.*,t2.* from t1 inner join t2 on t1.id=t2.id and t2.id=1
结论：
结论：取t1表的第一行,按谓词‘on’中的条件扫瞄t2表,如果满足条件,就加入返回结果表，不满足条件则只返回t1.
然后取t1表的第二行,按谓词‘on’中的条件扫瞄t2表,如果满足条件,就加入返回结果表，不满足条件则只返回t1.
重复以上过程,直到t1表扫描结束
结论：
结论：取t1表的第一行,按谓词‘on’中的条件扫瞄t1,t2表,如果满足条件,就加入返回结果表，不满足条件则只返回t1.
然后取t1表的第二行,按谓词‘on’中的条件扫瞄t2表,如果满足条件,就加入返回结果表，不满足条件则只返回t1.
重复以上过程,直到t1表扫描结束
结论：
结论：
结论：
结论：