SQL表连接优化总结
1. innerjoin 内联 返回匹配的数据
select * from tb1 inner join tb2 on tb1.c1=tb2.c1;
或
select * from tb1,tb2 where tb1.c1=tb2.c1;
2. leftjoin 左联 左边返回全部的数据,右边返回匹配的数据,右边没有的数据则返回NULL
select * from tb1 left join tb2 on tb1.c1=tb2.c1order by tb1.c1;
3. rightjion 右联 左边返回匹配的数据,右边返回全部的数据,左边没有的数据则返回NULL
select * from tb1 right join tb2 ontb2.c1=tb1.c1 order by tb2.c1;
4. 半连接 有两个表,只能返回一个表的数据,有两种写法in和exists
select * from tb1 where tb1.c1 in (selecttb2.c1 from tb2);
或
select * from tb1 where exists (selecttb2.c1 from tb2 where tb2.c1=tb1.c1);
对于半连接优化,改写成inner join
seletc * from tb1 inner join tb2 ontb1.c1=tb2.c1;
5. 反连接 与半连接相似,有两种写法notin和notexists;notexists对null值不敏感
select * from tb1 where tb1.c1 not in (selecttb2.c1 from tb2);
或
select * from tb1 where not exists (selecttb2.c1 from tb2 where tb2.c1=tb1.c1);
对于反连接优化,改写成left join
select * from tb1 left join tb2 ontb1.c1=tb2.c1 where tb2.c1 is NULL;
6. 标量子查询改写left join
标量子查询会多次全表扫描
select e.*,(select dname from deptd where d.deptno=e.deptno) as dname from emp e order by dname;
改写成
select e.*,d,name from emp e leftjoin dept d on d.deptno=e.deptno order by d.dname;