[오라클] 서브 쿼리 Sub Query

쿼리 문장 안에 다른 쿼리가 포함된 것

메인 쿼리	서브 쿼리
- 실행의 결과가 조회되는 쿼리. - 서브쿼리에 의해 실행된 쿼리문의 결과에 의해 메인 쿼리가 실행된다.	- 메인쿼리의 안쪽에 위치한 쿼리 - 서브쿼리의 실행 결과가 메인 쿼리의 조건이나 결과로 사용된다. - 서브쿼리에는 ORDER BY 절 사용 불가

 

서브쿼리 사용위치

WHERE 절	값의 결과가 1개 or 여러개 로 나오는 결과일 경우
HAVING 절	메인 쿼리에서 그룹행 함수를 사용하고 그룹에 대한 조건을 서브쿼리로 사용가능
SELECT 절	insert 문의 into 절 // update 문의 set 절
FROM 절

 

서브쿼리 종류

단일행 서브쿼리	서브쿼리의 결과가 하나만 나오는 경우
복수행 서브쿼리	서브쿼리의 결과가 하나 이상 나오는 경우 WHERE 절의 in 연산자와 사용 가능 IN, ANY, ALL, EXISTS 등에도 사용 가능

 

단일행 서브 쿼리 - WHERE 절에서 사용

[문제 1 ] 사원 번호가 109인 사원의 급여 보다 급여가 많은 직원의 이름과 담당업무를 조회하시오.

 

1) 일반 쿼리로 두 번 나눠 처리한 경우

SELECT SALARY 
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 109;  -- 9000

SELECT FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE SALARY > 9000;

 

2) 서브 쿼리로 처리한 경우 (단일행 서브 쿼리)

SELECT FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE SALARY > (SELECT SALARY 
                 FROM EMPLOYEES
                 WHERE EMPLOYEE_ID = 109) ;

 

[문제 2 ] 162번 사원의 급여와 동일한 급여를 받는 직원 명단을 조회하시오 (이름, 급여, 부서번호)

SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY = (SELECT SALARY 
                 FROM EMPLOYEES
                 WHERE EMPLOYEE_ID = 162) ;

 

단일행 서브 쿼리 - FROM절에서 사용

[문제 3 ] 부서 번호가 50 인 직원 중에 연봉이 3000 보다 높은 직원의 사원번호, 이름, 연봉, 부서번호를 조회하시오

SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50;

SELECT ROWNUM, tbl.* FROM
    (SELECT EMPLOYEE_ID, FIRST_NAME, SALARY ,DEPARTMENT_ID
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = 50)tbl
WHERE SALARY > 3000;

 

일련번호 ROWNUM 

동적으로 붙여주는 순서 번호

SELECT ROWNUM,FIRST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;

 

일련번호로 조건을 주고 싶다면?

SELECT ROWNUM, tbl.* FROM
    (SELECT EMPLOYEE_ID, FIRST_NAME, SALARY ,DEPARTMENT_ID
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = 50)tbl
WHERE SALARY > 3000
    AND
    ROWNUM BETWEEN 1 AND 10;

코드	의미
ROWNUM BETWEEN 1 AND 10;	일련번호를 1번 부터 10번 까지 조회
ROWNUM BETWEEN 11 AND 20;	--> 오류 : 조회하면서 동적으로 붙이는 번호이기 때문에 11번은 존재하지 않는다.

 

11번 부터 20번 까지 조회하는 방법

서브쿼리 안에 일련번호를 넣어 일반 컬럼으로 만든다. -> PAGING 할 때 사용하는 방법이다.

SELECT RNO, tbl.* FROM
    (SELECT ROWNUM AS RNO, EMPLOYEE_ID, FIRST_NAME, SALARY ,DEPARTMENT_ID
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = 50)tbl
WHERE 
    -- SALARY > 3000  -- NO 라는 컬럼이 되어 tbl 에 적용된것 --> 일반컬럼
    -- AND
    RNO BETWEEN 11 AND 20;

 

[문제 4] 사원번호가 101 인 사원의 담당업무가 같은 사원들의 목록을 조회 (사원번호, 이름, 담당업무)

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID
FROM EMPLOYEES
WHERE JOB_ID = (SELECT JOB_ID
                FROM EMPLOYEES
                WHERE EMPLOYEE_ID = 101);

 

[문제 5] 03년도 이전에 입사한 직원의 일련 번호와 이름, 급여, 입사일, 부서번호를 조회하시오

* 일련번호를 붙이고 ORDER BY 를 하면 순서가 뒤섞이게 된다.

SELECT ROWNUM RNO, FIRST_NAME, SALARY, HIRE_DATE, DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE < '03/01/01'
ORDER BY SALARY DESC;

 

 

이중 3번째로 급여가 많은 직원의 명단을 재 조회 하시오.

동적으로 생성된 컬럼이 아닌 일반컬럼으로!

