13. Oracle 서브쿼리

hr sample schema 계정 예제파일

hr0217_서브쿼리.sql

1. 서브쿼리(Subquery)란?

<aside>

서브쿼리는 하나의 SQL 문 안에서 실행되는 또 다른 SQL 문이다.
주로 메인 쿼리의 조건을 지정하거나 데이터를 가공하는 데 사용된다.
서브쿼리는 반드시 괄호 () 안에 작성해야 한다. </aside>

2. 서브쿼리의 종류 및 문법

1) 단일행 서브쿼리 (Single Row Subquery)

서브쿼리 결과가 한 개의 행(Row) 만 반환될 때 사용한다.
연산자: =, >, <, >=, <=, <>

-- 평균 급여보다 높은 급여를 받는 직원 조회
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

-- 1. 단일행 서브쿼리 (Single Row Subquery)
-- 하나의 행만 반환하는 서브쿼리

-- 예제 1: Steven King과 같은 부서에서 일하는 직원들 조회
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id = (
    SELECT department_id 
    FROM employees 
    WHERE first_name = 'Steven' 
    AND last_name = 'King'
);

-- 예제 2: 평균 급여보다 많은 급여를 받는 직원 조회
SELECT first_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees
);

-- 예제 3: 가장 최근에 입사한 직원 정보 조회
SELECT first_name, hire_date
FROM employees
WHERE hire_date = (
    SELECT MAX(hire_date) 
    FROM employees
);

2) 다중행 서브쿼리 (Multi Row Subquery)

서브쿼리 결과가 여러 개의 행(Row) 을 반환할 때 사용한다.
연산자: IN, ANY, ALL

-- location_id가 1700인 부서의 직원 목록 조회
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700
);

-- 2. 다중행 서브쿼리 (Multiple Row Subquery)
-- 여러 행을 반환하는 서브쿼리, IN, ANY, ALL 등의 연산자 사용

-- 예제 1: 각 부서별 최고 급여를 받는 직원들 조회
SELECT first_name, salary, department_id
FROM employees
WHERE (department_id, salary) IN (
    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
);

-- 예제 2: 관리자(manager)로 있는 직원들의 정보 조회
SELECT first_name, employee_id
FROM employees
WHERE employee_id IN (
    SELECT DISTINCT manager_id
    FROM employees
    WHERE manager_id IS NOT NULL
);

-- 예제 3: 부서 이동 이력이 있는 직원들의 현재 정보 조회
SELECT first_name, department_id
FROM employees
WHERE employee_id IN (
    SELECT employee_id
    FROM job_history
);

-- ANY 연산자 예제
-- 1. ANY: 조건을 만족하는 값이 하나라도 있으면 TRUE
-- IT 부서(부서번호 60)의 어떤 직원보다 급여가 높은 직원들 조회
SELECT first_name, salary, department_id
FROM employees
WHERE salary > ANY (
    SELECT salary 
    FROM employees 
    WHERE department_id = 60
);

-- 2. ANY: 직급(job_id)별 최저 급여보다 급여가 높은 직원 조회
SELECT first_name, salary, job_id
FROM employees
WHERE salary > ANY (
    SELECT MIN(salary)
    FROM employees
    GROUP BY job_id
);

-- ALL 연산자 예제
-- 1. ALL: 모든 값이 조건을 만족해야 TRUE
-- 모든 부서의 최소 급여보다 많이 받는 직원 조회
SELECT first_name, salary
FROM employees
WHERE salary > ALL (
    SELECT MIN(salary)
    FROM employees
    GROUP BY department_id
);

-- 2. ALL: 모든 매니저보다 늦게 입사한 직원 조회
SELECT first_name, hire_date
FROM employees
WHERE hire_date > ALL (
    SELECT hire_date
    FROM employees
    WHERE employee_id IN (
        SELECT DISTINCT manager_id 
        FROM employees 
        WHERE manager_id IS NOT NULL
    )
);

3) 다중열 서브쿼리 (Multiple Column Subquery)

서브쿼리에서 두 개 이상의 컬럼(Column) 을 반환할 때 사용한다.

-- 각 부서별 최소 급여를 받는 직원 조회
SELECT employee_id, first_name, salary, department_id
FROM employees
WHERE (salary, department_id) IN (
    SELECT MIN(salary), department_id
    FROM employees
    GROUP BY department_id
);

-- 3. 다중열 서브쿼리 (Multiple Column Subquery)
-- 여러 열을 반환하는 서브쿼리

-- 예제 1: 부서별로 가장 높은 급여와 가장 오래 근무한 직원 조회
SELECT first_name, salary, hire_date, department_id
FROM employees e
WHERE (salary, hire_date) IN (
    SELECT MAX(salary), MIN(hire_date)
    FROM employees
    GROUP BY department_id
    HAVING department_id = e.department_id
);

-- 예제 2: 같은 부서, 같은 직무를 가진 직원들 조회
SELECT first_name, department_id, job_id
FROM employees
WHERE (department_id, job_id) IN (
    SELECT department_id, job_id
    FROM employees
    GROUP BY department_id, job_id
    HAVING COUNT(*) > 1
);

-- 예제 3: 각 부서의 최소/최대 급여와 일치하는 직원 조회
SELECT first_name, salary, department_id
FROM employees
WHERE (department_id, salary) IN (
    SELECT department_id, MIN(salary)
    FROM employees
    GROUP BY department_id
    UNION
    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
);