22일차 - 서브쿼리

은채의 성장통·2025년 6월 27일
oraclesql

KCC정보통신

목록 보기
26/30
post-thumbnail

노션정리

서브쿼리 및 관련 SQL 개념 정리

1. 서브쿼리 유형 요약

구분설명사용 위치특징
단일행 서브쿼리하나의 행을 반환WHERE, HAVING=, >, < 등과 함께 사용
다중행 서브쿼리여러 행을 반환WHERE, HAVINGIN, ANY, ALL, EXISTS
상호연관 서브쿼리외부 쿼리와 연동되어 반복 실행WHERE, SELECT외부의 각 행마다 실행
스칼라 서브쿼리하나의 값 반환SELECT, WHERE 등컬럼처럼 활용
인라인 뷰FROM 절에 서브쿼리 사용FROM (SELECT …)임시 테이블 역할
계층형 쿼리트리 구조 조회START WITH, CONNECT BY계층 수준(LEVEL) 활용
Top-N 쿼리상위/하위 N건 조회ROWNUM, ROW_NUMBER, FETCH 등정렬 후 추출
CTE (WITH 절)공통 테이블 표현식쿼리 전체 구조 상단재사용성과 가독성 향상

2. 주요 SQL 예제

단일행 서브쿼리

SELECT first_name, job_id, hire_date
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 103);

다중행 서브쿼리

-- ANY 사용
SELECT first_name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE first_name = 'David');

-- IN 사용
SELECT first_name, salary
FROM employees
WHERE salary IN (SELECT salary FROM employees WHERE first_name = 'David');

EXISTS

SELECT first_name, salary
FROM employees e
WHERE EXISTS (
  SELECT * FROM departments d
  WHERE d.manager_id = e.employee_id
);

상호연관 서브쿼리

SELECT first_name, salary
FROM employees a
WHERE salary > (
  SELECT AVG(salary)
  FROM employees b
  WHERE b.department_id = a.department_id
);

스칼라 서브쿼리

SELECT first_name,
       (SELECT department_name
        FROM departments d
        WHERE d.department_id = e.department_id) AS department_name
FROM employees e;

인라인 뷰

SELECT row_number, first_name, salary
FROM (
  SELECT first_name, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
  FROM employees
)
WHERE row_number BETWEEN 1 AND 10;

FETCH 사용 (Oracle 12c+)

SELECT first_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS
FETCH FIRST 5 ROWS ONLY;

계층형 쿼리

SELECT 
    employee_id, 
    -- LEVEL 값에 따라 들여쓰기를 하고 이름을 출력하여 계층 구조 시각화
    LPAD(' ', 3 * (LEVEL - 1)) || first_name || ' ' || last_name AS full_name,
    -- 현재 행의 계층 수준 (루트 = 1, 하위 = 2, 3, ...)
    LEVEL 
FROM 
    employees
-- 계층 구조의 시작 조건: manager_id가 NULL인 최상위 직원부터 시작 (즉, 최고 관리자)
START WITH 
    manager_id IS NULL
-- 계층을 구성하는 규칙: 한 직원의 employee_id가 다른 직원의 manager_id일 때 관계를 형성
CONNECT BY 
    PRIOR employee_id = manager_id;

CTE (공통 테이블 표현식)

WITH dept_avg_salary AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
)
SELECT e.first_name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg_salary d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

3. 서브쿼리 vs 조인 비교

상황서브쿼리조인
단일 값 비교간단하고 직관적불필요
다수 테이블 결합복잡해질 수 있음효율적
재사용 가능성불리유리
성능 측면경우에 따라 느릴 수 있음인덱스와 최적화에 유리
가독성간단한 경우 유리복잡한 로직에 유리

1. CREATE TABLE AS SELECT (CTAS)

  • 기존 테이블 구조 및 데이터를 기반으로 새 테이블을 생성
  • 컬럼, 데이터 타입, 제약조건 중 일부는 계승되지 않음
CREATE TABLE new_table AS
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = 90;

2. INSERT

단일 행 삽입

INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (280, 'Data Analytics', NULL, 1700);

