[ Oracle] 오라클 WITH문 사용법 및 예제

[ Oracle] 오라클 WITH문 사용법 및 예제

Oracle 데이터베이스의 WITH문은 SQL 쿼리를 작성할 때 유용한 기능입니다.

WITH문의 개념과 작성 방법을 자세히 설명하고, 실제 예시를 통해 사용법을 보여드리겠습니다.

오라클 WITH문 사용법 및 예제

■ WITH문이란?

WITH문은 서브쿼리를 정의하고, 해당 서브쿼리의 결과를 임시로 생성된 가상 테이블로 사용할 수 있게 해주는 SQL의 기능입니다. 일반적으로 더 복잡한 쿼리를 작성할 때 임시 테이블을 생성하여 중복 코드를 줄이고 가독성을 높이는 데 사용됩니다. 또한, WITH문을 사용하면 서브쿼리를 단일 SQL 문으로 통합할 수 있어 성능 향상에도 기여합니다.

 

■ WITH문의 구문

WITH문은 다음과 같은 구문으로 사용됩니다.

WITH 가상테이블_이름 (칼럼이름1, 칼럼이름2, ...) AS (
    서브쿼리_문장
)

- 가상테이블_이름: WITH문에서 생성되는 가상 테이블의 이름을 지정합니다.
- 칼럼이름1, 칼럼이름2, ...: 가상테이블에 포함될 칼럼들의 이름을 지정합니다. 생략 가능합니다.
- 서브쿼리_문장: 가상 테이블로 사용될 서브쿼리를 작성합니다.

 

■ WITH문의 예시

아래 예시를 통해 WITH문의 사용법을 자세히 살펴보겠습니다.
먼저, 예시로 사용할 데이터베이스 테이블을 생성하겠습니다.

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100) NOT NULL,
    department VARCHAR2(50) NOT NULL,
    salary NUMBER NOT NULL
);
 
INSERT INTO employees VALUES (1, 'John Doe', 'HR', 50000);
INSERT INTO employees VALUES (2, 'Jane Smith', 'Finance', 60000);
INSERT INTO employees VALUES (3, 'Bob Johnson', 'Engineering', 70000);

● 예시 1: WITH문을 사용하지 않은 쿼리
우선 WITH문을 사용하지 않은 일반적인 쿼리를 살펴보겠습니다.

이 예시에서는 Engineering 부서의 직원들의 평균 급여를 구하는 쿼리를 작성합니다.

SELECT AVG(salary)
FROM employees
WHERE department = 'Engineering';

 

● 예시 2: WITH문을 사용한 쿼리
이번에는 WITH문을 사용하여 동일한 결과를 얻는 쿼리를 작성해보겠습니다.

WITH engineering_salary AS (
    SELECT salary
    FROM employees
    WHERE department = 'Engineering'
)
SELECT AVG(salary)
FROM engineering_salary;

위의 두 쿼리는 동일한 결과를 리턴하지만, WITH문을 사용한 두 번째 쿼리는 더 가독성이 높고 중복 코드를 피할 수 있습니다.

 

● 예시 3: WITH문과 다중 서브쿼리
WITH문은 여러 개의 서브쿼리를 조합하여 사용할 수도 있습니다.

예를 들어, HR 부서와 Finance 부서의 직원들의 급여 총합을 구하는 쿼리를 작성해보겠습니다.

WITH hr_finance_salary AS (
    SELECT salary
    FROM employees
    WHERE department = 'HR'
    UNION ALL
    SELECT salary
    FROM employees
    WHERE department = 'Finance'
)
SELECT SUM(salary)
FROM hr_finance_salary;

위의 쿼리는 HR 부서와 Finance 부서의 직원들의 급여를 하나의 가상 테이블로 합친 뒤, 총합을 계산합니다.

 

● 예시 4: WITH문으로 중첩 쿼리 풀기
WITH문을 사용하여 중첩 쿼리를 풀 수도 있습니다.

예를 들어, 특정 직원이 속한 부서의 모든 직원들을 조회하는 쿼리를 작성해보겠습니다.

WITH emp_info AS (
    SELECT department
    FROM employees
    WHERE emp_name = 'John Doe'
)
SELECT emp_id, emp_name, department
FROM employees
WHERE department = (SELECT department FROM emp_info);

위의 쿼리는 emp_info 가상 테이블을 사용하여 John Doe가 속한 부서를 구한 뒤, 해당 부서의 모든 직원들을 조회합니다.

 

반응형

● 예시 5: WITH문으로 계층 쿼리 작성하기
WITH문은 계층 쿼리를 작성하는 데에도 유용합니다. 예를 들어, 조직 구조를 나타내는 employees 테이블이 있을 때, 특정 직원의 하위 직원들을 모두 조회하는 쿼리를 작성할 수 있습니다.

