Oracle - Sub Query

2020-04-20

⏱ 12 min.

oracle, sql

서브쿼리란 쿼리 속 쿼리를 의미한다. 서브쿼리를 사용한 쿼리는 실행시 서브쿼리를 먼저 실행된다고 한다.

SQL 퀴즈를 바탕으로 어떻게 사용되는지 알아보자.

1	SMITH와 같은 부서에 근무중인 사원들의 사원번호(empno), 이름(ename), 급여액(sal), 부서이름(dname)을 가져온다.

‘SMITH’ 이름을 갖는 사원의 emp 테이블과 이 사람과 같은 부서에 근무중인 사원들에 대한 emp 테이블, 그리고 부서이름을 가져올 dept 테이블이 필요하다. 따라서 FROM 조건으로 emp e1, emp e2, dept d 3개의 테이블을 조회해야 한다.

SELECT e2.empno, e2.ename, e2.sal, d.dname
FROM emp e1, emp e2, dept d
WHERE e2.deptno = d.deptno 
    AND e1.ename = 'SMITH'
    AND e1.deptno = e2.deptno
ORDER BY e2.empno ASC;

단순히 Join으로도 해결할 수 있는데, 이 쿼리를 서브쿼리로도 작성할 수 있다.

SELECT emp.empno, emp.ename, emp.sal, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno 
    AND dept.deptno = (
        SELECT deptno
        FROM emp
        WHERE ename = 'SMITH'
)
ORDER BY emp.empno ASC;

완전히 동일한 결과이다. 코드 직관성은 서브쿼리가 더 나은것도 같지만, 간단한 데이터 조회여서 그런지 Join이 더 작성하기 쉽기도 했다.

구글에 서브쿼리의 장단점에 대해 검색을 해서 Techwalla라는 웹사이트의 문서를 보니 서브쿼리에 대한 장단점을 다음과 같이 정리했다.

서브 쿼리는 쿼리 구조를 이해하는데 도움이 된다. 단점으로는 테이블을 수정할 수 없고, 동일한 SQL 문에 있는 하위 쿼리 내의 동일한 테이블에서 선택할 수 없다는 것이다. 또한 database optimizer로 인하여 하위 쿼리를 Join보다 실행하는데 시간이 오래 걸릴 수 있다.

서브쿼리를 AND 키워드를 이용하여 다중사용도 가능하다. 아래의 퀴즈를 통해 확인해보자.

1 2	CHICAGO 지역에 근무중인 사원중 BLAKE가 직속상관인 사원들의 사원번호(empno), 이름(ename), 직무(job)를 가져온다.

SELECT empno, ename, job
FROM emp
WHERE deptno = (
		SELECT deptno
  	FROM dept
  	WHERE loc = 'CHICAGO'
) AND mgr = (
		SELECT empno
  	FROM emp
  	WHERE ename = 'BLAKE'
);

지금까지는 서브쿼리에 대한 결과, 데이터 컬럼이 하나일 때만에 해당했다. 그러나 서브쿼리에 대한 결과 데이터 컬럼이 2개 이상 조회된다면 어떨까? 아래의 퀴즈를 풀어보자

1 2	3000 이상의 급여를 받는 사원들과 같은 부서에 근무하고 있는 사원의 사원번호(empno), 이름(ename), 급여(sal)를 가져온다.

SELECT empno, ename, sal
FROM emp
WHERE deptno = (
    SELECT deptno
    FROM emp
    WHERE sal >= 3000
);

3천 이상의 급여를 받는 사원 과 같은 부서(deptno)를 갖는 사원 테이블을 조회하는 쿼리이다. 따라서 3천 이상의 급여를 받는 사원 테이블을 서브쿼리로 조회했고, 이 때 출력되는 결과를 deptno로 가져와서 이걸 where 조건절에 삽입했다. 그러나 결과는..?

에러 메세지를 확인해보면, “single-row subquery returns more than one row” 라며, 한 줄 이상의 데이터가 반환되어 에러가 발생함을 알 수 있다. where 조건절에선 deptno가 하나의 row만 검증하도록 쿼리를 작성했는데, 서브쿼리를 통해 반환되는 row는 2개 이상이었기 때문이다.

확인해보니까 정말 서브쿼리에서 반환되는 데이터가 3개였다. 이 중 어떤 row를 deptno와 비교할지 오라클 데이터베이스가 인식하지 못해서 발생한 에러였던 것이다.

이럴 때는 다음과 같이 in 키워드를 사용하여 서브쿼리에 대한 조건을 충족하는 데이터라면 모두 검증하도록 할 수 있다.

SELECT empno, ename, sal
FROM emp
WHERE deptno in (
    SELECT deptno
    FROM emp
    WHERE sal >= 3000
);

정상적으로 잘 출력된 걸 확인할 수 있다!

DevDatabase