[SQL] 서브 쿼리(SUBQUERY) - SUBQUERY, 단일 행, 다중 행, 다중 열, 다중 행 다중 열 서브 쿼리

SUBQUERY

o  SELECT 문장 안에 포함된 또 다른 SELECT 문장이 있는 것으로 메인 쿼리가 실행되기 전 한 번만 실행되며 반드시 괄호로 묶어야 한다. -> 서브 쿼리가 먼저 실행 후 메인 쿼리가 실행된다.

o  그렇기에 서브 쿼리는 메인 쿼리에 종속적이며 작성 시 연산자의 오른쪽에 위치해야 한다.

o  서브쿼리는 ORDER BY 문법의 지원이 안된다.

o  서브쿼리와 비교할 항목은 반드시 서브쿼리의 SELECT한 항목의 개수와 자료형을 일치시켜야 한다.

전체사원의 평균 급여보다 많이 받는 사원의 ID, 이름, 부서코드, 월급 조회하기	전지연의 매니저사원의 이름을 조회하세요
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY FROM EMPLOYEE WHERE SALARY >= (SELECT AVG(SALARY) FROM EMPLOYEE);	SELECT EMP_NAME FROM EMPLOYEE WHERE EMP_ID=(SELECT MANAGER_ID FROM EMPLOYEE WHERE EMP_NAME='전지연');

 

o  유형

1. 단일행 서브쿼리	서브쿼리의 조회 결과 값의 개수가 1개인 서브쿼리
2. 다중행 서브쿼리	서브쿼리의 조회 결과 값의 행이 여러 개인 서브쿼리
3. 다중열 서브쿼리	서브쿼리의 조회 결과 컬럼의 개수가 여러 개인 서브쿼리
4. 다중행 다중열 서브쿼리	서브쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러 개인 서브쿼리
5. 상(호연)관 서브쿼리	서브쿼리가 만든 결과 값을 메인 쿼리가 비교 연산할 때 메인 쿼리 테이블의 값이 변경되면 서브쿼리의 결과 값도 바뀌는 서브쿼리
6. 스칼라 서브쿼리	상관 쿼리이면서 결과 값이 한 개인 서브 쿼리

 

단일 행 서브쿼리

o  서브 쿼리의 조회 결과 값의 개수가 1개인 서브 쿼리

o  서브 쿼리의 결과가 OneRow, OneColum의 결과를 가진다.

o  WHERE절에서 비교할 때 많이 사용한다.

전 직원의 급여 평균보다 많은 급여를 받는 직원의 이름, 직급, 부서, 급여 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY FROM EMPLOYEE WHERE SALARY >= (SELECT AVG(SALARY) FROM EMPLOYEE) ORDER BY 3;
최대급여를 받고 있는 사원과 윤은해랑 같은 급여를 받는 사원 조회하기 사원명, 부서코드, 직책코드, 급여 조회
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, SALARY FROM EMPLOYEE WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEE)     -- 다수의 서브쿼리 사용 가능         OR (SALARY=(SELECT SALARY FROM EMPLOYEE WHERE EMP_NAME='윤은해')         AND EMP_NAME!='윤은해');

 

다중 행 서브쿼리

o  서브 쿼리의 조회 결과 값의 행이 여러 개인 서브 쿼리로 Many Row, One Colum의 결과를 가진다.

   (서브 쿼리에서 IN, GROUP BY 사용으로 결과값이 여려 행인 경우)

o  다중 행 서브 쿼리 앞에는 일반 비교 연산자 사용이 불가능하다.

   (사용 가능 연산자 : IN / NOT IN, >ANY / <ANY, >ALL / <ALL, EXIST / NOT EXIST 등)

부서 별 최고 급여를 받는 직원의 이름, 직급, 부서, 급여 조회
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, SALARY FROM EMPLOYEE WHERE SALARY IN (SELECT MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE) ORDER BY 3;

 

ANY

o  다중 값에 대해 대소비교를 할 수 있는 예약어이다.

o  다중행 서브쿼리에서 나온 결과 값을 OR로 연결해서 대소비교를 한다.