WITH RECURSIVE employee_hierarchy AS (
    SELECT emp_id, emp_name, department
    FROM employees
    WHERE emp_name = 'John Doe'
    UNION ALL
    SELECT e.emp_id, e.emp_name, e.department
    FROM employees e
    JOIN employee_hierarchy eh ON e.emp_id = eh.manager_id
)
SELECT emp_id, emp_name, department
FROM employee_hierarchy;

위의 예시 쿼리는 John Doe가 속한 조직 구조를 재귀적으로 조회합니다.

재귀적으로 사용하기 위해 WITH RECURSIVE를 사용하며, 초기 쿼리와 재귀 쿼리를 UNION ALL로 연결합니다.

 

● 예시 6: WITH문으로 데이터 변환하기
WITH문을 사용하여 데이터를 변환하는 데에도 유용합니다.

예를 들어, 직원들의 급여를 연봉으로 변환하여 조회하는 쿼리를 작성해보겠습니다.

WITH annual_salary AS (
    SELECT emp_id, emp_name, salary * 12 AS annual_salary
    FROM employees
)
SELECT emp_id, emp_name, annual_salary
FROM annual_salary;

위의 쿼리는 employees 테이블의 급여를 연봉으로 변환하여 annual_salary 가상 테이블로 생성한 뒤, 변환된 데이터를 조회합니다.

 

● 예시 7: WITH문으로 조건에 따라 데이터 분류하기
WITH문은 조건에 따라 데이터를 분류하는 데에도 활용될 수 있습니다.

예를 들어, 급여가 60000 이상인 직원들을 고급직원, 그 외의 직원들을 일반직원으로 분류하는 쿼리를 작성해보겠습니다.

WITH employee_classification AS (
    SELECT emp_id, emp_name,
        CASE
            WHEN salary >= 60000 THEN '고급직원'
            ELSE '일반직원'
        END AS classification
    FROM employees
)
SELECT emp_id, emp_name, classification
FROM employee_classification;

위의 쿼리는 CASE문을 사용하여 급여에 따라 고급직원과 일반직원으로 분류한 뒤, employee_classification 가상 테이블로 생성한 뒤 결과를 조회합니다.

 

● 예시 8: WITH문으로 테이블 조인 최적화하기
WITH문은 테이블 조인을 최적화하는 데에도 활용될 수 있습니다.

예를 들어, 특정 부서의 직원들의 정보와 부서 정보를 함께 조회하는 쿼리를 작성해보겠습니다.

WITH department_employees AS (
    SELECT e.emp_id, e.emp_name, e.department, d.location
    FROM employees e
    JOIN departments d ON e.department = d.department_name
    WHERE e.department = 'HR'
)
SELECT emp_id, emp_name, department, location
FROM department_employees;

위의 쿼리는 employees와 departments 테이블을 조인하여 특정 부서의 직원들과 해당 부서의 위치 정보를 함께 조회합니다.

 

● 예시 9: WITH문으로 문자열 처리하기
WITH문을 사용하여 문자열 처리를 하는 데에도 유용합니다.

예를 들어, 직원 이름의 일부를 가지고 이메일 주소를 생성하는 쿼리를 작성해보겠습니다.

WITH email_generation AS (
    SELECT emp_name,
        LOWER(SUBSTR(emp_name, 1, INSTR(emp_name, ' ') - 1)) || '@company.com' AS email
    FROM employees
)
SELECT emp_name, email
FROM email_generation;

위의 쿼리는 SUBSTR과 INSTR 함수를 사용하여 직원 이름의 첫 번째 공백 이전의 부분을 추출하여 이메일 주소를 생성합니다.

 

● 예시 10: WITH문으로 날짜 데이터 처리하기
WITH문은 날짜 데이터를 처리하는 데에도 활용될 수 있습니다.

예를 들어, 입사일과 현재 날짜를 비교하여 근속 연수를 계산하는 쿼리를 작성해보겠습니다.

WITH employee_tenure AS (
    SELECT emp_id, emp_name, hire_date,
        ROUND(MONTHS_BETWEEN(SYSDATE, hire_date) / 12, 1) AS tenure_years
    FROM employees
)
SELECT emp_id, emp_name, hire_date, tenure_years
FROM employee_tenure;

위의 쿼리는 MONTHS_BETWEEN 함수를 사용하여 입사일과 현재 날짜 사이의 월 수를 계산한 뒤, 근속 연수를 계산합니다.

은행의 코어뱅킹에서 호출하는 SQLIO 프로그램을 작성할 때, 복잡한 쿼리에서 WITH문을 사용하여 다양한 데이터 처리 작업을 효과적으로 수행할 수 있습니다.

 

#오라클  #oracle  #with절  #오라클쿼리  #SQL