11 공통 테이블 식, CTE

공통 테이블 식(Common Table Expression, CTE)

주로 데이터베이스에 없는 테이블을 필요할 때 사용하며, 바로 다음에 실행할 SELECT 문에만 사용해야 한다는 특징이 있다.

공통 테이블 식은 목적에 따라 '일반 공통 테이블 식'과 '재귀 공통 테이블 식'으로 나뉜다.

 

일반 CTE

CTE 안에서 UNION 문, UNION ALL 문, INTERSECT 문, EXCEPT 문을 사용해 여러 개의 일반 CTE 쿼리를 결합할 수 있다.

일반 CTE는 복잡한 쿼리를 단순하게 만들 때 사용하기 좋다

 

CTE의 기본 형식

WITH [CTE_테이블 이름] (열 이름 1, 열 이름 2, ...)
AS
(
  <SELECT 문>
)
SELECT [열 이름] FROM [CTE_테이블 이름];

 

CTE 사용 방법

WITH 문을 입력 한 후 CTE_테이블 이름과 같이 사용할 테이블의 이름을 지정하고

(열 이름 1, 열 이름 2, ...)와 같이 열 목록을 정의한다.

열 목록에 입력할 열 이름들은 AS(...)에 포함한 <SELECT 문>이 반환하는 열을 의미한다.

 

CTE를 WITH cte_stock_price (date, symbol, price)로 정의하고

SELECT 문에서 SELECT * FROM cte_stock_price WHERE symbol = 'MSFT'와 같이 CTE 테이블을 참조해 데이터를 검색하는 쿼리

WITH cte_stock_price (date, symbol, price)
AS
(
	SELECT date, symbol, [close] FROM stock
	WHERE date >= '2021-01-01' AND date <= '2021-01-10'
)
SELECT * FROM cte_stock_price WHERE symbol = 'MSFT'

-- 같은 값 나오는 쿼리
SELECT date, symbol, [close] AS price FROM stock
WHERE symbol = 'MSFT' and date >= '2021-01-01' AND date <= '2021-01-10'

 

CTE에서 정의한 열 개수와 CTE의 SELECT 문에서 얻은 열 목록이 다르면 오류가 발생한다.

오류 발생하는 쿼리: CTE 열 목록(date, symbol, price) 정의하고 SELECT 문에서는 date, symbol, [open], [close]를 사용해 열 개수가 맞지 않아 오류가 발생한 쿼리

WITH cte_stock_price (date, symbol, price)
AS
(
	SELECT date, symbol, [open], [close] FROM stock
	WHERE date >= '2021-01-01' AND date <= '2021-01-10'
)
SELECT * FROM cte_stock_price WHERE symbol = 'MSFT'

---

UNION 문과 UNION ALL 문으로 CTE 결합하기

UNION 문과 UNION ALL 문의 차이점: 중복을 제거한 행 포함 여부

 

UNION ALL 문으로 CTE 결합

WITH cte_stock_price (date, symbol, price)
AS
(
	SELECT date, symbol, [close] FROM stock
	WHERE date >= '2021-01-01' AND date <= '2021-01-10'
	UNION ALL
	SELECT date, symbol, [close] FROM stock
	WHERE date >= '2021-02-01' AND date <= '2021-02-07'
)
SELECT * FROM cte_stock_price WHERE symbol = 'MSFT'

---

UNION 과 UNION ALL 문

중복 데이터를 제거하는 연산을 포함하는 UNION 문 대신 UNION ALL 문을 사용하는 것이 좋다.

UNION 문은 성능 문제를 일으키기 쉬우므로 꼭 필요한 경우가 아니라면 사용하지 않는 것이 좋다.

---

INTERSECT 문으로 CTE 결합하기

CTE에서 INTERSECT 문을 사용하는 방법은 내부 조인과 비슷하지만,

내부 조인의 경우 테이블 사이의 조인 조건에 맞는 데이터를 반환하고