o  X > ANY(다중행 서브쿼리) : ANY의 ~(어떤)값보다 X가 크다면 참 -> 즉, 최소값보다 크면 참

o  X < ANY(다중행 서브쿼리) : ANY에 있는 ~(어떤)값보다 X가 작으면 참 -> 즉, 최대값보다 작으면 참

직책이 J3, J5인 사원들 중 최소급여보다 많이 받는 사원의 전체 컬럼 조회	직책이 J3, J5인 사원들 중 최대급여보다 적게 받는 사원의 전체 컬럼 조회
SELECT * FROM EMPLOYEE WHERE SALARY > ANY(SELECT SALARY FROM EMPLOYEE WHERE JOB_CODE IN ('J3','J5')); (서브 쿼리)SALARY의 최소 값보다 큰 SALARY 값들만 출력	SELECT * FROM EMPLOYEE WHERE SALARY < ANY(SELECT SALARY FROM EMPLOYEE WHERE JOB_CODE IN ('J3','J5')); (서브 쿼리)SALARY의 최대 값보다 적은 SALARY 값들만 출력

-   서브 쿼리의 값에서 최소값보다 크면 참이기에 굳이 최소값을 구하지 않아도 된다.

-   마찬가지로 서브 쿼리의 값에서 최대값보다 작으면 참이기에 굳이 최대값을 구하지 않아도 된다.

 

 

ALL

o  다중 값에 대해 대소비교를 할 수 있는 예약어이다.

o  모든 값보다 크다, 작다 -> AND

o  X>ALL(): ALL의 모든 값보다 큰 X가 참 -> ALL의 최대값보다 크면 참.

o  X<ALL() : ALL의 모든 값보다 작은 X가 참 -> ALL의 최소값보다 작으면 참.

SELECT * FROM EMPLOYEE WHERE SALARY > ALL(SELECT MAX(SALARY) FROM EMPLOYEE WHERE DEPT_CODE IN ('D2','D5'));

 

다중 열 서브쿼리

o  서브 쿼리의 조회 결과 컬럼의 개수가 여러 개인 서브쿼리

o  서브 쿼리의 결과가 One Row, Many Colum의 결과를 가진다.

퇴사한 여직원과 같은 부서, 같은 직급에 해당하는 사원의 이름, 직급, 부서, 입사일 조회
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, HIRE_DATE FROM EMPLOYEE WHERE (DEPT_CODE, JOB_CODE) IN (SELECT DEPT_CODE, JOB_CODE FROM EMPLOYEE WHERE SUBSTR(EMP_NO, 8, 1)=2 AND ENT_YN=‘Y);

-   WHERE (DEPT_CODE, JOB_CODE) 두 컬럼은 AND로 묶여 두 컬럼의 값과 일치하는 값만 포함이된다.

 

기술지원부이면서 급여가 200백만원인 사원의 이름, 부서, 코드 급여 조회하기

SELECT * FROM EMPLOYEE WHERE (DEPT_CODE,SALARY) IN (SELECT DEPT_CODE, SALARY FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID WHERE SALARY=2000000 AND DEPT_TITLE='기술지원부');

 

다중 행 다중 열 서브쿼리

o  서브쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러 개인 서브쿼리.

o  서브 쿼리의 결과가 Many Row, Many Colum의 결과를 가진다.

직급별 최소 급여를 받는 직원의 사번, 이름, 직급, 급여 조회

SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY FROM EMPLOYEE WHERE (JOB_CODE, SALARY) IN (SELECT JOB_CODE, MIN(SALARY) FROM EMPLOYEE GROUP BY JOB_CODE) ORDER BY 3;

 

부서별 최대급여를 받는 사원 또는 최소 급여를 받는 사원 조회하기 사원명, 부서명, 급여 출력

SELECT EMP_NAME, NVL(DEPT_TITLE,'인턴'), SALARY FROM EMPLOYEE     LEFT JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID WHERE (NVL(DEPT_CODE,1),SALARY)     IN (SELECT NVL(DEPT_CODE,1), MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE)        OR (NVL(DEPT_CODE,1),SALARY) IN (SELECT NVL(DEPT_CODE,1), MIN(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE);