sql多表关联查询之使用JOIN..ON与where的优化场景实例讲解

先说明原因:

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

关键字:on

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

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

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

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

假设有两张表：

表1：tab2

表2：tab2

两条SQL:
1、select*formtab1leftjointab2on(tab1.size=tab2.size)wheretab2.name=’AAA’
2、select*formtab1leftjointab2on(tab1.size=tab2.sizeandtab2.name=’AAA’)

实战练习

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