서브쿼리와 CTE

하나의 쿼리로 풀기 어려운 문제는 "쿼리 안의 쿼리"인 서브쿼리나, 이름을 붙인 임시 결과인 CTE 로 단계를 나눠 풀 수 있습니다. 복잡한 로직을 읽기 쉽게 정리하는 강력한 도구입니다.

학습 목표

• 스칼라·인라인뷰·상관 서브쿼리를 구분합니다.
• IN, EXISTS 로 존재 여부를 검사합니다.
• WITH 절(CTE)로 쿼리를 단계적으로 작성합니다.
• 재귀 CTE로 계층 구조를 다룹니다.

서브쿼리의 종류

스칼라 서브쿼리 (단일 값)

하나의 값을 반환해 비교에 사용합니다.

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

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

서브쿼리 결과를 하나의 테이블처럼 사용합니다.

SELECT dept, 평균급여
FROM (
  SELECT department AS dept, AVG(salary) AS 평균급여
  FROM Employees
  GROUP BY department
) t
WHERE 평균급여 > 3500000;

상관 서브쿼리

바깥 쿼리의 행마다 안쪽 쿼리가 다시 실행됩니다.

-- 자기 부서 평균보다 많이 받는 직원
SELECT e.name, e.salary, e.department
FROM Employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM Employees
  WHERE department = e.department   -- 바깥의 e를 참조
);

💡 TIP — 상관 서브쿼리는 바깥 행 수만큼 반복 실행되므로 데이터가 많으면 느려질 수 있습니다. 같은 결과를 윈도우 함수(9강)나 JOIN으로 더 빠르게 풀 수 있는 경우가 많습니다.

IN vs EXISTS

-- IN: 주문이 있는 고객
SELECT name
FROM Customers
WHERE id IN (SELECT customer_id FROM Orders);

-- EXISTS: 동일하지만 "존재 여부"만 확인
SELECT name
FROM Customers c
WHERE EXISTS (
  SELECT 1 FROM Orders o WHERE o.customer_id = c.id
);

구분	특징
IN	목록 전체를 만든 뒤 비교, NULL에 민감
EXISTS	첫 매칭을 찾으면 즉시 참, 보통 효율적
NOT EXISTS	"없는 것"을 찾을 때 안전 (NULL 문제 회피)

⚠️ 주의 — NOT IN (서브쿼리) 에서 서브쿼리 결과에 NULL 이 하나라도 있으면 전체가 의도와 다르게 빈 결과가 됩니다. "없는 것"을 찾을 때는 NOT EXISTS 가 안전합니다.

CTE — WITH 절로 이름 붙이기

CTE(Common Table Expression)는 서브쿼리에 이름을 붙여 본 쿼리 위에 미리 정의하는 방식입니다. 인라인 뷰보다 읽기 쉽습니다.

WITH DeptAvg AS (
  SELECT department, AVG(salary) AS avg_sal
  FROM Employees
  GROUP BY department
)
SELECT e.name, e.salary, d.avg_sal
FROM Employees e
JOIN DeptAvg d ON e.department = d.department
WHERE e.salary > d.avg_sal;

CTE는 여러 개를 콤마로 이어 정의할 수도 있습니다.

WITH A AS (...),
     B AS (...)
SELECT * FROM A JOIN B ON ...;

재귀 CTE — 계층 구조

조직도나 카테고리 트리처럼 부모-자식 관계를 펼칠 때 사용합니다.

WITH OrgTree AS (
  -- 앵커: 최상위(상사가 없는 사람)
  SELECT id, name, manager_id, 1 AS lvl
  FROM Employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 재귀: 부모를 찾은 사람들을 한 단계씩 붙임
  SELECT e.id, e.name, e.manager_id, t.lvl + 1
  FROM Employees e
  JOIN OrgTree t ON e.manager_id = t.id
)
SELECT * FROM OrgTree
ORDER BY lvl;

💡 TIP — 재귀 CTE가 무한 반복되지 않도록 종료 조건(부모를 못 찾으면 멈춤)이 명확해야 합니다. 기본 재귀 횟수 제한은 OPTION (MAXRECURSION n) 으로 조정합니다.

요약

• 스칼라 서브쿼리는 단일 값, 인라인 뷰는 FROM 절의 임시 테이블입니다.
• 상관 서브쿼리는 바깥 행마다 실행되어 느릴 수 있습니다.
• "있는 것"은 EXISTS, "없는 것"은 NOT EXISTS 가 안전합니다.
• WITH(CTE)는 서브쿼리에 이름을 붙여 가독성을 높입니다.
• 재귀 CTE는 앵커 + UNION ALL + 재귀 항으로 계층 구조를 펼칩니다.

연습문제

1. 전체 평균 급여보다 많이 받는 직원을 스칼라 서브쿼리로 조회하세요.
2. 한 번도 주문하지 않은 고객을 NOT EXISTS 로 찾아 보세요.
3. CTE를 사용해 부서별 평균보다 많이 받는 직원을 출력하세요.
4. 재귀 CTE로 특정 직원의 모든 하위 직원을 펼쳐 보세요.

힌트 — 2번은 WHERE NOT EXISTS (SELECT 1 FROM Orders ...), 4번은 앵커에서 시작 직원을 지정하세요.