🌼 SUBQUERY (서브쿼리)
-
SQL 명령에 포함되어 실행되는 SELECT 명령
-
다수의 SQL 명령으로 얻을 수 있는 결과를 하나의 SQL 명령으로 얻기 위해 사용하는 기능
-
SELECT 명령(MAINQUERY)에 포함되어 실행되는 SELECT 명령(SUBQUERY)
-
서브쿼리의 명령 실행 후 제공되는 검색결과를 가지고 메인쿼리의 명령 실행하여 검색
-
서브쿼리는 메인쿼리의 FROM, WHERE, HAVING에서 ( )안에 작성하여 실행
🐣 example
--EMP 테이블에서 사원이름이 SCOTT인 사원보다 많은 급여를 받은 사원의 사원번호,사원이름,급여 검색
--SELECT 명령을 2번 사용하여 원하는 결과 검색
SELECT SAL FROM EMP WHERE ENAME='SCOTT';--검색결과 : 3000
SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>3000;
- 서브쿼리를 이용하면 SELECT 명령을 1번만 사용하여 원하는 결과 검색 가능
🌻 WHERE 절
- WHERE에서 조건식의 비교값 대신 서브쿼리의 검색 결과값을 사용하여 검색
- 조건식의 비교대상(컬럼)과 같은 자료형의 값이 하나만 검색되도록 서브쿼리 작성
- 서브쿼리로 단일행(SINGLE-ROW)의 단일컬럼(SINGLE-COLUMN)에 대한 값 검색
SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SCOTT');
--EMP 테이블에서 사원번호가 7844인 사원과 같은 업무를 하는 사원의 사원번호,사원이름,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO=7844)
AND EMPNO<>7844;
--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);
--EMP 테이블에서 SALES 부서에 근무하는 사원의 사원번호,사원이름,업무,급여 검색
--부서이름은 DEPT 테이블에 저장되어 있으므로 테이블 결합을 사용하여 검색
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');
--EMP 테이블에 저장된 모든 사원 중 가장 적은 급여를 받는 사원의 사원번호,사원일,업무,급여 검색
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM EMP);
--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'));
🌻 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);
🌼 다중행 서브쿼리
- 서버쿼리의 검색결과가 다중행(MULTI-ROW SUBQUERY)인 경우 = 연산자를 사용하여 컬럼값을 비교할 경우 에러 발생
- ex. EMP 테이블에서 부서별로 가장 적은 급여를 받는 사원의 사원번호,이름,급여,부서번호 검색
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL=(SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);
-- 다중행 오류 발생
🌻 IN 키워드
- 서브쿼리의 검색결과가 다중행인 경우 = 연산자 대신 IN 키워드를 사용하여
컬럼값을 비교하여 검색
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP
WHERE SAL IN(SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);
🌻 ANY or ALL 키워드
- 서브쿼리의 검색결과가 다중행인 경우 > 또는 < 연산자로 비교하기 위해 서브쿼리 앞에
ANY 또는 ALL 키워드를 사용하여 검색
--EMP 테이블에서 부서번호가 10인 부서에 근무하는 어떠한 사원보다 급여가 적은 사원의
--사원번호, 사원이름, 급여, 부서번호 검색
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP
WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO=10) AND DEPTNO<>10;
--EMP 테이블에서 부서번호가 10인 부서에 근무하는 모든 사원보다 급여가 적은 사원의
--사원번호, 사원이름, 급여, 부서번호 검색
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP
WHERE SAL < ALL (SELECT SAL FROM EMP WHERE DEPTNO=10);
--EMP 테이블에서 부서번호가 20인 부서에 근무하는 어떠한 사원보다 급여가 많은 사원의
--사원번호, 사원이름, 급여, 부서번호 검색
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP
WHERE SAL > ANY (SELECT SAL FROM EMP WHERE DEPTNO=20) AND DEPTNO<>20;
--EMP 테이블에서 부서번호가 20인 부서에 근무하는 모든 사원보다 급여가 많은 사원의
--사원번호, 사원이름, 급여, 부서번호 검색
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO=20);
🌻 MIN or MAX 함수
- 다중행 서브쿼리의 ANY 또는 ALL 대신 단일행 서브쿼리의 MIN 또는 MAX 함수 사용 권장
- 컬럼명 < ANY(다중행 서브쿼리) 대신 컬럼명 < (단일행 서브쿼리 - MAX 함수) 사용
- 컬럼명 < ALL(다중행 서브쿼리) 대신 컬럼명 < (단일행 서브쿼리 - MIN 함수) 사용
- 컬럼명 > ANY(다중행 서브쿼리) 대신 컬럼명 > (단일행 서브쿼리 - MIN 함수) 사용
- 컬럼명 > ALL(다중행 서브쿼리) 대신 컬럼명 > (단일행 서브쿼리 - MAX 함수) 사용
---EMP 테이블에서 부서번호가 10인 부서에 근무하는 어떠한 사원보다 급여가 적은 사원의
--사원번호, 사원이름, 급여, 부서번호 검색
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP
WHERE SAL < (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=10) AND DEPTNO<>10;
--EMP 테이블에서 부서번호가 10인 부서에 근무하는 모든 사원보다 급여가 적은 사원의
--사원번호, 사원이름, 급여, 부서번호 검색
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP
WHERE SAL < (SELECT MIN(SAL) FROM EMP WHERE DEPTNO=10);
--EMP 테이블에서 부서번호가 20인 부서에 근무하는 어떠한 사원보다 급여가 많은 사원의
--사원번호, 사원이름, 급여, 부서번호 검색
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP
WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20) AND DEPTNO<>20;
--EMP 테이블에서 부서번호가 20인 부서에 근무하는 모든 사원보다 급여가 많은 사원의
--사원번호, 사원이름, 급여, 부서번호 검색
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=20);
🌻 비교 컬럼을 , 로 구분하여 나열
- EMP 테이블에서 사원이름이 ALLEN인 사원과 관리자가 같으며 같은 업무를 하는 사원의 사원번호,사원이름,관리자번호,업무 검색
SELECT EMPNO,ENAME,MGR,JOB FROM EMP WHERE MGR=(SELECT MGR FROM EMP WHERE ENAME='ALLEN')
AND JOB=(SELECT JOB FROM EMP WHERE ENAME='ALLEN') AND ENAME<>'ALLEN';
--중복되는 구문 발생- 서브쿼리의 검색대상이 여러 개(MULTI-COLUMN SUBQUERY)를 비교할 경우 비교 컬럼을
( )안에 ,로 구분하여 나열하면 비교 가능
SELECT EMPNO, ENAME, MGR, JOB, SAL FROM EMP
WHERE (MGR,JOB)=(SELECT MGR,JOB FROM EMP WHERE ENAME='ALLEN') AND ENAME<>'ALLEN';
🎀 연습문제
--문제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;
Study Log 📂