[Oracle SQL] 서브쿼리

앞서 배운 조인은 하나 이상의 테이블에서 원하는 데이터를 조회할 때 사용하는 방법이었습니다. 이와 달리 서브쿼리는 하나의 SELECT문만으로 원하는 데이터를 조회할 수 없을 때 사용하는 방법으로, 여러 개의 SELECT문을 한 개의 문장으로 합쳐서 원하는 데이터를 조회할 수 있도록 합니다. 서브쿼리를 잘 활용하면 조인을 이용하지 않고도 조인을 한 것과 같은 결과를 출력할 수 있습니다. 

 

SMITH가 근무하는 부서와 같은 부서의 사원들을 찾고자 할 때, 기존에는 SELECT문이 두 개가 필요했습니다.

SELECT deptno FROM emp WHERE ename='SMITH';

SMITH가 근무하는 부서가 20번임을 확인한 후, 다시 20번 부서에 근무하는 사원을 찾는 것입니다.

SELECT ename,deptno FROM emp WHERE deptno=20;

 

이것을 서브쿼리를 활용하여 한 개의 SELECT문으로 만들면 다음과 같습니다.

SELECT ename,deptno FROM emp WHERE deptno= (   -- 메인쿼리
SELECT deptno FROM emp WHERE ename='SMITH'     -- 서브쿼리
);

서브쿼리가 먼저 실행되어 20이라는 결과값을 도출하고, 이 결과가 메인쿼리로 전달되어 최종적으로 메인쿼리가 실행됩니다. 이렇게 하면 하나의 SELECT문으로 SMITH가 근무하는 부서와 같은 부서의 사원을 조회할 수 있게 됩니다.

 

서브쿼리가 실행되어 반환되는 행의 개수에 따라 단일행 서브쿼리와 복수행 서브쿼리로 구분됩니다. 주의할 점은 단일행 서브쿼리의 경우 반드시 단일행 연산자를, 복수행 서브쿼리의 경우 복수행 연산자를 사용해야 한다는 것입니다.

 

종류	설명	사용 가능 연산자
단일행 서브쿼리	서브쿼리 실행 결과가 한 개의 행을 반환	=, >, >=, <, <=, != 와 같은 비교 연산자
복수행 서브쿼리	서브쿼리 실행 결과가 복수 개의 행을 반환	IN, ANY, ALL, EXIST 연산자

 

 

1. 단일행 서브쿼리

 

서브쿼리가 실행되어 한 개의 행을 반환합니다. 

 

사원들의 평균 월급보다 많이 받는 사원을 조회하는 SQL문입니다.

SELECT ename, sal 
FROM emp 
WHERE sal> (SELECT AVG(sal) FROM emp);

 

30번 부서의 사원들 중 최대 월급을 받는 사원보다 많이 받는 사원을 조회하는 SQL문입니다.

SELECT ename, sal 
FROM emp 
WHERE sal> (SELECT MAX(sal) FROM emp WHERE deptno=30);

 

부서별 최대 월급이 30번 부서의 최대 월급보다 높은 부서를 조회하는 SQL문입니다. 

SELECT deptno, MAX(sal) 
FROM emp
GROUP BY deptno
HAVING MAX(sal)> (SELECT MAX(sal) FROM emp WHERE deptno=30);

 

 

 

2. 복수행 서브쿼리

 

서브쿼리가 실행되어 반환되는 결과가 하나 이상의 행일 때 사용하며, 반드시 복수행 연산자와 함께 사용해야 합니다.

 

종류	설명
IN	서브쿼리 결과값이 복수개이고 메인쿼리와 동등 연산자(=) 방식으로 비교할 때 사용
ANY	> 또는 < 같은 비교 연산자를 사용하고자 할 때 사용하며, 검색 조건이 하나라도 일치하면 참
ALL	> 또는 < 같은 비교 연산자를 사용하고자 할 때 사용하며, 검색 조건의 모든 값이 일치하면 참
EXIST	서브쿼리의 반환값이 존재하면 메인쿼리를 실행하고 존재하지 않으면 실행하지 않음

 

 

2-1. IN 연산자

 

서브쿼리 결과값이 복수개이고 메인쿼리와 동등 연산자(=) 방식으로 비교할 때 사용합니다. 

 

직업별로 최소 월급을 받는 사원의 정보를 출력하는 SQL문입니다. 

SELECT ename, sal, job, deptno 
FROM emp 
WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY job);

 

 

2-2. ALL 연산자

 

복수행 서브쿼리에서 > 또는 < 같은 비교 연산자를 사용하고자 할 때 사용합니다. 

 

종류	설명
> ALL (서브쿼리)	서브쿼리에서 반환된 모든 데이터보다 큰 데이터를 메인쿼리에서 조회 즉 서브쿼리에서 반환된 최대값보다 큰 데이터를 조회
< ALL (서브쿼리)	서브쿼리에서 반환된 모든 데이터보다 작은 데이터를 메인쿼리에서 조회 즉 서브쿼리에서 반환된 최소값보다 작은 데이터를 조회

 

다시 말해, ALL 연산자는 최대값보다 크거나, 최소값보다 작은 데이터를 검색할 때 사용할 수 있습니다.

 

직업이 MANAGER인 사원들의 월급을 검색하면 2450, 2850, 2975라는 3개의 결과값이 나옵니다.

SELECT sal FROM emp WHERE job='MANAGER';

 

직업이 MANAGER인 모든 사원들의 월급보다 많이 받는 사원을 검색하는 SQL문입니다. 이는 월급이 MANAGER인 사원들의 월급의 최대값인 2975보다 큰 사원을 검색하는 것으로서, MAX 함수를 통해 단일행 서브쿼리를 사용한 SQL문과 동일한 결과를 출력하게 됩니다.

