11.18.(금) OracleDB(7): 서브쿼리

서브쿼리

서브쿼리란 하나의 쿼리문 안에 들어가있는 내장 쿼리문이다.

DML문은 물론, DDL문에서도 사용되는 만큼 가용 범위가 아주 방대하다.

주로 알려지지 않은 조건에 근거한 값들을 검색하는 SELECT문에서 아주 유용하게 쓰인다.

서브쿼리는 메인쿼리가 실행되기 이전에 한 번 실행된다.

Single-row(단일 행) 서브쿼리

SELECT문으로부터 하나의 행을 검색하는 쿼리문이다.

서브쿼리를 이용하면 좀 더 상세한 조건을 붙여서 SELECT할 수 있다.

-- scott 같은 부서 사원은?
SELECT deptno FROM emp WHERE ename='SCOTT';
SELECT * FROM emp WHERE deptno=20;

이렇게 원래 2개였던 쿼리문을 하나로 합치면

SELECT empno, ename, sal FROM emp
WHERE deptno=(SELECT deptno FROM emp WHERE ename='SCOTT');

이렇게 표현할 수 있다.

또 다른 예시로는,

-- 평균급여 이상을 받는 사원은?
SELECT AVG(sal) FROM emp;
SELECT * FROM emp WHERE sal>2977.1153;

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

활용방법은 굉장히 다양하다.

HAVING절에도 서브쿼리가 들어갈 수 있다.

-- 최소급여가 20번 부서의 최소급여보다 많은 부서의 최소급여를 부서별로 나열하라
SELECT deptno, MIN(sal) FROM emp group by deptno
HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno=20);

Multi-row(다중 행) 서브쿼리

SELECT문으로부터 하나 이상의 행을 검색하는 쿼리문이다.

SELECT empno, ename, sal FROM emp
WHERE sal > (SELECT sal FROM emp WHERE deptno=10); --ERROR:서브쿼리가 하나 이상의 값을 출력

위 경우 서브쿼리에서 하나 이상의 값을 출력하기 때문에 에러가 난다.

-- [문제] 부서별 최고 급여와 같은 급여를 받는 사원을 선택하라
SELECT empno, ename, job, sal, deptno FROM emp
WHERE sal IN (SELECT MAX(sal) FROM emp group by deptno);

ANY

하나의 조건만 만족해도 선택한다.

-- 업무가 'SALESMAN'인 사원의 최소급여보다
-- 많으면서 부서번호가 20번이 아닌 사원의 이름과 급여, 부서코드를 출력하라.
SELECT ename, sal, deptno FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE job='SALESMAN')
and deptno != 20;

ALL

모든 조건을 만족해야 선택한다.

-- 업무가 'SALESMAN'인 사원의 최대급여보다
-- 많으면서 부서번호가 20번이 아닌 사원의 이름과 급여를 출력하라.
SELECT ename, sal FROM emp
WHERE deptno != 20
AND sal > ALL (SELECT sal FROM emp WHERE job='SALESMAN');

최대급여보다 크다는 것은 모든 SALESMAN의 급여보다 크다는 뜻과 같다. 물론 여기서 ALL대신 MAX(sal)을 써도 똑같다.

EXISTS : 서브쿼리가 최소 1개 이상의 데이터를 출력하면 출력, 없다면 출력하지 않는다.

-- 관리자 위치에 있는 사람을 뽑아라
SELECT empno, ename, job, sal FROM emp e
WHERE EXISTS (SELECT * FROM emp WHERE e.empno = mgr);

Multi-column(다중 열) 서브쿼리

서브쿼리에서 선택한 데이터의 칼럼이 2개 이상일때 사용한다.

Pairwise(쌍비교)

서브쿼리가 실행되면서 모든 칼럼들을 검색해서 메인쿼리로 넘겨준다.

-- 사원테이블에서 급여와 보너스가 부서30에 있는 사원의 급여, 보너스와 일치하는 사원의 이름, 부서번호, 급여, 보너스를 출력하라
SELECT ename, deptno, sal, comm FROM emp
WHERE (sal, comm)
IN (SELECT sal, comm FROM emp WHERE deptno = 30 AND comm IS NOT NULL);

-- [문제] 업무별로 최소 급여를 받는 사원의 사번, 이름, 업무, 부서번호를 출력하세요.
-- 단, 업무별로 정렬하세요.
SELECT empno, ename, job, deptno, sal FROM emp e
WHERE (sal, job) IN (SELECT MIN(sal), job FROM emp GROUP BY job)
ORDER BY job;

Nonpairwise(비쌍비교)

서브쿼리도 여러 조건별로 사용된 후 결과값을 메인쿼리로 넘겨준다.

SELECT empno, sal, deptno FROM emp e
WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30 AND comm is NOT NULL)
AND deptno IN (SELECT deptno FROM emp WHERE deptno = 30 AND comm is NOT NULL);

집합쿼리

우리 모두가 수학의정석을 시작할때 가장 먼저 배우는 집합개념과 동일하다.

UNION : 합집합

SELECT deptno FROM emp
UNION 
SELECT deptno FROM dept;

UNION ALL : 공통원소를 중복 포함한 합집합

SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept;

INTERSECT : 교집합

SELECT deptno FROM emp
INTERSECT
SELECT deptno FROM dept;

MINUS : 차집합

SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;