[SQL] 공통 테이블 식 Common Table Expression, CTE

기본 개념

주로 데이터베이스에 없는 테이블이 필요할 때 사용된다. 

바로 다음에 실행할 SELECT 문에만 사용해야 한다는 특징이 있다. 

일반 CTE 

기본 형식 

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

 

UNION 문으로 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'

 

INTERSECT 문으로 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'
  INTERSECT
  SELECT date, symbol, [close]  FROM stock
  WHERE date >= '2021-02-01' AND date <= '2021-02-07'

EXCEPT 문으로 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'
  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 순서 변셩
WITH cte_stock_price (date, symbol, price)
AS
(
  SELECT date, symbol, [close], FROM stock
  WHERE date >= '2021-01-07' AND date <= '2021-01-20'
  EXCEPT
  SELECT date, symbol, [close] FROM stock
  WHERE date >= '2021-01-01' AND date <= '2021-01-10'
)
SELECT * FROM cte_stock_price WHERE symbol = 'MSFT';

재귀 CTE 

기본 형식 

WITH [CTE_테이블 이름] (열 이름 1, 열 이름 2 ...)
AS(
  <SELECT * FROM  테이블 A>
  UNION ALL
  <SELECT * FROM 테이블 B JOIN CTE_테이블 이름>
)
SELECT * FROM [CTE_테이블 이름];

 

재귀 CTE 실행 순서 

1. 최초에 쿼리 1을 실행한다. 이때 쿼리 2의 기본값은 0으로 초기화된다. 
2. 쿼리 2를 실행한다. 이때 쿼리 2의 기본값은 1만큼 증가한다. 쿼리 1의 결과 행 수만큼 쿼리 2에서 CTE_테이블 이름을 재귀 호출하고, 쿼리 2의 기본값이 1씩 증가하면서 쿼리 1의 결과 행 수까지 도달해 결과가 더 없다면 재귀 호출을 중단한다. 
3. 외부 SELECT 문에서 과정 1,2 를 통해 만든 CTE 누적 결과를 검색한다. 

 

예시

IF OBJECT_ID (doit_cte_recursive', 'U') IS NOT UNLL
DROP TABLE dbo.doit_cteretursive
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', 'Sanchez', 101)
INSERT INTO doit_cte_recursive VALUES (104, 'Bob', 'Sanchez', 102)
INSERT INTO doit_cte_recursive VALUES (105, 'Gail', 'Sanchez', 102)
INSERT INTO doit_cte_recursive VALUES (106, 'Jossef', 'Sanchez', 103)
INSERT INTO doit_cte_recursive VALUES (107, 'Dylan', 'Sanchez', 103)
INSERT INTO doit_cte_recursive VALUES (108, 'Diane', 'Sanchez', 105)
INSERT INTO doit_cte_recursive VALUES (109, 'Gigi', 'Sanchez', 105)
INSERT INTO doit_cte_recursive VALUES (110, 'Michael', 'Sanchez', 106)

SELECT * FROM doit_cte_recursive

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.MarID) AS Manager
FROM cte_recursive
ORDER BY EmpLevel, MgrID