关联子查询与嵌套子查询[转]

今天优化了一个sql语句，感觉速度好像是快了点，自己想想觉得也是，下面给出类似的例子，工作中的表就不拿来举例了。实际我们平常都在凭着自己的感觉在写SQL，其实跳出那个圈子你会发现能写出更好的。

一、先给出我的表和数据，这里数据量少，可能不明显，只是表明一下这个意思！

[sql] view plaincopyprint?
  1. create table EMP  
  2. (  
  3.   EMPNO    NUMBER(4) not null,  
  4.   ENAME    VARCHAR2(10),  
  5.   JOB      VARCHAR2(9),  
  6.   MGR      NUMBER(4),  
  7.   HIREDATE DATE,  
  8.   SAL      NUMBER(7,2),  
  9.   DEPTNO   NUMBER(2)  
  10. );  
  11. insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)  
  12. values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, 20);  
  13. insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)  
  14. values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 30);  
  15. insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)  
  16. values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 30);  
  17. insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)  
  18. values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, 20);  
  19. insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)  
  20. values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 30);  
  21. insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)  
  22. values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, 30);  
  23. insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)  
  24. values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, 10);  
  25. insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)  
  26. values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, 20);  
  27. insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)  
  28. values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 30);  
  29. insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)  
  30. values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, 20);  
  31. insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)  
  32. values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, 30);  
  33. insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)  
  34. values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, 20);  
  35. insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)  
  36. values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, 10);  
  37. commit;  

 

二、假设有这样一个需求，我要得到这个表中所有低于所在部门平均工资的员工的基本信息。很自然的 我们会写出这样的SQL：

SELECT *  
  1.   FROM emp A  
  2.  WHERE A.sal < (SELECT AVG(sal) FROM emp B WHERE A.deptno = B.deptno);  

 

 

这种写法是很通常的写法，也很好理解，从字面上看都知道是什么意思！但是在一个很大的表中这样来统计是很慢很慢的，对于每一条记录都要嵌套的查询一个子查询，这样对性能影响是很大的。

三、为什么不跳出这个思维的定式，换一种方法来统计呢，我给出下面的方法：

    sSELECT A.*  
  1.   FROM emp A, (SELECT deptno, AVG(sal) sal FROM emp GROUP BY deptno) B  
  2.  WHERE A.deptno = B.deptno  
  3.    AND A.sal < B.sal;  

 

这里没有使用嵌套子查询，而是使用了关联子查询 ，这样实际上也是很好理解的，但是往往我们很少这样写。

四、总的来说我还是很喜欢下面的写法，在数据量很大的情况下，对性能的提高真的不少，但是在数据量小的情况下似乎看不出什么效果。实际上也是，如果数据量小，根本不会涉及到优化，我在这里说也没什么用。如果觉得自己的子查询有问题，看看能不能把嵌套子查询转化为关联子查询，效果还是挺明显的。

posted @ 2012-03-08 14:33  daniel.deng  阅读(333)  评论(0)    收藏  举报