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