子查詢

子查詢定義

select 語句嵌套 select 語句.

select ..(select)
from ..(select)
where ..(select)

where 後面加子查詢

select ename, sal from emp where sal > avg(sal);

第一步：select avg(sal) from emp; 第一步：select ename, sal from emp where sal > (select avg(sal) from emp);

from 後面加子查詢（將查詢結果當作臨時表）

select deptno, avg(sal) from emp group by deptno;

第一步：select deptno, avg(sal) from emp group by deptno;

select
        t.deptno, t.avgsal, s.grade
from
        (select deptno, avg(sal) as avgsal from emp group by deptno;) t
join
        s
on
        t.avgsal between s.losal and s.hisal;

select 後面加子查詢

select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;