안녕하세요. 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>::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )


먼저, 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 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022 

책구매링크.png

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 38722
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 20805
2294 SQL강좌: 12-1. 인덱스 생성과 관리 - 인덱스(Index) 소개 file 코난(김대우) 2023.08.18 123
2293 SQL강좌: 11-8. 관계형 데이터베이스 이해 - 데이터베이스 구현 코난(김대우) 2023.08.18 56
2292 SQL강좌: 11-7. 관계형 데이터베이스 이해 - 데이터베이스 물리적 설계 코난(김대우) 2023.08.18 64
2291 SQL강좌: 11-6. 관계형 데이터베이스 이해 - 데이터베이스 논리적 설계 코난(김대우) 2023.08.18 49
2290 SQL강좌: 11-5. 관계형 데이터베이스 이해 - 데이터베이스 설계 요구사항(Requirements) 분석과 개념적 설계 코난(김대우) 2023.08.18 56
2289 SQL강좌: 11-4. 관계형 데이터베이스 이해 - 데이터베이스 설계 목표와 고려사항 코난(김대우) 2023.08.18 49
2288 SQL강좌: 11-3. 관계형 데이터베이스 이해 - 데이터베이스 설계 단계 file 코난(김대우) 2023.08.18 55
2287 SQL강좌: 11-2. 관계형 데이터베이스 이해 - 데이터베이스 생명주기 file 코난(김대우) 2023.08.18 53
2286 SQL강좌: 11-1. 관계형 데이터베이스 이해 - 관계형 모델 코난(김대우) 2023.08.18 74
» SQL강좌: 10-4. T-SQL 프로그래밍 - 공통 테이블 식(CTE-Common Table Expression) 코난(김대우) 2023.08.18 101
2284 SQL강좌: 10-3. T-SQL 프로그래밍 - 변수, 조건문(IF-ELSE), 반복문(WHILE), CASE 표현식 코난(김대우) 2023.08.18 92
2283 SQL강좌: 10-2. T-SQL 프로그래밍 - 일괄처리(Batch) 코난(김대우) 2023.08.18 67
2282 SQL강좌: 10-1. T-SQL 프로그래밍 - 소개 코난(김대우) 2023.08.18 69
2281 SQL강좌: 9-3. 커서 - 현업에서 커서 사용 및 주의사항 코난(김대우) 2023.08.18 102
2280 SQL강좌: 9-2. 커서 - 커서 구문 코난(김대우) 2023.08.18 69
2279 SQL강좌: 9-1. 커서(CURSOR) - 커서 소개 코난(김대우) 2023.08.18 101
2278 SQL강좌: 8-7. 저장 프로시저 - 프로시저 캐시와 RECOMPILE 코난(김대우) 2023.08.18 85
2277 SQL강좌: 8-6. 저장 프로시저 - 종속성(Dependency) 확인 코난(김대우) 2023.08.18 101
2276 SQL강좌: 8-5. 저장 프로시저 - OUTPUT 파라미터 코난(김대우) 2023.08.18 90
2275 SQL강좌: 8-4. 저장 프로시저 - 동적 SQL(Dynamic SQL)과 SQL 주입(Injection) 공격 코난(김대우) 2023.08.18 123





XE Login