子查询怎么转换为连接查询

select ename, salary, deptno from emp_xxx a where salary < (select avg(salary) from emp_xxx b where b.deptno = a.deptno);

select a.ename, a.salary, a.deptno from emp_xxx ajoin (select deptno,avg(salary) as avs from emp_xxx group by deptno) bon a.deptno = b.deptnowhere a.salary < b.avs
------解决方案--------------------
SQL code
select a.ename,a.salary,a.deptno from emp_xxx a inner join (select avg(salary) salary,deptno from emp_xxx ) b on b.deptno = a.deptno and a.salary<b.salary
------解决方案--------------------
探讨

好不容易转换为连接，怎么还是有子查询


引用:
SQL code
select a.ename, a.salary, a.deptno
from emp_xxx a
join (select deptno,avg(salary) as avs from emp_xxx group by deptno) b
on a.deptno = b.deptno
where……

------解决方案--------------------
SQL code
;with cte as(SELECT   deptno,          Avg(salary) AS avs             FROM     emp_xxx             GROUP BY deptno)SELECT a.ename,       a.salary,       a.deptnoFROM   emp_xxx a       JOIN  cte b         ON a.deptno = b.deptnoWHERE  a.salary < b.avs
------解决方案--------------------
05以后的写法。sql server
------解决方案--------------------
没说过子查询一定会慢
------解决方案--------------------
这个叫CTE，共同表表达式，是sql好像99以后的标准，就算Oracle、db2也支持的。
------解决方案--------------------
我个人的理解是同时执行，因为是关联的。