SELECT empno, ename, sal 
FROM emp
WHERE sal > ALL (SELECT sal FROM emp WHERE job='MANAGER');

SELECT empno, ename, sal 
FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp WHERE job='MANAGER');

 

직업이 MANAGER인 모든 사원들의 월급보다 적게 받는 사원을 검색하는 SQL문입니다. 마찬가지로 월급이 MANAGER인 사원들의 월급의 최소값인 2450보다 적은 사원을 검색하는 것이므로, MIN 함수를 사용한 값과 동일합니다.

SELECT empno, ename, sal 
FROM emp 
WHERE sal < ALL (SELECT sal FROM emp WHERE job='MANAGER');

SELECT empno, ename, sal 
FROM emp 
WHERE sal < (SELECT MIN(sal) FROM emp WHERE job='MANAGER');

 

 

2-3. ANY 연산자

 

종류	설명
> ANY (서브쿼리)	서브쿼리에서 반환된 데이터 중 하나 이상만 조건이 일치하면 되는 큰 데이터를 메인쿼리에서 조회 즉 서브쿼리에서 반환되는 최소값보다 큰 데이터를 조회
< ANY (서브쿼리)	서브쿼리에서 반환된 데이터 중 하나 이상만 조건이 일치하면 되는 작은 데이터를 메인쿼리에서 조회 즉 서브쿼리에서 반환되는 최대값보다 작은 데이터를 조회

 

직업이 MANAGER인 사원들의 월급 중 최소값보다 월급을 많이 받는 사원을 출력하는 SQL문입니다.

SELECT empno, ename, sal 
FROM emp 
WHERE sal > ANY (SELECT sal FROM emp WHERE job='MANAGER');

 

직업이 MANAGER인 사원들의 월급 중 최대값보다 월급을 적게 받는 사원을 출력하는 SQL문입니다.

SELECT empno, ename, sal 
FROM emp 
WHERE sal < ANY (SELECT sal FROM emp WHERE job='MANAGER');

 

 

2-4. EXISTS 연산자

 

서브쿼리에서 검색된 결과가 하나라도 존재하면 메인쿼리를 실행하고, 결과가 하나도 없으면 메인쿼리를 실행하지 않습니다. 

 

다음과 같이 커미션이 null이 아닌 사원이 존재하는 경우, 메인쿼리를 실행합니다.

SELECT * FROM emp 
WHERE EXISTS (SELECT empno FROM emp WHERE comm IS NOT NULL);

 

월급을 500000 보다 많이 받는 사원이 없기 때문에 메인쿼리가 실행되지 않습니다.

SELECT ename, deptno, sal 
FROM emp 
WHERE EXISTS (SELECT empno FROM emp WHERE sal > 500000);

 

 

 

3. 다중 컬럼 서브쿼리

 

서브쿼리에서 여러 개의 컬럼값을 검색하여 메인쿼리의 조건절과 비교합니다. 컬럼을 쌍으로 묶어서 동시에 비교하는 pairwise 방식과, 컬럼별로 나누어 비교하고 나중에 AND로 연산하는 unpairwise 방식이 있습니다.

 

부서별로 가장 많은 월급을 받는 사원의 정보를 출력하기 위한 서브쿼리를 pairwise 방식으로 작성해 보겠습니다.

SELECT ename, deptno, sal 
FROM emp
WHERE (deptno, sal) IN (SELECT deptno, MAX(sal) 
                       FROM emp 
                       GROUP BY deptno);

 

unpairwise 방식으로도 작성해 볼 수 있습니다.

SELECT ename, deptno, sal FROM emp
    WHERE deptno IN (SELECT deptno FROM emp GROUP BY deptno)
    AND sal IN (SELECT MAX(sal) FROM emp GROUP BY deptno);

 

 

 

4. 인라인 뷰 (in-line view)

 

지금까지 알아본 서브쿼리는 주로 WHERE절에서 사용하는 서브쿼리였습니다. 하지만 FROM절에도 서브쿼리를 사용할 수 있으며, FROM절에 사용하는 서브쿼리를 인라인 뷰라고 합니다. 여기서는 서브쿼리가 실행된 결과를 가상 테이블로서 사용하게 됩니다. FROM절에서 참조하는 테이블의 크기가 클 경우, 필요한 행과 컬럼을 추출하여 만든 가상 테이블을 대신 사용하면 효율적인 처리가 가능해집니다. 

 

emp 테이블에서 부서별 월급 합계, 평균, 인원수를 구한 결과를 출력하는 SQL문입니다.

SELECT deptno, SUM(sal) 합계, AVG(sal) 평균, COUNT(*) 인원수
FROM emp
GROUP BY deptno;

 

 

이 결과를 하나의 가상 테이블로 만들어서 e 라는 별칭을 부여하고 이를 dept 테이블과 조인할 수 있습니다.

SELECT e.deptno, dname, 합계, 평균, 인원수
FROM (SELECT deptno, SUM(sal) 합계, AVG(sal) 평균, COUNT(*) 인원수
      FROM emp
      GROUP BY deptno) e, dept d WHERE e.deptno = d.deptno
ORDER BY 1;

 

 

이번 글에서는 서브쿼리에 대한 내용을 총정리해 보았습니다. 

 

지금까지 알아본 내용은 모두 SELECT문, 즉 DQL(질의어)에 대한 내용이었습니다. 다음 글에서는 DML(조작어)에 대해서 알아보도록 하겠습니다.