SELECT * FROM
(
    SELECT ROWNUM RNO, TEMP.* FROM 
    (
        SELECT FIRST_NAME, SALARY, HIRE_DATE, DEPARTMENT_ID
        FROM EMPLOYEES
        WHERE HIRE_DATE < '03/01/01'
        ORDER BY SALARY DESC -- 마무리 된 다음에 서브쿼리를 해서 관계 없다... 
    )TEMP
)TMP
WHERE RNO = 3;

 

단일행 서브 쿼리 - SELECT절에서 사용

[문제 6] 이름이 'Bruce' , 'Daniel' 이라는 직원의 성을 조회하시오.

 1) 일반 쿼리로 처리한 경우

SELECT FIRST_NAME , LAST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME IN ('Bruce' , 'Daniel');

 

두 개의 행으로 나오는 것을 하나의 행으로 나오도록 하고 싶다. (** 잘 사용하지는 않는다.)

SELECT 
    (SELECT LAST_NAME FROM EMPLOYEES WHERE FIRST_NAME = 'Bruce') AS "Bruce의 성",
    (SELECT LAST_NAME FROM EMPLOYEES WHERE FIRST_NAME = 'Daniel') AS "Daniel의 성"
FROM DUAL;

 

 

복수행 서브 쿼리

[문제 7] IN 연산자와 함께 30번 부서의 직급과 동일한 직급을 다른 팀에서도 하고 있는지 조회

SELECT *
FROM EMPLOYEES
WHERE JOB_ID IN ( SELECT DISTINCT JOB_ID
                FROM EMPLOYEES
                WHERE DEPARTMENT_ID = 30);
                
                
SELECT SALARY, JOB_iD  
FROM EMPLOYEES
WHERE JOB_ID = 'ST_MAN'
ORDER BY 1;

 

ANY

서브 쿼리에서 추출된 어느 값보다 큰 값이므로 결국, 가장 작은 값보다 큰 값이 조회된다.

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > ANY(SELECT SALARY  
                    FROM EMPLOYEES
                    WHERE JOB_ID = 'ST_MAN'); -- 5800(가장 낮은 값)보다 큰 값이 조회된다.

 

ALL

서브쿼리로 추출된 모든 값들보다 큰 값이 메인쿼리에서 사용되므로 가장 큰 값보다 더 큰 값이 조회된다.

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > ALL(SELECT SALARY  
                    FROM EMPLOYEES
                    WHERE JOB_ID = 'ST_MAN');

 

[Q] 'ST_MAN' 직군의 급여중에서 가장 적게 받는 급여보다 더 적게 받는 사람의 이름, 급여를 조회하시오.

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY < ANY(SELECT SALARY  
                    FROM EMPLOYEES
                    WHERE JOB_ID = 'ST_MAN');

 

[Q] 직군이 'REP'로 끝나는 직원들이 받는 급여와 동일한 급여를 받는 직원의 사원번호, 이름, 직급, 급여를 조회하시오.

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE SALARY = ANY( SELECT SALARY
                    FROM EMPLOYEES
                    WHERE JOB_ID LIKE '%REP');

 

REP로 끝나는 직군을 찾는 방법

SELECT JOB_ID
FROM EMPLOYEES
WHERE JOB_ID LIKE '%REP'

 

EXISTS

서브쿼리에서 질의한 결과에 만족하는 행이 존재하면 메인쿼리를 실행 ( 결과의 유무가 중요하다)

SELECT DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE EXISTS( SELECT DEPARTMENT_ID
                FROM EMPLOYEES
                WHERE DEPARTMENT_ID = 80);
               -- WHERE DEPARTMENT_ID = 500);

 

[Q] 성이 'Chen' 이 존재하면 그 직원의 부서명, 이름, 성, 급여를 조회하시오

SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE EXISTS (SELECT FIRST_NAME, LAST_NAME, SALARY
                FROM EMPLOYEES
                WHERE LAST_NAME LIKE 'Chen');

존재 하면 FROM 까지가 실행되기 때문에 적어 놓은 모든 직원의 정보가 조회된다.

만약 Chen 만 조회하고 싶다면?

SELECT D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME, E.SALARY
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE EXISTS (SELECT FIRST_NAME, LAST_NAME, SALARY
                FROM EMPLOYEES
                WHERE LAST_NAME LIKE 'Chen')
AND LAST_NAME LIKE 'Chen';

코드	의미
FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID	문제에서 직원의 부서명을 원했기 때문에
AND LAST_NAME LIKE 'Chen';	메인 쿼리 마지막에 조건을 다시 설정한다.

 

 

연습 문제

[Q] SELF JOIN을 사용하여 'Oliver' 사원의 부서명, 그 사원과 동일한 부서에서 근무하는  동료 사원의 이름을 조회. (단, 각 열의 별칭은 부서명, 동료로 할 것)

SELECT TEMP.FIRST_NAME "동료", D.DEPARTMENT_NAME "부서명"
FROM 
    (
    SELECT FIRST_NAME, DEPARTMENT_ID
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                            FROM EMPLOYEES 
                            WHERE FIRST_NAME = 'Oliver')
    )TEMP, DEPARTMENTS D
WHERE TEMP.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY 1;