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对这两种情况做了相同处理。
执行计划如下图所示:
image.png

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

执行计划如下:
image.png
从执行计划可以看出,t1.status=’1’放在on后面,t1表并没有对谓词status进行过滤,结果集显示t1的全表数据。这是由left join的特性决定的,左表会显示全部数据。t2.id[3是先对t2表进行过滤再进行连接,而t1.status=’1’是作为连接条件存在,对连接时产生的笛卡尔积数据做连接过滤。
(2)左右表谓词过滤都放在where后面:
SQL](3%E6%98%AF%E5%85%88%E5%AF%B9t2%E8%A1%A8%E8%BF%9B%E8%A1%8C%E8%BF%87%E6%BB%A4%E5%86%8D%E8%BF%9B%E8%A1%8C%E8%BF%9E%E6%8E%A5%EF%BC%8C%E8%80%8Ct1.status=%E2%80%991%E2%80%99%E6%98%AF%E4%BD%9C%E4%B8%BA%E8%BF%9E%E6%8E%A5%E6%9D%A1%E4%BB%B6%E5%AD%98%E5%9C%A8%EF%BC%8C%E5%AF%B9%E8%BF%9E%E6%8E%A5%E6%97%B6%E4%BA%A7%E7%94%9F%E7%9A%84%E7%AC%9B%E5%8D%A1%E5%B0%94%E7%A7%AF%E6%95%B0%E6%8D%AE%E5%81%9A%E8%BF%9E%E6%8E%A5%E8%BF%87%E6%BB%A4%E3%80%82%0A(2)%E5%B7%A6%E5%8F%B3%E8%A1%A8%E8%B0%93%E8%AF%8D%E8%BF%87%E6%BB%A4%E9%83%BD%E6%94%BE%E5%9C%A8where%E5%90%8E%E9%9D%A2:%0ASQL)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

image.png
从执行计划可以看出,谓词放在where后面,是先对表进行过滤,然后对过滤后的数据再进行连接。而且我们发现t1表上自动加上了id<3的过滤条件,这是因为有t1.id=t2.id等值连接,如果t1表上id列有索引,性能就能看出差别来了。注意连接方式变成了hash join,其实右表的谓词过滤条件写在where后面,cbo会把左连接等价为内连接。
(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

image.png
当把对右表的过滤写在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

image.png
从执行计划看这种情况左联接转换为内连接,左表的谓词条件写在哪个位置都一样。而且因为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

image.png
从执行计划看出,虽然t2表返回0行,步骤3上的filter条件肯定不成立,但有逻辑读消耗,所以推断它依然进行了全表扫描,所以这种语句对t2表的扫描是对资源的一种浪费,没有意义。或许你会觉得谁会这么无聊写这种sql,但是在开发中,经常是各种过滤条件组合经过拼接而成,因为返回结果是对的,他们意识不到会出现这种组合,在此说明此种情况主要是想说明一件事,不要总想着用一个语句来解决所有的功能需求,适当的拆分对性能的提升是很有必要的。

3.right join

右连接与左连接是相似的,只不过是右表显示全部数据,on后面的对右表的谓词过滤对右表不起作用,在此不再举例说明。

4.full join

全连接在应用中似乎很少碰到,但是存在即合理,只是自己没有遇到而已。
(1)两个表的谓词都放在on的后面:
image.png
image.png
这种情况不会先对两个表过滤,而是作为连接条件过滤,符合连接就匹配上,不符合的就左右两表的数据都显示出来,另一表的字段以空显示。
(2)两个表的谓词都放在where后面:
image.png
这种情况cbo将其转换为内连接,先过滤再关联。
(3)左表谓词放在on后面,右表放在where后面:
image.png
这种情况转换为右外连接,但是也是先对两表过滤后再关联。
(4)左表谓词放在where后面,右表放在on后面:
image.png
这种情况转换为左外连接,也是先对两表过滤后再关联。

总结:
1.对于内连接inner join,两个表的谓词条件放在on与where后面相同。
2.对于left join:
左表谓词放在on后不会对左表数据进行过滤,依然显示左表全部数据,放在where后面
才会对左表进行过滤
右表谓词不管放在on后还是where后都会对右表先过滤再连接,但是放在where后left
join会转换为inner join。
3.对于外连接,谓词条件放的位置不同,结果集也不同,可以根据自己的需求斟酌使用。

最后修改:2021 年 10 月 02 日
如果觉得我的文章对你有用,请随意赞赏