join条件莫乱放

qq群内有人说这两个sql计划一样

select * from t1 left join t2 on t1.id=t2.id                                   left join t3 on t3.id=t2.idand t2.name='Bill' AND t3.id < 6;
select * from t1 left join t2 on t1.id=t2.id and t2.name='Bill'left join t3 on t3.id=t2.id and t3.id < 6;

为了便于理解以上两条可以分别改写如下：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id LEFT JOIN (SELECT * FROM t3 WHERE t3.id < 6) t3 ON (t3.id=t2.idand t2.name='Bill');
SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2 WHERE t2.name='Bill')t2 ON t1.id=t2.id LEFT JOIN (SELECT * FROM t3 WHERE t3.id < 6) t3 ON (t3.id=t2.id);

运行环境如下

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;

create table t1
(
id int primary key,
name nvarchar2(50) not null,
score number not null
); 

insert into t1 values(1,'Aaron',78);
insert into t1 values(2,'Bill',76);
insert into t1 values(3,'Cindy',89);
insert into t1 values(4,'Damon',90);
insert into t1 values(5,'Ella',73);
insert into t1 values(6,'Frado',61);
insert into t1 values(7,'Gill',99);
insert into t1 values(8,'Hellen',56);
insert into t1 values(9,'Ivan',93);
insert into t1 values(10,'Jay',90); 
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE TABLE t3 AS SELECT * FROM t1;

我们来看下计划

SQL> set autotrace traceonly explain;

select * from t1 left join t2 on t1.id=t2.id left join t3 on t3.id=t2.id and t2.name='Bill' AND t3.id < 6;
select * from t1 left join t2 on t1.id=t2.id and t2.name='Bill' left join t3 on t3.id=t2.id and t3.id < 6;

Execution Plan
----------------------------------------------------------
Plan hash value: 133157483

----------------------------------------------------------------------------
| Id  | Operation	    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	10 |  2340 |	 6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER    |	   |	10 |  2340 |	 6   (0)| 00:00:01 |
|*  2 |   HASH JOIN	    |	   |	10 |  1560 |	 4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |	10 |   780 |	 2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |	10 |   780 |	 2   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL | T3   |	 5 |   390 |	 2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T3"."ID"(+)="T2"."ID" AND "T2"."NAME"=SYS_OP_C2C(CASE
	      WHEN "T3"."ID"(+) IS NOT NULL THEN 'Bill' ELSE 'Bill' END ))
   2 - access("T1"."ID"="T2"."ID")
   5 - filter("T3"."ID"(+)<6)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL> 
Execution Plan
----------------------------------------------------------
Plan hash value: 335170654

----------------------------------------------------------------------------
| Id  | Operation	    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	10 |  2340 |	 6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER    |	   |	10 |  2340 |	 6   (0)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |	   |	10 |  1560 |	 4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |	10 |   780 |	 2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T2   |	 1 |	78 |	 2   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL | T3   |	 5 |   390 |	 2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T3"."ID"(+)="T2"."ID")
   2 - access("T1"."ID"="T2"."ID"(+))
   4 - filter("T2"."NAME"(+)=U'Bill')
   5 - filter("T3"."ID"(+)<6)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL> 

下面看运行结果

select count(t1.id) as id1,count(t2.id) as id2,count(t3.id) as t3 from t1 left join t2 on t1.id=t2.id left join t3 on t3.id=t2.id and t2.name='Bill' AND t3.id < 6;
       ID1	  ID2	      T3
---------- ---------- ----------
	10	   10	       1

select count(t1.id) as id1,count(t2.id) as id2,count(t3.id) as t3 from t1 left join t2 on t1.id=t2.id and t2.name='Bill' left join t3 on t3.id=t2.id and t3.id < 6;

       ID1	  ID2	      T3
---------- ---------- ----------
	10	    1	       1

很明显，因t2.name='Bill'位置的关系这是两个不同的sql，走不同的计划，出不同的结果