다른 테이블로부터 여러 행 삽입 (서브쿼리 사용)

INSERT INTO managers (employee_id, first_name, job_id, salary, hire_date)
SELECT employee_id, first_name, job_id, salary, hire_date
FROM employees
WHERE job_id LIKE '%MAN';

3. UPDATE

기본 사용법

UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 103;

서브쿼리를 이용한 다중 열 갱신

UPDATE employees
SET (job_id, salary, manager_id) = (
    SELECT job_id, salary, manager_id
    FROM employees
    WHERE employee_id = 108
)
WHERE employee_id = 109;

※ 단, 위 문법은 Oracle에서만 가능하며, SET에 괄호 묶인 컬럼 리스트와 반환되는 서브쿼리 열 순서가 일치해야 합니다.

4. DELETE

단순 조건 기반 삭제

DELETE FROM employees
WHERE employee_id = 104;

서브쿼리를 이용한 조건 삭제

DELETE FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Shipping'
);

※ 서브쿼리가 다중행 반환 가능성이 있을 땐 IN 사용 권장

5. MERGE

  • Oracle 전용 DML로, 조건에 따라 INSERT, UPDATE, 또는 아무 작업도 수행하지 않음
  • 주로 데이터 동기화, 비교 병합 처리에 유용
MERGE INTO target_table t
USING source_table s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
    UPDATE SET t.salary = s.salary, t.job_id = s.job_id
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, job_id, salary, hire_date)
    VALUES (s.employee_id, s.first_name, s.job_id, s.salary, s.hire_date);

부가 팁: MERGE 문 사용 시 고려사항

항목설명
ON 조건기준이 되는 Key 또는 비교 기준 정의
MATCHED기준이 일치할 경우 수행 (UPDATE)
NOT MATCHED기준이 없을 경우 수행 (INSERT)
제약 조건PK 또는 Unique Key로 비교하는 것이 일반적

1. Unconditional Multiple Insert (INSERT ALL)

개념

  • 조건 없이 SELECT로 조회된 모든 결과를 여러 테이블에 모두 삽입
  • 각 INTO 절은 항상 실행

예시

-- employees 테이블에서 모든 직원을 두 개의 백업 테이블에 복사
INSERT ALL
  INTO emp_backup1 (employee_id, first_name, salary)
  VALUES (employee_id, first_name, salary)

  INTO emp_backup2 (employee_id, first_name, salary)
  VALUES (employee_id, first_name, salary)
SELECT employee_id, first_name, salary
FROM employees;

2. Conditional Multiple Insert (INSERT ALL ... WHEN)

개념

  • 여러 WHEN 조건을 지정하여 SELECT 결과를 각각의 조건에 맞는 테이블에 분기 삽입
  • 여러 WHEN 절이 동시에 만족하면 모두 실행 (중복 삽입 가능성 있음)

예시

-- 부서별로 데이터를 다른 테이블에 삽입
INSERT ALL
  WHEN department_id = 10 THEN
    INTO emp_10 (employee_id, first_name, salary)
    VALUES (employee_id, first_name, salary)

  WHEN department_id = 20 THEN
    INTO emp_20 (employee_id, first_name, salary)
    VALUES (employee_id, first_name, salary)

  WHEN department_id = 30 THEN
    INTO emp_30 (employee_id, first_name, salary)
    VALUES (employee_id, first_name, salary)
SELECT employee_id, first_name, salary, department_id
FROM employees;

3. Conditional Insert with Priority (INSERT FIRST ... WHEN)

개념

  • INSERT FIRST는 조건을 위에서부터 차례대로 검사
  • 가장 먼저 만족하는 조건만 실행, 이후 조건은 무시
  • 조건이 서로 겹치거나 범위 조건일 때 유리

예시

