[SQL][TIL] Correlated Subquery, Scalar Subquery

※ Correlated Subquery : subquery안에 join을 사용한 것

※ Correlated Subquery 에서는 ★후보행★을 이해하는 게 중요 !!

Q_ 10번 부서에서 근무하면서, 10번 부서의 평균 급여보다 많은 급여를 받는 사원 정보

select *
from emp e
where deptno = 10
and sal > (select avg(sal)
           from emp
           where deptno = 10);

 

Q_ 20번 부서에서 근무하면서, 20번 부서의 평균 급여보다 많은 급여를 받는 사원 정보

select *
from emp e
where deptno = 20
and sal > (select avg(sal)
           from emp
           where deptno = 20);

 

Q_ 위의 두 문제를 한번에 출력하세요

1. 우선, alias를 붙여보기

select m.empno, m.ename, m.sal, m.deptno, m.mgr
from emp m
where m.deptno = 10
and m.sal > (select avg(s.sal)
             from emp s
             where s.deptno = 10);

2. main query와 sub query를 join

select m.empno, m.ename, m.sal, m.deptno, m.mgr
from emp m
where m.deptno = 10
and m.sal > (select avg(s.sal)
             from emp s
             where s.deptno = m.deptno);

// m테이블의 deptno컬럼의 모든 값들이 끌어와진다. 

// m테이블의 deptno컬럼의 모든 값들이 '후보행'이 된다.

3. 필요없는 문장 제거

select m.empno, m.ename, m.sal, m.deptno, m.mgr
from emp m
                              
where m.sal > (select avg(s.sal)
               from emp s
               where s.deptno = m.deptno);

 

 

Q_ 부서원이 4명 이상인 부서들의 정보를 모두 출력하세요. (correlatied subquery)

select *
from dept m
where 4 <= (select count(s.deptno)
            from emp s
            where s.deptno = m.deptno);

 

 

Q_ 부서마다 부서원의 수를 출력하시오! (join)

select *
from dept;
+ (join)
select deptno, count(*)
from enp
group by deptno;

 

방법1) 그냥 join   =>   emp 테이블의 모든 정보를 다 가져온다  =>  비효율적

select d.deptno, d.dname, d.loc, count(e.ename) cn
from emp e, dept d
where e.deptno = d.deptno
group by d.deptno, d.dname, d.loc;

 

방법2) subquery 이용한 join   =>   subquery를 이용해서 emp 테이블의 필요한 정보를 한번만 가져온다.

select *
from dept d, (select deptno, count(*)
              from emp 
              group by deptno) e
where d.deptno = e.deptno;

 

 

※ scalar subquery : select list에 subquery가 사용된 것

※ scalar subquery는 반드시 subquery의 출력 결과가 단일행이어야 한다.

select d.*, (select count(*)
             from emp
             where deptno = d.deptno) as cnt 
from dept d;