[MySQL] WITH (CTE)
CTE(Common Table Expressions)는 복잡한 SQL 쿼리를 단순화하는 데 사용되는 일시적인 결과 집합입니다. CTE는 쿼리 실행 시 한 번 정의되며, 그 쿼리 내에서만 사용할 수 있는, 복잡한 서브쿼리를 보다 읽기 쉽게 만듭니다. CTE를 사용하면 재사용 가능하고 유지 관리가 쉬운 코드를 작성할 수 있습니다.
일반적인 사용 시나리오와 장점
CTE는 데이터 집계, 임시 결과 집합의 생성, 계층적 또는 재귀적 쿼리의 구현 등 복잡한 쿼리를 간단하게 만드는 데 유용합니다. CTE의 장점은 쿼리의 가독성과 유지보수성을 향상시키는 것입니다.
WITH 구문의 기본 형태
기본적인 WITH 구문은 다음과 같습니다:
WITH CTE_Name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT * FROM CTE_Name;
기본 WITH (CTE) 문법과 예제
단일 CTE 작성 방법
단일 CTE를 작성하는 방법은 간단합니다. 예를 들어, 특정 조건을 만족하는 직원 목록을 받으려면 다음과 같이 작성할 수 있습니다:
WITH EmployeeCTE AS (
SELECT *
FROM Employees
WHERE Department = 'Finance'
)
SELECT * FROM EmployeeCTE;
다중 CTE 연쇄 및 결합 사용 방법
여러 CTE를 연쇄적으로 사용할 수 있으며, 이전 CTE의 결과를 다음 CTE에서 활용할 수 있습니다:
WITH CTE_One AS (
SELECT *
FROM Table1
),
CTE_Two AS (
SELECT *
FROM Table2
WHERE Column1 IN (SELECT Column1 FROM CTE_One)
)
SELECT * FROM CTE_Two;
WITH RECURSIVE를 이용한 재귀적 쿼리 작성 방법
WITH RECURSIVE를 사용하여 재귀적 쿼리를 작성할 수 있습니다. 예를 들어, 조직도 내의 모든 상하 관계를 조회하려면 다음과 같이 쿼리를 작성할 수 있습니다:
WITH RECURSIVE EmployeePath AS (
SELECT EmployeeID, ManagerID, 1 AS Depth
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ManagerID, ep.Depth + 1
FROM Employees e
INNER JOIN EmployeePath ep ON e.ManagerID = ep.EmployeeID
)
SELECT * FROM EmployeePath;
성능 최적화 및 사용 시 주의사항
CTE와 일반 서브쿼리의 성능 비교
CTE는 가독성과 코드의 구조화를 향상시키지만, 항상 서브쿼리보다 성능상 이점을 제공하는 것은 아닙니다. CTE와 서브쿼리 간의 성능을 비교할 때, 실행 계획을 확인하여 비용이 더 낮은 방식을 선택해야 합니다.
재귀 CTE의 사용 시 주의사항과 한계
재귀 CTE는 대량의 데이터에서 메모리 소모가 커질 수 있으므로, 사용 시 주의가 필요합니다. 또한, 무한 루프에 빠지지 않도록 조건을 적절히 설정해야 합니다.
유지보수 및 가독성을 위한 사용 팁
- CTE의 이름을 명확하게 지으세요. 가능하면, CTE가 반환하는 데이터의 내용을 설명하는 이름을 사용합니다.
- 과도한 CTE 사용은 오히려 가독성을 해칠 수 있으니, 적절히 사용하세요.
실제 사용 사례
계층적 데이터 조회
CTE는 조직의 계층 구조나 제품 분류와 같은 계층적 데이터를 조회하는 데 매우 유용합니다.
복잡한 집계 데이터의 단계별 구분
복잡한 집계를 필요로 하는 보고서의 작성 시, CTE를 사용하여 데이터를 단계별로 분해함으로써 보다 명확하고 관리하기 쉬운 쿼리를 작성할 수 있습니다.
일시적 테이블 대신 CTE 사용의 이점
임시 테이블 대신 CTE를 사용하면, 서버에 대한 부하를 줄이고 성능을 향상시킬 수 있는 경우가 많습니다. 또한 CTE는 선언적으로 작성되므로, 의도가 명백하고 코드의 가독성이 향상됩니다.