Sql优化过程中，发现开发在写多表关联查询的时候，对于谓词过滤条件的写法很随意，写在on后面与where后面的情况均有，有时可能没有理解清楚其真正的含义会无法得到期望的结果。多表关联连接方式有inner join、left join、right join、full join，下面通过实验来说明针对不同连接方式谓词放在on与where后的效果与影响。
初始化测试数据
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','1');
insert into t1 values(5,'e','1');
insert into t1 values(6,'f','0');
insert into t1 values(7,'g','0');
insert into t1 values(8,'h','0');
insert into t1 values(9,'i','0');
insert into t1 values(10,'j','0');
insert into t2 values(1,'12345');
insert into t2 values(2,'23456');
insert into t2 values(3,'34567');
insert into t2 values(6,'67890');
insert into t2 values(7,'78901');
1.Inner join
SQL>select * from t1 inner join t2 on t1.id=t2.id and t1.status=‘1’;
ID NAME ST ID MOBILE
1 a 1 1 12345
2 b 1 2 23456
3 c 1 3 34567
SQL> select * from t1 inner join t2 on t1.id=t2.id where t1.status=‘1’;
ID NAME ST ID MOBILE
1 a 1 1 12345
2 b 1 2 23456
3 c 1 3 34567
我们发现谓词t1.status=’1’放在on后与where后结果一样，它们的执行计划相同，说明cbo对这两种情况做了相同处理。
执行计划如下图所示：
Inner join时谓词不管放在哪个位置，cbo先对t1表过滤，再与t2表关联。
# 2.left join
(1)左右表谓词过滤都放在on后面：
SQL> select * from t1 left join t2 on t1.id=t2.id and t1.status=‘1’ and t2.id<3;
ID NAME ST ID MOBILE
1 a 1 1 12345
2 b 1 2 23456
3 c 1
8 h 0
5 e 1
9 i 0
10 j 0
7 g 0
6 f 0
4 d 1
执行计划如下：
从执行计划可以看出，t1.status=’1’放在on后面，t1表并没有对谓词status进行过滤，结果集显示t1的全表数据。这是由left join的特性决定的，左表会显示全部数据。t2.id<3是先对t2表进行过滤再进行连接，而t1.status=’1’是作为连接条件存在，对连接时产生的笛卡尔积数据做连接过滤。
(2)左右表谓词过滤都放在where后面:
SQL>select * from t1 left join t2 on t1.id=t2.id where t1.status=‘1’ and t2.id<3;
ID NAME ST ID MOBILE
1 a 1 1 12345
2 b 1 2 23456
从执行计划可以看出，谓词放在where后面，是先对表进行过滤，然后对过滤后的数据再进行连接。而且我们发现t1表上自动加上了id<3的过滤条件，这是因为有t1.id=t2.id等值连接，如果t1表上id列有索引，性能就能看出差别来了。注意连接方式变成了hash join，其实右表的谓词过滤条件写在where后面，cbo会把左连接等价为内连接。
2021-12-20日后续补充加入：
如果出现右表的谓词过滤条件是：某个列是 is null的过滤，或者is null or上其他条件，这样就都还是外连接，不会等价为内连接。
下面看举例：
例子中过滤条件为t2.mobile is null or t2.id=7 ,执行计划显示进行的仍然是外连接，这些过滤条件是在连接后是对连接后的结果进行过滤的。
为什么是这样呢？因为where右表某个列is null 意味着要么是取没关联上的数据、要么是关联上的数据里该列为null值，所以这个条件必须等关联之后才能去过滤，而关联时只能是使用外连接来关联。
(3)右表的谓词写在on后面，左表的谓词写在where后面：
SQL>select * from t1 left join t2 on t1.id=t2.id and t2.id<3
where t1.status=‘1’; 2
ID NAME ST ID MOBILE
1 a 1 1 12345
2 b 1 2 23456
5 e 1
4 d 1
3 c 1
当把对右表的过滤写在on后面，先对两表进行过滤，再进行left join，显示结果集与写在where后面是不同的，连接方式还是左外连接，显示t1过滤后的全部数据。
(4)右表的谓词写在where后面，左表的谓词写在on后面：
SQL> select * from t1 left join t2 on t1.id=t2.id and t1.status=‘1’ where t2.id<7;
ID NAME ST ID MOBILE
1 a 1 1 12345
2 b 1 2 23456
3 c 1 3 34567
从执行计划看这种情况左联接转换为内连接，左表的谓词条件写在哪个位置都一样。而且因为t2表过滤后数据比t1表少，cbo把t2表当成了驱动表。
接下来我们再看一个语句：
SQL> select * from t1 left join t2 on t1.id=t2.id and t1.status=‘1’
where t1.status=‘0’ ;
ID NAME ST ID MOBILE
8 h 0
6 f 0
9 i 0
10 j 0
7 g 0
从执行计划看出，虽然t2表返回0行，步骤3上的filter条件肯定不成立，但有逻辑读消耗，所以推断它依然进行了全表扫描，所以这种语句对t2表的扫描是对资源的一种浪费，没有意义。或许你会觉得谁会这么无聊写这种sql，但是在开发中，经常是各种过滤条件组合经过拼接而成，因为返回结果是对的，他们意识不到会出现这种组合，在此说明此种情况主要是想说明一件事，不要总想着用一个语句来解决所有的功能需求，适当的拆分对性能的提升是很有必要的。
3.right join
右连接与左连接是相似的，只不过是右表显示全部数据，on后面的对右表的谓词过滤对右表不起作用，在此不再举例说明。
4.full join
全连接在应用中似乎很少碰到，但是存在即合理，只是自己没有遇到而已。
（1）两个表的谓词都放在on的后面：
这种情况不会先对两个表过滤，而是作为连接条件过滤，符合连接就匹配上，不符合的就左右两表的数据都显示出来，另一表的字段以空显示。
（2）两个表的谓词都放在where后面：
这种情况cbo将其转换为内连接，先过滤再关联。
（3）左表谓词放在on后面，右表放在where后面：
这种情况转换为右外连接，但是也是先对两表过滤后再关联。
（4）左表谓词放在where后面，右表放在on后面：
这种情况转换为左外连接，也是先对两表过滤后再关联。
总结：
1.对于内连接inner join，两个表的谓词条件放在on与where后面相同。
2.对于left join：
左表谓词放在on后不会对左表数据进行过滤，依然显示左表全部数据，放在where后面
才会对左表进行过滤
右表谓词不管放在on后还是where后都会对右表先过滤再连接，但是放在where后left
join会转换为inner join。
3.对于外连接，谓词条件放的位置不同，结果集也不同，可以根据自己的需求斟酌使用。