INTERSECT 문은 각 쿼리에서 반환한 결과와 중복 결과를 걸래 내 반환한다는 차이점이 있다

 

INTERSECT 문을 사용해 CTE에 정의한 2개의 SELECT 문 결과에서 중복 결과를 검색하는 쿼리

WITH cte_stock_price (date, symbol, price)
AS 
(
	SELECT date, symbol, [close] FROM stock
	WHERE date >= '2021-01-01' AND date <= '2021-01-10'
	INTERSECT
	SELECT date, symbol, [close] FROM stock
	WHERE date >= '2021-01-07' AND date <= '2021-01-20'
)
SELECT * FROM cte_stock_price WHERE symbol = 'MSFT'

1월 7일부터 1월 10일까지의 데이터가 중복하는 구간이 있으며 CTE는 중복된 결과를 반환한다.

그리고 SELECT에서 CTE 결과 중에 symbol이 'MSFT'인 결과만 검색한다.

 

같은 테이블을 사용했지만  서로 다른 테이블을 사용할 때

예를 들어 지역 A와 지역 B에서 주식 테이블을 따로 관리하는 상황이라면 INTERSECT 문과 CTE로 풀기 복합한 쿼리를 쉽게 해결할 수 있다.

INTERSECT 문과 CTE를 사용하면 각 SELECT 문에서 지역 데이터를 검색한 다음 결과에서 중복된 데이터만 검색할 수 있다.

---

EXCEPT 문으로 CTE 결합하기

EXCEPT 문으로 CTE를 결합하는 방식은 NOT IN 문과 비슷하지만,

EXCEPT 문은 결괏값에서 중복을 제거한 유일한 행을 반환하고

NOT IN 문은 중복을 제거하지 않고 반환하는 점이 다르다.

 

또한 CTE에서 먼저 작성한 쿼리 기준으로, 그 다음 작성한 SELECT 문 쿼리와 중복되지 않는 데이터를 반환한다.

 

2번째 쿼리는 1번째 쿼리에서 CTE에 정의한 SELECT 문의 순서를 변경한 것

--2021-01-01부터 2021-01-06까지의 데이터 출력
WITH cte_stock_price (date, symbol, price)
AS
(
	SELECT date, symbol, [close] FROM stock
	WHERE date >= '2021-01-01' AND date <= '2021-01-10'
	EXCEPT
	SELECT date, symbol, [close] FROM stock
	WHERE date >= '2021-01-07' AND date <= '2021-01-20'
)
SELECT * FROM cte_stock_price WHERE symbol = 'MSFT';


-- CTE 내부의 SELECT 순서 변경
-- 2021-01-01부터 2021-01-06과 2021-01-11부터 2021-01-20까지의 데이터가 포함
WITH cte_stock_price (date, symbol, price)
AS
(
	SELECT date, symbol, [close] FROM stock
	WHERE date >= '2021-01-01' AND date <= '2021-01-20'
	EXCEPT
	SELECT date, symbol, [close] FROM stock
	WHERE date >= '2021-01-07' AND date <= '2021-01-10'
)
SELECT * FROM cte_stock_price WHERE symbol = 'MSFT';

---

재귀 CTE

CTE 결과를 CTE 내부의 쿼리에서 재사용함으로써 반복 실행하는 쿼리 구조를 갖는다.

주로 계층 데이터를 검색할 때 많이 사용한다.

 

재귀 CTE의 기본 형식

WITH [CTE_테이블 이름] (열 이름 1, 열 이름 2, ...)
AS(
	<SELECT * FROM 테이블 A> -- (쿼리 1)앵커 멤버: 자기 자신 CTE를 참조하지 않는 멤버
    UNION ALL
    <SELECt * FROM 테이블 B JOIN CTE_테이블 이름> -- (쿼리 2)재귀 멤버
)
SELECT * FROM [CTE_테이블 이름];

