sql多表关联查询使用JOIN..ON与where的优化场景

74 阅读 0 评论 49 点赞

我是靠谱客的博主激情猎豹，最近开发中收集的这篇文章主要介绍sql多表关联查询使用JOIN..ON与where的优化场景，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

先说明原因:

优化的本质就是(join on 和where的执行顺序）

关键字: on

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

表1：tab2

id   size
1   10
2   20
3   30
表2：tab2

size   name
10   AAA
20   BBB
20   CCC
两条SQL:
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

第一条SQL的过程：

1、中间表
on条件:
tab1.size = tab2.size
tab1.id   tab1.size   tab2.size   tab2.name
1   10   10   AAA
2   20   20   BBB
2   20   20   CCC
3   30   (null)   (null)

2、再对中间表过滤
where 条件：
tab2.name=’AAA’
tab1.id   tab1.size   tab2.size   tab2.name
1   10   10   AAA

第二条SQL的过程：

1、中间表
on条件:
tab1.size = tab2.size and tab2.name=’AAA’
(条件不为真也会返回左表中的记录)
tab1.id   tab1.size   tab2.size   tab2.name
1   10   10   AAA
2   20   (null)   (null)
3   30   (null)   (null)

实战练习

1.首先先建立两个表

create table table1
(
mon varchar2(14),
dep number(4),
yj number(4)
)
insert into table1 values ('一月份',1,10);
insert into table1 values ('一月份',2,10);
insert into table1 values ('一月份',3,5);
insert into table1 values ('二月份',2,8);
insert into table1 values ('二月份',4,9);
insert into table1 values ('二月份',3,8);

create table table2
(
dep number(4),
dname varchar2(30)
)

insert into table2 values(1,'国内业务一部');
insert into table2 values(2,'国内业务二部');
insert into table2 values(3,'国内业务三部');
insert into table2 values(4,'国际业务部');
2.要求查出每个部门一月份,二月份的业绩

查出结果如下

3 用以下where语句增加最后的过滤条件在特定场景下肯定是可以的 (但是鉴于这里是要1~4月全部展示,所以where子句还是不符合条件,在这里列出是为了说明where是在on条件执行之后增加过滤条件)

select t0.dep as dep,t0.dname as 部门名称 , t1.yj as 一月份业绩 , t2.yj as 二月份业绩
from table2 t0
left join table1 t1 on t0.dep=t1.dep
left join table1 t2 on t0.dep=t2.dep
where t1.mon='一月份' and t2.mon='二月份'
order by t0.dep
4. 接下来把where条件去掉看查出来的数据有10条,可以发现这其中没有 t1.mon='一月份' and t2.mon='二月份'的限定条件,

table2表的国际业务一部 (dep=1)与 table1表的一月份(dep=1)的业绩为10(yj=10) 匹配2次,分别匹配出一条一月份的业绩和二月份的业绩 ,因为国际业务一部二月份么有业绩,所以这明显是错误的匹配 (增加了额外的中间表数量)

5. 如果能在匹配之前先对应加上t1.mon='一月份' 或者 t2.mon='二月份'的限定条件,如果没有满足限定条件,后面的t0.dep=t1.dep就不会执行,这样就会至少在中间表中会少一条数据, 假如再使用where过滤就会减少过滤操作数量

select t0.dep as dep,t0.dname as 部门名称 , t1.yj as 一月份业绩 , t2.yj as 二月份业绩
from table2 t0
left join table1 t1 on t1.mon='一月份' and t0.dep=t1.dep
left join table1 t2 on t2.mon='二月份' and t0.dep=t2.dep
order by t0.dep

参考文章:https://www.cnblogs.com/wlzhang/p/4532587.html

另附提高SQL查询效率的30种方法传送门

https://www.cnblogs.com/lykbk/p/aefweere45454545454.html
————————————————
版权声明：本文为CSDN博主「Master_Shifu_」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Master_Shifu_/article/details/80585096