주로 데이터베이스에 없는 테이블이 필요할 때 사용하며, 바로 다음에 실행할 SELECT 문에만 사용해야 한다.
일반 CTE
| CTE의 기본 형식 |
| WITH [CTE_테이블 이름] (열 이름1, 열 이름 2, ...) AS ( <SELECT 문> ) SELECT [열 이름] FROM [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'
→해석: AS(...)에 포함된 <SELECT>문을 통해 반환된 date, symbol, [close] 열의 값을 WITH 문의 cte_stock_price 테이블의 date, symbol, price 열에 대입한 다음, 마지막 SELECT 문의 cte_stock_price 테이블에서 symbol이 'MSFT'인 조건을 만족하는 모든 행을 출력하겠다.
결과
date symbol price
2021-01-08 00:00:00.000 MSFT 219.619995117188
2021-01-07 00:00:00.000 MSFT 218.289993286133
2021-01-06 00:00:00.000 MSFT 212.25
2021-01-05 00:00:00.000 MSFT 217.899993896484
2021-01-04 00:00:00.000 MSFT 217.690002441406
CTE에서 정의한 열 개수와 CTE의 <SELECT문>에서 얻은 열 목록이 다르면 에러가 발생
UNION 문과 UNION ALL 문으로 CTE 결합하기
이 둘의 차이점은 중복을 제거한 행 포함 여부이다.
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'
→해석: AS(...)에 2개의 SELCET 문이 있는데, UNION ALL을 통해 이들 두 SELECT문의 결과를 모두 cte_stock_price 테이블에 반환하고, 마지막 SELECT 문의 cte_stock_price 테이블에서 symbol이 'MSFT'인 조건을 만족하는 모든 행을 출력하겠다.
결과
date symbol price
2021-01-04 00:00:00.000 MSFT 217.690002441406
2021-01-05 00:00:00.000 MSFT 217.899993896484
2021-01-06 00:00:00.000 MSFT 212.25
2021-01-07 00:00:00.000 MSFT 218.289993286133
2021-01-08 00:00:00.000 MSFT 219.619995117188
2021-02-01 00:00:00.000 MSFT 239.649993896484
2021-02-02 00:00:00.000 MSFT 239.509994506836
2021-02-03 00:00:00.000 MSFT 243
2021-02-04 00:00:00.000 MSFT 242.009994506836
2021-02-05 00:00:00.000 MSFT 242.199996948242
중복을 제거한 결과를 보고 싶다면 UNION 문을 사용한다.
※ 주의: UNION 문과 UNION ALL 문은 다른 쿼리로 데이터를 어느 정도 거른다음 사용하는 경우가 많으므로 UNION ALL 문의 사용빈도가 더 높다. 즉, 되도록이면 다른 쿼리에서 중복을 제거한 다음 UNION ALL 문을 사용하라.
INTERSECT 문으로 CTE 결합하기
내부 조인과 비슷하나, 내부 조인은 테이블 사이의 조인 조건에 맞는 데이터를 반환하고, INTERSECT 문은 각 쿼리에서 반환한 결과에서 중복 결과를 걸러 내 반환하다는 차이점이 있다. INTERSECT 문은 테이블 A 와 테이블 B의 교집합에 해당한다.
예제)
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'
→해석: AS(...) 내에 2개의 SELECT 문(SELECT1, SELECT2)이 있는데, INTERSECT 문을 통해 SELECT1과 SELECT2의 공통 부분(중복)만을 반환해서, 마지막 SELECT 문의 cte_stock_price 테이블에서 symbol이 'MSFT'인 조건을 만족하는 행을 출력하겠다.
결과
date symbol price
2021-01-07 00:00:00.000 MSFT 218.289993286133
2021-01-08 00:00:00.000 MSFT 219.619995117188
EXCEPT 문으로 CTE 결합하기
NOT IN과 비슷하나, EXCEPT 문은 결괏값에서 중복을 제거한 유일한 행을 반환하고 NOT IN은 중복을 제거하지 않고 반환한다. 또한, CTE에서 먼저 작성한 쿼리 기준으로, 그 다음 작성한 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'
EXEPCT
SELECT date, symbol, [close] FROM stock
WHERE date >= '2021-01-07' AND date <= '2021-01-20'
)
SELECT * FROM cte_stock_price WHERE symbol = 'MSFT'
→해석: AS(...) 내에 2개의 SELECT 문(SELECT1, SELECT2)이 있는데, EXCEPT 문을 통해 SELECT1 쿼리를 (2021-01-1~10) 기준으로 SELECT2와의 공통 부분(중복)(2021-01-07~10)을 제거해 (2021-01-1~7) 반환해서, 마지막 SELECT 문의 cte_stock_price 테이블에서 symbol이 'MSFT'인 조건을 만족하는 행을 출력하겠다.
date symbol price
2021-01-04 00:00:00.000 MSFT 217.690002441406
2021-01-05 00:00:00.000 MSFT 217.899993896484
2021-01-06 00:00:00.000 MSFT 212.25
예제) CTE 내부의 SELECT 순서 변경
WITH cte_stock_price (date, symbol, price)
AS
(
SELECT date, symbol, [close] FROM stock
WHERE date >= '2021-01-07' AND date <= '2021-01-20'
EXEPCT
SELECT date, symbol, [close] FROM stock
WHERE date >= '2021-01-01' AND date <= '2021-01-10'
)
SELECT * FROM cte_stock_price WHERE symbol = 'MSFT'
결과
date symbol price
2021-01-11 00:00:00.000 MSFT 217.490005493164
2021-01-12 00:00:00.000 MSFT 214.929992675781
2021-01-13 00:00:00.000 MSFT 216.339996337891
2021-01-14 00:00:00.000 MSFT 213.020004272461
2021-01-15 00:00:00.000 MSFT 212.649993896484
2021-01-19 00:00:00.000 MSFT 216.440002441406
2021-01-20 00:00:00.000 MSFT 224.339996337891
재귀 CTE
CTE 결과를 CTE 내부의 쿼리에 재사용함으로써, 반복 실행하는 쿼리 구조를 갖는다. 재귀 CTE는 주로 계층 데이터를 검색할 때 많이 사용한다.
| 재귀 CTE의 기본 형식 |
| WITH [CTE_테이블 이름] (열 이름1, 열 이름 2, ...) AS ( <SELECT * FROM 테이블 A> ←-쿼리1(앵커 멤버) UNION ALL <SELECT * FROM 테이블 B JOIN CTE_테이블 이름> ←쿼리2(재귀 멤버) ) SELECT * FROM [CTE_테이블 이름] |
기본 형식에서 보듯이 재귀 CTE는 적어도 2개의 CTE 쿼리가 필요하다. 이 때 각 쿼리는 앵커 멤버와 재귀 멤버를 포함해야 한다. 앵커 멤버는 자기 자신 CTE를 참조하지 않는다. 주의할 점은 앵커 멤버는 첫 번째 재귀 멤버 앞에 있어야 하고, 재귀 멤버의 열 자료형은 반드시 앵커 멤버의 열 자료형과 일치해야 한다. 또한 앵커 멤버와 재귀 멤버는 여러 개 정의할 수 있다.
| 재귀 CTE의 실행 순서 |
| ① 최초에 쿼리 1을 실행한다. 이때 쿼리 2의 기본값은 0으로 초기화한다. ② 쿼리 2를 실행한다. 이때 쿼리 2의 기본 값은 1씩 증가한다. 쿼리 1의 결과 행 수만큼 쿼리 2에서 CTE_테이블 이름을 재귀 호출하고, 쿼리 2의 기본값이 1씩 증가하면서 쿼리 1의 결과 행 수에 도달해 결과가 더 없다면 재귀 호출을 중단한다. ③ 외부 SELECT 문에서 과정 ①, ②를 통해 만든 CTE 누적 결과를 검색한다. |
예제)
테이블 생성
IF OBJECT_ID('doit_cte_recursive', 'U') IS NOT NULL
DROP TABLE dbo.doit_cte_recursive
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)
INSERT INTO doit_cte_recursive VALUES (107, 'Dylan', 'Miller', 103)
INSERT INTO doit_cte_recursive VALUES (108, 'Diane', 'Marghein', 105)
INSERT INTO doit_cte_recursive VALUES (109, 'Gigi', 'Matthew', 105)
INSERT INTO doit_cte_recursive VALUES (110, 'Michael', 'Raheem', 106)
SELECT * from doit_cte_recursive
결과
| EmployeeID | FirstName | LastName | ManagerID | |
| 1 | 101 | Ken | Sanchez | NULL |
| 2 | 102 | Terri | Duffy | 101 |
| 3 | 103 | Roberto | Tamburello | 101 |
| 4 | 104 | Rob | Walters | 102 |
| 5 | 105 | Gail | Erickson | 102 |
| 6 | 106 | Jossef | Goldberg | 103 |
| 7 | 107 | Dylan | Miller | 103 |
| 8 | 108 | Diane | Marghein | 105 |
| 9 | 109 | Gigi | Matthew | 105 |
| 10 | 110 | Michael | Raheem | 106 |
WITH cte_recursive (EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, MgrID, 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
| FullName | EmpLevel | Manager | |
| 1 | Ken Sanchez | 1 | NULL |
| 2 | Terri Duffy | 2 | Ken Sanchez |
| 3 | Roberto Tamburello | 2 | Ken Sanchez |
| 4 | Rob Walters | 3 | Terri Duffy |
| 5 | Gail Erickson | 3 | Terri Duffy |
| 6 | Jossef Goldberg | 3 | Roberto Tamburello |
| 7 | Dylan Miller | 3 | Roberto Tamburello |
| 8 | Diane Marghein | 4 | Gail Erickson |
| 9 | Gigi Matthew | 4 | Gail Erickson |
| 10 | Michael Raheem | 4 | Jossef Goldberg |
→해석: 1번째 SELECT 문의 SELECT EmployeeID, FirstName, LastName, MgrID, 1을 보면, 최상위 직원을 검색해 EmpLevel을 1로 지정한다. 2번째 SELECT 문은 1번째 SELECT 문이 반환한 값을 doit_cte_recursive 테이블과 조인하면서 바로 앞 행의 EmpLevel에 1을 더하며 재귀 호출한다. 이때 ManageID와 EmpID가 같은 값을 조인 조건으로 사용해 매니저가 같으면 같은 EmpLevel을 지정한다. AS(...) 의 결과가 WITH 문의 cte_recursive 테이블에 입력되고, 다시AS(...) 내의 재귀 멤버에서 cte_recursive을 호출하여, 결과를 cte_recursive 테이블에 반환한다. 이 재귀 호출은 테이블 행 끝까지 진행한다. 마지막 SELECT 문은 cte_recursive 테이블을 검색한다. 스칼라 서브 쿼리를 사용해 FullName과 EmpLevel을 검색하고 각 행의 Manage 이름을 검색한다.
퀴즈 4. industry_group 테이블에서 industry 열의 데이터가 Oil에 해당하는 symbol을 industry_group_symbol 테이블에서 검색한 다음, nasdaq_company 테이블에서 해당 symbol의 company_name을 검색하세요(CTE 형식의 코드를 사용해 작성).
WITH cte_1
AS
(
SELECT b.symbol
FROM industry_group as a
INNER JOIN industry_group_symbol as b ON a.num = b.num
WHERE a.industry ='Oil'
)
SELECT c.symbol, c.company_name
FROM nasdaq_company as c
INNER JOIN cte_1 as d ON c.symbol = d.symbol
'IT Story > SQL Story' 카테고리의 다른 글
| SQL 함수: 날짜 함수 (0) | 2022.12.02 |
|---|---|
| SQL 함수: 문자열 함수 (0) | 2022.12.02 |
| 서브 쿼리 (0) | 2022.11.30 |
| 조인(Join): 외부 조인 (0) | 2022.11.29 |
| 조인(Join): 내부 조인 (0) | 2022.11.28 |
