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;