DBMS 서브쿼리 종류

단일 행

1. 실행 결과가 하나의 컬럼, 하나의 행을 리턴
2. 그 결과와 비교를 해야하기 때문에 =, <, >, <=, >= 같은 비교 연산자 사용
3. 여러 개의 결과를 리컨하는 경우 에러 발생
4. 메인 쿼리문 안에 서브 쿼리문이 들어간다.
5. SELECT문의 WHERE 절에 그룹함수를 사용하고 싶을 때 서브 쿼리를 이용한다.

--emp테이블과 dept테이블에서 부서 중 평균 급여가 가장 높은 부서의 이름과 평균 급여를 출력
SELECT DNAME, AVG(SAL)
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
GROUP BY DNAME
HAVING AVG(SAL) = (SELECT MAX(AVG(SAL))
FROM EMP
GROUP BY DEPTNO);

 

 

에러 발생 경우

서브 쿼리문이 여러 개의 결과를 리턴하는 경우

SELECT ename, sal
FROM emp
WHERE sal = (SELECT sal FROM emp
WHERE deptno=10);

=> 수정 =>

SELECT ename, sal
FROM emp
WHERE sal = ANY(SELECT sal FROM emp
WHERE deptno=10);

 

메인 쿼리문과 서브 쿼리문의 컬럼 수가 일치하지 않는 경우

--부서 번호가 10인 사람들의 급여와 같은 사람들의 이름과 급여

SELECT ename, sal
FROM emp
WHERE sal = (SELECT sal, comm
FROM emp
WHERE deptno=10);


=> 수정 =>

SELECT ename, sal
FROM emp
WHERE sal = ANY(SELECT sal
FROM emp
WHERE deptno=10);

 

서브쿼리문의 WHERE 절에 복수 행 함수를 사용할 경우

-- 급여가 전체평균보다 많은 사원들의 이름과 급여를 출력

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

=>수정=>

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

 

SELECT 문의 WHERE에서 그룹 함수를 사용할 때

SELECT ENAME, SAL
FROM EMP 
WHERE SAL > min(SAL);

=>수정=>

SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT MIN(SAL)
FROM EMP);

 

 

 

---

복수 행

1. 실행 결과가 여러 개의 행을 리턴해주는 경우
2. IN, ANY, SOME, ALL, EXISTS 사용
3. IN : 서브쿼리문에 의해 리턴되는 값 중에서 조건에 해당하는 행이 있으면 출력
4. ANY : 서브쿼리문에 의해 리턴되는 값 중에서 하나 이상을 만족하는 행이 있으면 출력
5. ALL : 서브쿼리문에 의해 리턴되는 값이 조건을 만족하면 출력
6. EXISTS : 서브쿼리문에 의해 리턴되는 값 중에서 조건을 만족하는 행이 존재하면 출력

--부서별 최고 연봉자의 이름과 급여 출력
SELECT ENAME, SAL
FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP
GROUP BY DEPTNO);


--부서번호가 30인 사원들의 직무를 선택하고 그 직무들과 같은 사원들의 사원번호, 이름, 직무가 나와라
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE JOB IN(SELECT JOB FROM EMP
WHERE DEPTNO = 30);


--직무가 MANAGER인 사람의 급여에서 제일 많은 사람보다 많은 급여를 받는 사원 출력
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL > ALL(SELECT SAL FROM EMP
WHERE JOB='MANAGER');

 

 

 

---

복수 컬럼

1. 실행 결과가 복수 개의 컬럼, 복수 개의 행을 리턴하는 경우

--부서별 최소급여
SELECT MIN(SAL),DEPTNO
FROM EMP
GROUP BY DEPTNO;


SELECT SAL, DEPTNO
FROM EMP
WHERE (SAL, DEPTNO) IN ( SELECT MIN(SAL),DEPTNO
FROM EMP
GROUP BY DEPTNO);

--부서번호 30인 사원들의 급여와 커미션을 통해 이름 부서번호, 커미션출력

SELECT ENAME, DEPTNO, NVL(COMM, -1)
FROM EMP
WHERE (SAL, NVL(COMM,-1)) IN (SELECT SAL, NVL(COMM,-1)
FROM EMP
WHERE DEPTNO=30);

 

 

 

---

상호 작용

1. 메인 쿼리문에 사용된 테이블이 서브쿼리문에 다시 재 사용되는 경우
2. 서브 쿼리문에 사용될 조건 값을 메인 쿼리문으로부터 참조해야하는 경우
3. 성능 저하 등의 단점으로 많이 사용하진 않음

SELECT E.EMPNO, E.ENAME, E.DEPTNO, E.SAL
FROM EMP E
WHERE SAL>(SELECT AVG(SAL)
FROM EMP D
WHERE D.DEPTNO=E.DEPTNO);