[sql] 7. 서브쿼리(SUBQUERY)

1. 서브쿼리(SUBQUERY)의 개념

• SQL 명령에 포함되어 실행되는 SELECT 명령
• 다수의 SQL 명령으로 얻을 수 있는 결과를 하나의 SQL 명령으로 얻기 위해 사용하는 기능
• (즉, 여러번 SQL 명령을 써야할 것으로 한번에 끝낼 수 있음!)
• SELECT 명령(MAINQUERY)에 포함되어 실행되는 SELECT 명령(SUBQUERY)
  • 서브쿼리의 명령 실행 후 제공되는 검색결과를 가지고 메인쿼리의 명령을 실행하여 검색
  • 서브쿼리는 메인쿼리의 FROM , WHERE , HAVING 에서 ( )안에 작성하여 실행

 

 

 

 

 

 

2. 단일행 , 단일컬럼 서브쿼리

• 조건식의 비교대상(컬럼)과 같은 자료형의 값이 하나만 검색되도록 서브쿼리 작성
• 서브쿼리로 단일행(SINGLE-ROW)의 단일컬럼(SINGLE-COLUMN)에 대한 값 검색

서브쿼리를 사용하는 이유

• 서브쿼리를 이용하면 SELECT 명령 1번 사용으로 원하는 결과값을 얻을 수 있음

--EMP테이블에서 사원이름이 SCOTT인 사원보다 많은 급여를 받는 사원의 사원번호, 사원이름, 급여 검색
SELECT SAL FROM EMP WHERE ENAME='SCOTT'; --검색결과:3000
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL>3000;
--SELECT 명령 2번 사용해야함ㅠㅠㅠ

 

예시

--1)
-- 단일행, 단일컬럼인 서브쿼리값!
-- WHERE에서 조건식의 비교값 대신 서브쿼리의 검색 결과값을 사용하여 검색
SELECT EMPNO,ENAME,SAL FROM EMP
WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME='SCOTT');

 

서브쿼리

1)

--2)
--EMP 테이블에서 사원번호가 7844인 사원과 같은 업무를 하는 사원의 사원번호,사원이름,업무,급여 검색
--7844인 사원 포함
SELECT EMPNO,ENAME,JOB,SAL FROM EMP
WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO=7844);
--3)
--7844인 사원 제외
SELECT EMPNO,ENAME,JOB,SAL FROM EMP
WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO=7844) AND EMPNO<>7844;

서브쿼리

2)

3)

--4)
--EMP 테이블에서 사원번호가 7521인 사원과 같은 업무를 하는 사원 중
--사원번호가 7844인 사원보다 많은 급여를 받는 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP
WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO=7521)
AND EMPNO<>7521
AND SAL > (SELECT SAL FROM EMP WHERE EMPNO=7844);

서브쿼리

서브쿼리

4)

--5)
-- 검색대상에 테이블 결합할 이유가 없다면, 서브쿼리를 이용하자

--EMP 테이블에서 SALES 부서에 근무하는 사원의 사원번호,사원이름,업무,급여 검색
--부서이름은 DEPT 테이블에 저장되어 있으므로 테이블 결합을 사용하여 검색
--테이블 결합 이용(JOIN)
SELECT EMPNO,ENAME,JOB,SAL FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WHERE DNAME='SALES';

--서브쿼리 이용
--테이블 결합 대신 서브쿼리를 사용하여 검색 가능
SELECT EMPNO,ENAME,JOB,SAL FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME='SALES');

서브쿼리

5)

--6)
-- MIN험수. MAX함수
-- 서브쿼리 내 그룹함수 사용 가능
-- 특히, MIN험수, MAX함수 서브쿼리와 많이 사용함

--EMP 테이블에 저장된 '모든 사원 중'
--가장 적은 급여를 받는 사원의 사원번호,사원일,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP
WHERE SAL = (SELECT MIN(SAL) FROM EMP);

서브쿼리

6)

