关联子查询与嵌套子查询 效率

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

 

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

  大数据下第二种方式效率更高，方法一每条记录都要进行一次select查询与A.SAL做比较.