서브쿼리와 CTE — 복잡한 조건 처리하기

서브쿼리 기본

쿼리 안에 중첩된 쿼리입니다.

-- 평균 급여보다 많이 받는 직원
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 서브쿼리는 괄호 안에
-- 단일 값 반환 (스칼라 서브쿼리)
SELECT
    name,
    salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

---

IN과 서브쿼리

-- 주문이 있는 고객만 조회
SELECT name FROM customers
WHERE id IN (
    SELECT DISTINCT customer_id FROM orders
);

-- 주문이 없는 고객
SELECT name FROM customers
WHERE id NOT IN (
    SELECT DISTINCT customer_id FROM orders WHERE customer_id IS NOT NULL
);

---

EXISTS

행의 존재 여부를 확인합니다. IN보다 대용량 데이터에 효율적입니다.

-- 주문이 있는 고객
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- 50000원 이상 주문한 고객
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
    AND o.amount >= 50000
);

---

FROM 절 서브쿼리 (인라인 뷰)

-- 부서별 평균 급여를 임시 테이블처럼 사용
SELECT
    e.name,
    e.salary,
    dept_avg.avg_salary
FROM employees e
JOIN (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg ON e.department = dept_avg.department
WHERE e.salary > dept_avg.avg_salary;

---

CTE (Common Table Expression)

WITH 절로 이름 붙인 임시 결과를 여러 곳에서 재사용합니다.

-- 위 쿼리를 CTE로 개선
WITH dept_averages AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
above_average AS (
    SELECT e.name, e.department, e.salary, d.avg_salary
    FROM employees e
    JOIN dept_averages d ON e.department = d.department
    WHERE e.salary > d.avg_salary
)
SELECT * FROM above_average
ORDER BY department, salary DESC;

---

재귀 CTE

트리 구조나 계층 데이터를 조회합니다.

-- 조직도: 모든 직속/간접 부하 찾기
WITH RECURSIVE subordinates AS (
    -- 기준점: 특정 직원
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE id = 1

    UNION ALL

    -- 재귀: 직속 부하 계속 추가
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY depth;

---

윈도우 함수 맛보기

-- 급여 순위 (RANK)
SELECT
    name,
    department,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS overall_rank,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- 결과
-- name | department | salary  | overall_rank | dept_rank
-- -----|------------|---------|-------------|----------
-- 박민준 | 개발팀    | 6500000 | 1           | 1
-- 정태양 | 개발팀    | 5800000 | 2           | 2
-- ...

---

정리

기법	용도
스칼라 서브쿼리	단일 값 반환
IN (서브쿼리)	목록 조건
EXISTS (서브쿼리)	존재 여부 확인
인라인 뷰	FROM 절 임시 테이블
CTE (WITH)	재사용 가능한 임시 결과
재귀 CTE	계층 데이터 조회

다음 편에서는 INSERT, UPDATE, DELETE — 데이터를 추가, 수정, 삭제하는 방법을 배웁니다.