--7)
-- 오라클은 서브쿼리 안에 서브쿼리 사용 가능 (MYSQL은 에러임)
--EMP 테이블에서 'SALES 부서에 근무하는 사원 중'
--가장 적은 급여를 받는 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP
WHERE SAL = (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME='SALES'));

서브쿼리 내 서브쿼리

서브쿼리

7)

--8)
-- HAVING절 내 서브쿼리
-- HAVING의 그룹조건식에서 비교값 대신 서브쿼리를 사용하여 검색

--EMP 테이블에서 부서별 평균 급여 중 가장 많은 평균 급여를 받는 부서의 부서번호와 평균급여 검색
SELECT DEPTNO,CEIL(AVG(SAL)) AVG_SAL FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) = (SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO);

서브쿼리

8)

 

 

 

 

 

 

 

 

3. 다중행 , 단일컬럼 서브쿼리

1) IN키워드

-- 서브쿼리의 검색결과가 다중행(MULTI-ROW SUBQUERY)인 경우
-- = 연산자를 사용하여 컬럼값을 비교하면 에러 발생
--EMP 테이블에서 부서별로 가장 적은 급여를 받은 사원의 사원번호,사원이름,급여,부서번호 검색
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL = (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);  --ERROR


-- 서브쿼리의 검색결과가 다중행(MULTI-ROW SUBQUERY)인 경우 
-- = 연산자 대신 IN 키워드를 사용해 컬럼값을 비교하여 검색
--EMP 테이블에서 부서별로 가장 적은 급여를 받은 사원의 사원번호,사원이름,급여,부서번호 검색
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);  --OK

서브쿼리

 

2) ANY 또는 ALL 키워드

-- 서브쿼리의 검색결과가 다중행(MULTI-ROW SUBQUERY)인 경우 
-- > 또는 < 연산자로 비교하기 위해 서브쿼리 앞에 ANY 또는 ALL 키워드를 사용해 검색


--1)
-- 💚 어떠한 사원보다 급여가 적은 = 최대값보다 급여가 적은
--EMP 테이블에서 부서번호가 10인 부서에 근무하는
--어떠한 사원보다 급여가 적은 사원의 사원번호,사원이름,급여,부서번호 검색
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO=10) AND DEPTNO<>10;

--2)
-- 💚 모든 사원보다 급여가 적은 = 최소값보다 급여가 적은
--EMP 테이블에서 부서번호가 10인 부서에 근무하는
--모든 사원보다 급여가 적은 사원의 사원번호,사원이름,급여,부서번호 검색
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL < ALL (SELECT SAL FROM EMP WHERE DEPTNO=10);

--3)
-- 💚 어떠한 사원보다 급여가 많은 = 최소값보다 급여가 많은
--EMP 테이블에서 부서번호가 20인 부서에 근무하는
--어떠한 사원보다 급여가 많은 사원의 사원번호,사원이름,급여,부서번호 검색
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL > ANY (SELECT SAL FROM EMP WHERE DEPTNO=20) AND DEPTNO<>20;

--4)
-- 💚 모든 사원보다 급여가 많은 = 최대값보다 급여가 많은
--EMP 테이블에서 부서번호가 20인 부서에 근무하는
--모든 사원보다 급여가 많은 사원의 사원번호,사원이름,급여,부서번호 검색
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO=20);

서브쿼리

1)

2)

서브쿼리

3)

4)

3) 다중행 서브쿼리의 ANY 또는 ALL 키워드대신 단일행 서브쿼리의 MIN함수 또는 MAX함수 사용 권장

--1)
-- 💛 어떠한 사원보다 급여가 적은 = 최대보다 더 작은
-- 컬럼명 < ANY(다중행 서브쿼리) 대신 컬럼명 < (단일행 서브쿼리 - MAX 함수) 사용
-- EMP 테이블에서 부서번호가 10인 부서에 근무하는
-- 어떠한 사원보다 급여가 적은 사원의 사원번호,사원이름,급여,부서번호 검색
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL < (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=10) AND DEPTNO<>10;

