[Oracle SQL] having절 사용하지 않고 그룹 조건 나타내는 방법

from절에서 subquery를 사용해 특정 그룹 집계 결과를 새 필드 값처럼 만들고,

그 값으로 where절에서 조건을 작성한다.

---

-- having절 사용
select d.dname "부서명", ROUND(avg(e.sal),2) "평균 급여"
from dept d, emp e
where d.deptno = e.deptno
group by d.dname
having count(e.empno) >= 2;

그룹 조건을 나타낼 때는 having절을 사용한다고 배웠다.

 

같은 질의를 having절을 사용하지 않고 derived relation(subquery)을 사용해 작성하는 방법은 다음과 같다. 

-- subquery 사용
select d.dname "부서명", ROUND(avg(e1.sal),2) "평균 급여"
from dept d, emp e1, (select count(*) c, deptno from emp group by deptno) e2
where d.deptno = e2.deptno and e2.c>=2 and e1.deptno = e2.deptno
group by d.dname;

emp 테이블에서 deptno으로 그룹화 시키고, 그룹화 후 deptno와 deptno별 그룹의 컬럼의 count를 select한 결과를 e2라고 한다. 이때 count를 재명명해주고, 조건절에서 재명명한 값을 사용해서 그룹 조건을 작성한다. 

where절에서 그룹 조건을 작성하지 못해서 from절에서 subquery를 작성해 부서 별로 그룹화 시키고 각 사원수에 대한 정보를 새 필드 값처럼 만든 것이다. 

 

원본 테이블 emp와 이미 emp를 그룹화 시킨 e2를 조인하는 이유는 ?

먼저 부서 별로 사원들의 수를 구해놓고, 사원이 2명 이상 있는 부서들을 골랐다. 부서의 정보는 연결한 것이다.

각 부서별로 사원의 정보를 조인해서 연결해야 한다. 그래서 그룹화 시킨 e2와 원본 사원 테이블 e1을 조인한다. 

그럼 각 부서별로 사원의 정보가 쭉 연결되고, avg를 이용해 e1.sal에 저장된 값들의 평균을 부서 별로 계산할 수 있다. 

---

-- subquery 사용
SELECT dname, avg_sal
FROM (SELECT dname, COUNT(empno) num_emp, AVG(sal) avg_sal 
           FROM emp JOIN dept ON emp.deptno = dept.deptno
           GROUP BY dname)
WHERE num_emp >= 2;

아마 위 subquery보다 더 좋은 쿼리라고 생각이 든다. 아예 from절에서 emp 테이블을 갖고, 부서별로 사원수와 평균 급여를 계산해놓은 테이블을 만든다. 그리고 부서별 사원수를 조건절에서 이용하고, 평균 급여를 바로 원래 있던 컬럼처럼 검색해서 출력한다.