-- 직원 급여에 따라 한 번만 삽입되는 구조
INSERT FIRST
  WHEN salary <= 5000 THEN
    INTO emp_sal5000 (employee_id, first_name, salary)
    VALUES (employee_id, first_name, salary)

  WHEN salary <= 10000 THEN
    INTO emp_sal10000 (employee_id, first_name, salary)
    VALUES (employee_id, first_name, salary)

  WHEN salary <= 15000 THEN
    INTO emp_sal15000 (employee_id, first_name, salary)
    VALUES (employee_id, first_name, salary)

  WHEN salary <= 20000 THEN
    INTO emp_sal20000 (employee_id, first_name, salary)
    VALUES (employee_id, first_name, salary)

  ELSE
    INTO emp_sal_others (employee_id, first_name, salary)
    VALUES (employee_id, first_name, salary)
SELECT employee_id, first_name, salary
FROM employees;

비교 요약

구분실행 방식사용 목적
INSERT ALL모든 대상 테이블에 삽입백업, 다중복사
INSERT ALL WHEN조건에 따라 여러 군데 삽입 가능값 분기 저장
INSERT FIRST WHEN조건 중 첫 번째만 실행범위 기반 분류

1. 트랜잭션 (Transaction)

  • 논리적인 작업의 단위
  • 여러 SQL 명령을 하나의 작업 단위로 처리
  • 완전하게 수행되거나 전혀 수행되지 않아야 함 (Atomicity)

트랜잭션 종료 방식

종료 방법설명
COMMIT변경 사항을 영구 반영
ROLLBACK변경 사항을 취소
DDL / DCL 실행자동으로 커밋 발생 (암묵적 커밋)

2. 트랜잭션 흐름 예시 (텍스트 그림)

[BEGIN]
   ├── DML(1): DELETE FROM emps WHERE department_id = 20;
   ├── SAVEPOINT delete_20
   ├── DML(2): DELETE FROM emps WHERE department_id = 30;
   ├── ROLLBACK TO delete_20  -- 30번 삭제 취소, 20번 삭제는 유지
   └── COMMIT                 -- 전체 확정 저장
[END]

예시 코드

DELETE FROM emps WHERE department_id = 20;
SAVEPOINT delete_20;

DELETE FROM emps WHERE department_id = 30;

ROLLBACK TO delete_20;
COMMIT;

3. 세이브포인트 (SAVEPOINT)

  • 트랜잭션 내에서 부분 롤백 지점을 설정
  • 이후 특정 시점까지 롤백 가능
SAVEPOINT save1;
...
ROLLBACK TO save1;

4. 자동 커밋 (Auto Commit)

  • Oracle에서는 기본적으로 자동 커밋이 비활성화되어 있음
  • 수동으로 COMMIT 해야만 변경 사항이 저장됨

오토커밋 설정 (SQL*Plus 기준)

SET AUTOCOMMIT ON;   -- 자동 커밋 활성화
SET AUTOCOMMIT OFF;  -- 자동 커밋 비활성화 (기본값)

자동 커밋은 실수로 인한 데이터 손실 위험이 높기 때문에 권장되지 않음

5. 데이터 잠금 (LOCK)

개념

  • 여러 사용자의 동시 작업에서 일관된 읽기/쓰기 보장
  • DML 수행 시 자동으로 락이 걸리며, 명시적으로도 요청 가능

테이블 락

LOCK TABLE employees IN EXCLUSIVE MODE;
  • EXCLUSIVE MODE: 다른 사용자의 읽기/쓰기 모두 차단

주요 테이블 락 모드

모드설명
ROW SHARE가장 낮은 수준의 잠금 (다른 사용자의 DML 허용)
ROW EXCLUSIVEINSERT, DELETE, UPDATE 수행 시 기본값
SHARE UPDATE드물게 사용되며 ROW SHARE와 유사
SHARE동시에 SELECT는 가능하지만 DML은 제한
SHARE ROW EXCLUSIVESHARE와 EXCLUSIVE의 중간 단계
EXCLUSIVE읽기, 쓰기 모두 제한 (완전 잠금)

락 모드를 사용할 때는 트랜잭션 종료(COMMIT/ROLLBACK) 전까지 잠금이 유지됨

profile
은채의 성장통
인생 별거 없어
이전 포스트

22일차 - 분석함수 & join

다음 포스트

23일차 - 테이블, 뷰

0개의 댓글