재귀 CTE는 적어도 2개의 CTE 쿼리가 필요하다.

각 쿼리는 '앵커 멤버', '재귀 멤버'를 포함해야 한다.

 

주의사항으로 앵커 멤버는 1번째 재귀 멤버 앞에 있어야 하고,

재귀 멤버의 열 자료형은 반드시 앵커 멤버의 열 자료형과 일치해야 한다.

 

앵커 멤버과 재귀 멤버는 여러 개 정의할 수 있다

 

재귀 CTE의 실행순서

1. 최조의 쿼리 1을 실행한다. 이때 쿼리 2의 기본값은 0으로 초기화한다.

2. 이어 쿼리 2를 실행한다. 쿼리 2의 기본값은 1만큼 증가한다. 쿼리 1의 결과 행 수만큼 쿼리 2에서 CTE_테이블 이름을 재귀 호출하고, 쿼리 2의 기본값이 1씩 증가하면서 쿼리 1의 결과 수행까지 도달해 결과가 더 없다면 재귀 호출을 중단한다.

3. 외부 SELECT 문에서 과정 1, 2를 통해 만든 CTE 누적 결과를 검색한다.

 

재귀 CTE 실습 테이블 생성과 데이터 입력

IF OBJECT_ID('doit_cte_recursive', 'U') IS NOT NULL
DROP TABLE dbo.doit_cte_recursive
GO -- GO문을 기준으로 각각 독립된 쿼리처럼 실행된다
CREATE TABLE dbo.doit_CTE_recursive
(
	EmployeeID int NOT NULL PRIMARY KEY,
	FirstName varchar(50) NOT NULL,
	LastName varchar(50) NOT NULL,
	ManagerID int NULL
)
GO
INSERT INTO doit_CTE_recursive VALUES (101, 'ken', 'Sanchez', NULL)
INSERT INTO doit_CTE_recursive VALUES (102, 'Terri', 'Duffy', 101)
INSERT INTO doit_CTE_recursive VALUES (103, 'Roberto', 'Tamburello', 101)
INSERT INTO doit_CTE_recursive VALUES (104, 'Rob', 'Walters', 102)
INSERT INTO doit_CTE_recursive VALUES (105, 'Gail', 'Erickson', 102)
INSERT INTO doit_CTE_recursive VALUES (106, 'Jossef', 'Goldberg', 103)

SELECT * FROM doit_CTE_recursive

 

생성한 doit_cte_recursive 테이블에서 EmployeeID, ManagerID 값을 확인해 EmpLevel을 구하는 쿼리

WITH
	cte_recursive (EmpID, FirstName, LastName, MgrID, EmpLevel)
	AS
	(
	SELECT EmployeeID, FirstName, LastName, ManagerID, 1
	FROM doit_CTE_recursive WHERE ManagerID IS NULL

	UNION ALL

	SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, r.EmpLevel + 1
	FROM doit_CTE_recursive AS e 
		INNER JOIN cte_recursive AS r ON e.ManagerID = r.EmpID
	)
SELECT
	FirstName + ' ' + LastName AS FullName, EmpLevel,
	(SELECT FirstName + ' ' + LastName From doit_CTE_recursive
	WHERE EmployeeID = cte_recursive.MgrID) AS Manager
FROM cte_recursive
ORDER BY EmpLevel, MgrID

1번째 SELECT 문: SELECT EmployeeID, FirstName, LastName, ManagerID를 보면 최상위 직원을 검색해 EmpLevel 1로 지정한다.

2번째 SELECT 문: 1번째 SELECT 문이 반환한 값을 doit_cte_recursive 테이블과 조인하면서 바로 앞 행의 EmpLevel에 1을 더하며 재귀 호출한다

이때 ManagerID와 EmpID가 같은 값을 조인 조건으로 사용해 매니저가 같으면 같은 EmpLevel을 지정한다.

마지막 SELECT 문: cte_recursive 테이블을 검색한다.