--2)
-- 💛 모든 사원보다 급여가 적은 = 최소보다 더 작은
-- 컬럼명 < ALL(다중행 서브쿼리) 대신 컬럼명 < (단일행 서브쿼리 - MIN 함수) 사용
-- EMP 테이블에서 부서번호가 10인 부서에 근무하는
-- 모든 사원보다 급여가 적은 사원의 사원번호,사원이름,급여,부서번호 검색
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL < (SELECT MIN(SAL) FROM EMP WHERE DEPTNO=10);

--3)
-- 💛 어떠한 사원보다 급여가 많은 = 최소보다 더 큰
-- 컬럼명 > ANY(다중행 서브쿼리) 대신 컬럼명 > (단일행 서브쿼리 - MIN 함수) 사용
-- EMP 테이블에서 부서번호가 20인 부서에 근무하는
-- 어떠한 사원보다 급여가 많은 사원의 사원번호,사원이름,급여,부서번호 검색
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20) AND DEPTNO<>20;

--4)
-- 💛 모든 사원보다 급여가 많은 = 최대보다 더 큰
-- 컬럼명 > ALL(다중행 서브쿼리) 대신 컬럼명 > (단일행 서브쿼리 - MAX 함수) 사용
-- EMP 테이블에서 부서번호가 20인 부서에 근무하는
-- 모든 사원보다 급여가 많은 사원의 사원번호,사원이름,급여,부서번호 검색
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=20);

서브쿼리

1)

서브쿼리

2)

서브쿼리

3)

서브쿼리

4)

 

 

 

 

 

 

 

 

 

4. 단일행, 다중컬럼 서브쿼리

-- 서브쿼리의 검색대상이 여러 개(MULTI-COLUMN SUBQUERY)인 경우, 
-- 비교 컬럼을 ( )안에 ,로 구분하여 나열하면 비교 가능

-- EMP 테이블에서 사원이름이 ALLEN인 사원과 관리자가 같으며 
-- 같은 업무를 하는 사원의 사원번호,사원이름,관리자번호,업무,급여 검색
--1)
SELECT EMPNO,ENAME,MGR,JOB,SAL FROM EMP
WHERE MGR = (SELECT MGR FROM EMP WHERE ENAME='ALLEN')
AND JOB = (SELECT JOB FROM EMP WHERE ENAME='ALLEN') AND ENAME<>'ALLEN';
--2)
SELECT EMPNO,ENAME,MGR,JOB,SAL FROM EMP
WHERE (MGR,JOB) = (SELECT MGR,JOB FROM EMP WHERE ENAME='ALLEN') AND ENAME<>'ALLEN';

서브쿼리

서브쿼리

1)

서브쿼리

2)

 

 

 

 

 

 

 

 

 

5. 연습문제

문제1. 사원 테이블에서 BLAKE 보다 급여가 많은 사원들의 사번,이름,급여를 검색하시오.

SELECT EMPNO,ENAME,SAL FROM EMP
WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME='BLAKE');

서브쿼리

 

문제2. 사원 테이블에서 MILLER 보다 늦게 입사한 사원의 사번,이름,입사일을 검색하시오.

SELECT EMPNO,ENAME,HIREDATE FROM EMP
WHERE HIREDATE > (SELECT HIREDATE FROM EMP WHERE ENAME='MILLER');

서브쿼리

 

문제3. 사원 테이블에서 사원 전체 평균 급여보다 급여가 많은 사원들의 사번,이름,급여 검색하시오.

SELECT EMPNO,ENAME,SAL FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP);

서브쿼리

 

문제4. 사원 테이블에서 CLARK와 같은 부서이며, 사번이 7698인 직원의 급여보다 많은 급여를 받는 사원들의 사번,이름,급여를 검색하시오.

SELECT EMPNO,ENAME,SAL FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME='CLARK')
    AND SAL > (SELECT SAL FROM EMP WHERE EMPNO=7698);

서브쿼리

서브쿼리

 

문제5. 사원 테이블에서 부서별 최대 급여를 받는 사원들의 사번,이름,부서코드,급여를 검색하시오.

SELECT EMPNO,ENAME,DEPTNO,SAL FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO) ORDER BY DEPTNO;

서브쿼리