目录

前言：

一、初始化测试数据：

二、inner join--内关联表：

三、left join--左外关联表（left outer join，outer可以省略） 

1、当把左右表的谓词都写在on后面：

2、当把左表的谓词写在where后面，右表的谓词写在on后面：

3、当把左表的谓词写在on后面，右表的谓词写在where后面：

4、当把左右表的谓词都放在where后面：

四、right join--右外关联表（right outer join，outer可以省略） ：

五、full join-- 全外关联表（full outer join，outer可以省略）：

1、当把左右表的谓词都写在on后面：

2、当把左表的谓词写在where后面，右表的谓词写在on后面：

3、当把左表的谓词写在on后面，右表的谓词写在where后面：

4、当把左右表的谓词都放在where后面：

六、总结：

1、内连接：

2、左外连接：

3、右外连接：

4、全外关联：

---

前言：

        多表关联的连接方式有inner join、left join、right join、full join四种，下面通过实验来说明不同连接方式谓词放在on与where后的效果与影响。

一、初始化测试数据：

--创建表t1和t2
create table t1(id number(10),name varchar2(30),status varchar2(2));
create table t2(id number(10),mobile varchar2(30));
 
--插入数据
insert into t1 values(1,'a','1');
insert into t1 values(2,'b','1');
insert into t1 values(3,'c','1');
insert into t1 values(4,'d','0');
insert into t1 values(5,'e','0');
insert into t2 values(1,'12345');
insert into t2 values(2,'23456');
 
--查询表的数据
select * from t1;
select * from t2;

二、inner join--内关联表：

select t1.id,t1.name,t1.status,t2.id,t2.mobile from t1 inner join t2 on t1.id = t2.id and t1.status = 1 and t2.id <2;
select t1.id,t1.name,t1.status,t2.id,t2.mobile from t1 inner join t2 on t1.id = t2.id and t1.status = 1 where t2.id <2;
select t1.id,t1.name,t1.status,t2.id,t2.mobile from t1 inner join t2 on t1.id = t2.id where t1.status = 1 and t2.id <2;
select t1.id,t1.name,t1.status,t2.id,t2.mobile from t1 inner join t2 on t1.id = t2.id and t2.id <2 where t1.status = 1;

执行结果如下，4种情况得到结果是相同的。

 放在on后与where后结果一样，它们的执行计划相同，说明CBO对这两种情况做了相同处理。

三、left join--左外关联表（left outer join，outer可以省略） 

-- 左右表谓词过滤都放在on后面
select t1.id, t1.name, t1.status, t2.id, t2.mobile
  from t1
  left join t2
    on t1.id = t2.id
   and t1.status = 1
   and t2.id < 2;
-- 左表的谓词写在where后面，右表的谓词写在on后面
select t1.id, t1.name, t1.status, t2.id, t2.mobile
  from t1
  left join t2
    on t1.id = t2.id
   and t2.id < 2
 where t1.status = 1;
-- 左表的谓词写在on后面，右表的谓词写在where后面
select t1.id, t1.name, t1.status, t2.id, t2.mobile
  from t1
  left join t2
    on t1.id = t2.id
   and t1.status = 1
 where t2.id < 2;
-- 左右表谓词过滤都放在where后面
select t1.id, t1.name, t1.status, t2.id, t2.mobile
  from t1
  left join t2
    on t1.id = t2.id
 where t1.status = 1
   and t2.id < 2;

执行结果如下，左关联表就有很大的不同：

1、当把左右表的谓词都写在on后面：

        t1.status=’1’放在on后面，t1表并没有对谓词status进行过滤，结果集显示t1的全表数据。这是由left join的特性决定的，左表会显示全部数据。t2.id<2是先对t2表进行过滤再进行连接，而t1.status=’1’是作为连接条件存在，对连接时产生的笛卡尔积数据做连接过滤。

2、当把左表的谓词写在where后面，右表的谓词写在on后面：

        先对两表进行过滤，再进行left join，显示结果集与写在where后面是不同的，连接方式还是左外连接，显示t1过滤后的全部数据。

3、当把左表的谓词写在on后面，右表的谓词写在where后面：

        这种情况先对t2表过滤，并且t1.id=t2.id，所以会将左连接转换为内连接，左表的谓词条件写在哪个位置都一样。而且因为t2表过滤后数据比t1表少，CBO把t2表当成了驱动表。

4、当把左右表的谓词都放在where后面：

        是先对表进行过滤，然后再对过滤后的数据进行连接。而且我们发现t1表上自动加上了id<2的过滤条件，这是因为有t1.id=t2.id等值连接，如果t1表上id列有索引，性能就能看出差别来了。注意连接方式变成了hash join，这是因为右表的谓词过滤条件写在where后面，CBO会把左连接等价为内连接。 

四、right join--右外关联表（right outer join，outer可以省略） ：

        右连接与左连接是相反的，是右表显示全部数据，写在on后面谓词过滤对右表不起作用。

五、full join-- 全外关联表（full outer join，outer可以省略）：

        全连接在应用中似乎很少碰到，但是存在即合理，此处也举例说明一下。

1、当把左右表的谓词都写在on后面：

        这种情况不会先对两个表过滤，而是作为连接条件过滤，符合连接就匹配上，不符合的就把左右两表的数据都显示出来，另一表的字段以空显示。

2、当把左表的谓词写在where后面，右表的谓词写在on后面：

        这种情况转换为左外连接，也是先对两表过滤后再关联。

3、当把左表的谓词写在on后面，右表的谓词写在where后面：

        这种情况转换为右外连接，但是也是先对两表过滤后再关联。

4、当把左右表的谓词都放在where后面：

        这种情况CBO将其转换为内连接，先过滤再关联。

六、总结：

1、内连接：

关联条件在on后面或者where后面，查询结果一样。

2、左外连接：

左表谓词和右表谓词在on后面：左表全显示，是因为left join特性，右表过滤后再关联。
左表谓词在on后面，右表谓词在where后面：因为右表谓词在where后面，所以要先过滤右表，又因为左右表的关联条件t1.id=t2.id,导致左关联转化成内关联。
左表谓词在where后面，右表谓词在on后面：左右表分别通过谓词过滤，然后关联，显示左表过滤之后的全部数据。
左表谓词和右表谓词在where后面：左右表分别通过谓词过滤，又因为左右表的关联条件t1.id=t2.id,导致左关联转化成内关联。
所以，如果想让左表的谓词起作用，就需要放在where后面，想让右表的谓词起作用，就需要放在on后面。

3、右外连接：

正好和左外连接类似，且相反的效果。

4、全外关联：

不常见，但也介绍一下：
左表谓词和右表谓词在on后面：不过滤，符合连接就匹配，不符合连接就左右表都显示，另一个表为空显示。
左表谓词在on后面，右表谓词在where后面：转化成右连接，先过滤后再关联。
左表谓词在where后面，右表谓词在on后面：转化成左连接，先过滤后再关联。
一句话就是：那边表的谓词在where后面，就是那边的连接，都是先过滤后在关联。
左表谓词和右表谓词在where后面：转换为内连接，先过滤后再关联。