🚀MSSQL에서 CTE (Common Table Expression) 활용법

MSSQL에서 CTE(Common Table Expression)는 임시적인 결과 집합을 만들어 SQL 쿼리의 가독성을 높이고, 재귀 쿼리를 작성할 때 유용하게 사용할 수 있습니다.
이번 포스팅에서는 CTE의 개념, 기본 사용법, 활용 사례를 쉽게 설명해 보겠습니다. ✅

---

🔍 1. CTE란?

CTE (Common Table Expression)는
쿼리 안에서 임시적인 결과 집합을 만들어, 마치 뷰(View)처럼 사용할 수 있는 기능입니다.

🔹 WITH 키워드를 사용하여 정의하며,
🔹 SELECT, INSERT, UPDATE, DELETE 문과 함께 사용할 수 있습니다.

✅ CTE의 장점

• 쿼리 가독성 향상: 서브쿼리를 줄이고, SQL을 더 읽기 쉽게 만든다.
• 재귀 쿼리 지원: 계층 구조 데이터를 쉽게 조회할 수 있다.
• 임시 데이터 활용: 한 번 정의한 결과 집합을 여러 번 재사용할 수 있다.

---

🛠 2. CTE 기본 사용법

✅ CTE 기본 구조

WITH CTE명 (컬럼1, 컬럼2, ...) AS (
    -- CTE 내부 쿼리
    SELECT 컬럼1, 컬럼2, ...
    FROM 테이블명
    WHERE 조건
)
-- CTE 결과를 활용한 메인 쿼리
SELECT * FROM CTE명;

🔹 기본 예제: 직원 테이블에서 급여가 5000 이상인 직원 조회

WITH HighSalaryEmployees AS (
    SELECT EmployeeID, Name, Salary
    FROM Employees
    WHERE Salary >= 5000
)
SELECT * FROM HighSalaryEmployees;

✅ CTE를 사용하면 복잡한 서브쿼리를 줄일 수 있어 가독성이 좋아집니다.

---

🔄 3. CTE 활용 예제

🎯 1) CTE로 여러 번 재사용하기

CTE를 활용하면 동일한 서브쿼리를 여러 번 사용할 때 유용합니다.

WITH HighSalaryEmployees AS (
    SELECT EmployeeID, Name, Salary
    FROM Employees
    WHERE Salary >= 5000
)
SELECT COUNT(*) AS EmployeeCount FROM HighSalaryEmployees;

SELECT Name FROM HighSalaryEmployees WHERE Name LIKE 'J%';

✅ HighSalaryEmployees CTE를 두 번 사용했지만, 한 번만 정의하면 됨!

---

🎯 2) 재귀 CTE 활용 (계층 구조 데이터 조회)

CTE는 재귀 쿼리를 작성할 때도 매우 유용합니다.
예를 들어, 조직도(상사-부하 관계) 데이터를 조회하는 경우에 사용할 수 있습니다.

WITH EmployeeHierarchy AS (
    -- 기본 계층 (최상위 관리자)
    SELECT EmployeeID, Name, ManagerID, 1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- 재귀적으로 부하 직원 추가
    SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy ORDER BY Level;

✅ 재귀 CTE를 사용하면 계층 구조 데이터를 쉽게 조회할 수 있습니다.

---

🎯 3) CTE를 이용한 데이터 업데이트

CTE는 단순 조회뿐만 아니라, 업데이트(UPDATE), 삭제(DELETE), 삽입(INSERT) 도 가능합니다.

✅ 급여 5000 이상인 직원의 급여를 10% 인상하는 예제

WITH HighSalaryEmployees AS (
    SELECT EmployeeID, Salary
    FROM Employees
    WHERE Salary >= 5000
)
UPDATE Employees
SET Salary = Salary * 1.1
FROM Employees e
INNER JOIN HighSalaryEmployees h ON e.EmployeeID = h.EmployeeID;

✅ CTE를 활용하면 대상 데이터를 명확하게 정의하고 업데이트할 수 있습니다.

---

🎯 4. CTE vs 서브쿼리 vs 뷰 비교

방식	설명	장점	단점
CTE	임시적인 결과 집합	가독성 향상, 재귀 지원, 여러 번 재사용 가능	실행 후 자동 삭제
서브쿼리	쿼리 내부에서 중첩하여 사용	간단한 연산에 적합	복잡하면 가독성이 떨어짐
뷰(View)	데이터베이스에 저장되는 가상 테이블	여러 쿼리에서 재사용 가능	데이터베이스에 저장 필요

✅ CTE는 뷰처럼 별도 저장이 필요 없으며, 서브쿼리보다 가독성이 좋습니다!

---

🎯 5. 마무리

🔹 CTE(Common Table Expression)는 SQL 쿼리를 더 가독성 좋게 만들고, 재귀 쿼리를 쉽게 작성할 수 있도록 도와줍니다.
🔹 일반적인 서브쿼리보다 효율적이며, 뷰를 만들 필요 없이 임시로 데이터를 가공할 때 유용합니다.
🔹 재귀 CTE를 활용하면 조직도나 계층 구조 데이터를 손쉽게 조회할 수 있습니다.

💡 MSSQL에서 CTE를 활용하면 복잡한 SQL을 더 쉽고, 효율적으로 작성할 수 있습니다! 🚀

---