[days08] 서브쿼리

where 조건절에 비교할 열이 여러개인 다중열 서브쿼리

문제) 각 부서에서 가장 월급을 많이 받는 사람을 조회하라

--1. rank() 사용
select *
from (
    select emp.*, 
        rank() over(partition by deptno order by sal desc)일등 
    from emp
    )
where 일등=1;

--2. 다중열 서브쿼리
select *
from emp
where (deptno, sal) in (select deptno, max(sal) from emp group by deptno);

with절에 여러 별칭이 올 수 있다 

with e10 as(
    select *
    from emp
    where deptno = 10
    ),
d as(
    select *
    from dept
)
select *
from e10, d
where e10.deptno = d.deptno;

상관서브쿼리

(join 대신에 상관서브쿼리 사용)

select e.*
        ,(select dname from dept where deptno=e.deptno) as dname
        ,(select grade from salgrade where e.sal between losal and hisal) as grade
from emp e;