안녕하세요. SQLER의 코난 김대우입니다.
이번 강좌에서는, 10-4. T-SQL 프로그래밍 - 공통 테이블 식(CTE-Common Table Expression)을 진행 하겠습니다.
SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
이번에 진행할 강좌는 T-SQL 프로그래밍 - 공통 테이블 식(CTE - common table expression)
TL;DR
T-SQL 프로그래밍의 공통 테이블 식(CTE - Common Table Expression)은 임시 테이블과 유사하지만, 재귀 처리가 가능한 간결한 표현식입니다. CTE의 기본 구조와 재귀 처리로 계층 데이터를 다루는 방법을 진행합니다.
CTE는 임시 테이블과 유사하지만, 재귀 처리가 가능한 특징이 있는 간결한 표현식(Expression)입니다. 간략히 예제 먼저 살펴보고 진행하겠습니다.
CTE 쿼리 예제
USE AdventureWorks; GO -- 테이블이 존재하면 삭제 IF OBJECT_ID(N'dbo.CTE_products', N'U') IS NOT NULL DROP TABLE dbo.CTE_products; GO -- products 테이블 생성 CREATE TABLE CTE_products ( product_id INT PRIMARY KEY, product_name NVARCHAR(10), price DECIMAL(10, 0) ); GO -- products 테이블에 데이터 삽입 INSERT INTO CTE_products (product_id, product_name, price) VALUES (1, N'초코파이', 1200), (2, N'칸쵸', 800), (3, N'포테토칩', 1800), (4, N'꼬북칩', 1300); GO SELECT * FROM CTE_products; GO -- CTE 쿼리 수행 WITH Expensive_product_CTE (product_name, price) AS ( SELECT product_name, price FROM CTE_products WHERE price > 1000 ) -- CTE를 참조하는 쿼리 SELECT product_name, price FROM Expensive_product_CTE; GO
일반적인 CTE 구문입니다. 그럼 CTE 구문 정보를 살펴보고 CTE 쿼리를 알아보겠습니다.
CTE 구문 정보
구문 정보는 다음과 같습니다.
[ WITH <common_table_expression> [ ,...n ] ] <common_table_expression>::= |
먼저, WITH 구문으로 CTE 이름과 리턴할 컬럼을 정의합니다.
WITH Expensive_product_CTE (product_name, price)
다음은 CTE 괄호 안에서 CTE로 수행할 SQL 구문이나 식을 작성합니다. 뷰나 임시테이블과 유사합니다.
( SELECT product_name, price FROM CTE_products WHERE price > 1000 )
마지막으로 CTE를 FROM 절에서 참조하는 SQL 구문을 작성하고 실행합니다.
-- CTE를 참조하는 쿼리 SELECT product_name, price FROM Expensive_product_CTE
이렇게 CTE를 생성하고 참조해 사용 가능합니다.
재귀 공통 테이블 식(Recursive CTE)
CTE의 큰 장점이자 차별화는 재귀 처리(Recursive)가 가능합니다. 앵커 쿼리(Anchor query)와 재귀 멤버(Recursive member)를 보통 UNION으로 연결해 재귀적으로 CTE를 자기 참조하면서, 계층 데이터 처리 등에 유용하게 사용할 수 있습니다.
이전 2-9. JOIN - 테이블 연결 강좌에서 진행한 SELF JOIN과 유사하지만, CTE가 좀 더 직관적입니다.
예제를 통해 재귀 CTE를 살펴보겠습니다.
재귀 CTE 예제
-- 재귀 CTE - 계층 구조 쿼리에 유용 IF OBJECT_ID(N'dbo.employees', N'U') IS NOT NULL DROP TABLE dbo.employees; --직원 예제 테이블 생성 CREATE TABLE employees ( employee_id INT, employee_name NVARCHAR(10), manager_id INT ); GO INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (1, N'구교환', 2), (2, N'송중기', 3), (3, N'이정재', NULL), (4, N'박은빈', 3), (5, N'김태리', 4); GO --테스트 조회 SELECT * FROM employees; GO -- 재귀 CTE 구문 WITH Employees_CTE(employee_id, employee_name, manager_id, job_level) AS ( -- 앵커 쿼리 SELECT employee_id, employee_name, manager_id, 0 FROM employees WHERE manager_id IS NULL UNION ALL -- 재귀 멤버 쿼리 SELECT e.employee_id, e.employee_name, e.manager_id, c.job_level + 1 FROM employees AS e INNER JOIN Employees_CTE AS c ON e.manager_id = c.employee_id ) SELECT employee_id, employee_name, manager_id, job_level FROM Employees_CTE ORDER BY job_level; GO 결과 employee_id employee_name manager_id job_level ----------- ------------- ----------- ----------- 3 이정재 NULL 0 2 송중기 3 1 4 박은빈 3 1 5 김태리 4 2 1 구교환 2 2
이렇게, 직원 테이블에서 계층적 구조로 직업 레벨을 출력하려고 할 때 사용할 수 있습니다. 앵커 쿼리는 계층 구조 최상위 로우를 설정하고, UNION을 수행해 직원 테이블과 CTE JOIN 결과를 합칩니다. 최종적으로, CTE 테이블을 조회하면 계층 구조 설정을 볼 수 있습니다.
☑️ 챗GPT 활용: 공통 테이블 식(CTE - common table expression)은 ANSI 표준인가요?
SQL 강좌 책 구매
강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다.