[SQL 입문] 공통 테이블 식

04 - 3 공통 테이블 식

공통 테이블 식(common table expression)은 주로 데이터베이스에 없는 테이블이 필요할 때 사용하며, 바로 다음에 실행할 SELECT 문에만 사용해야 한다는 특징이 있습니다. 공통 테이블 식은 목적에 따라 일반 공통 테이블 식과 재귀 공통 테이블 식으로 나뉩니다. 공통 테이블 식은 줄여서 CTE라고도 부릅니다.

 

일반 CTE

CTE 안에서 UNION 문, UNION ALL 문, INTERSECT 문, EXCEPT 문을 사용해 여러 개의 일반 CTE 쿼리를 결합할 수 있습니다. 일반 CTE는 복잡한 쿼리를 단순하게 만들 때 사용하기 좋습니다.

-- CTE의 기본 형식
WITH [CTE_테이브 이름] (열 이름 1, 열 이름 2, ···)
AS
(
<SELECT 문>
)
SELECT [열 이름] FROM [CTE_테이블 이름];

-- 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'

-- CTE 테이블을 참조해 데이터 검색 : 열 개수가 맞지 않으면 오류
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 문 대신 UNION ALL 문을 사용하는 것이 좋습니다. 아무래도 UNION 문은 성능 문제를 일으키기 쉬우므로 꼭 필요한 경우가 아니라면 사용하지 않는 것이 좋기 때문입니다.

INTERSECT 문으로 CTE 결합하기

CTE에서 INTERSECT 문을 사용하는 방법은 내부 조인과 비슷하지만, 내부 조인의 경우 테이블 사이의 조인 조건에 맞는 데이터를 반환하고 INTERSECT 문은 각 쿼리에서 반환한 결과에서 중복 결과를 걸러 내 반환한다는 차이점이 있습니다.

-- CTE에서 INTERSECT 문으로 중복 결과 검색
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'

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

EXCEPT 문으로 CTE 결합하기

EXCEPT 문으로 CTE를 결합하는 방식은 NOT IN 문과 비슷하지만, EXCEPT 문은 결괏값에서 중복을 제거한 유일한 행을 반환하고 NOT IN 문은 중복을 제거하지 않고 반환하는 점이 다릅니다. 또한 CTE에서 먼저 작성한 쿼리 기준으로, 그다음 작성한 SELECT 문 쿼리와 중복되지 않는 데이터를 반환합니다.

-- CTE에서 EXCEPT 문으로 중복 제거한 결과 검색 : 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'
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-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 내부의 쿼리에서 재사용함으로써 반복 실행하는 쿼리 구조를 갖습니다. 재귀 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 쿼리가 필요합니다. 이떄 각 쿼리는 앵커 멤버(anchor member)와 재귀 멤버(recursive member)를 포함해야 합니다. 앵커 멤버는 자기 자신 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
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', 'Margheim', 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

※ 이 예제를 살펴보면 GO 문을 사용했음을 알 수 있습니다. GO는 문단의 끝을 의미하므로, 이 쿼리를 한 번에 실행하더라도 실제 데이터베이스 엔진이 처리할 때는 GO 문을 기준으로 각각 독립된 쿼리처럼 실행합니다. CREATE 문은 첫 행에 사용해야 한다는 제약 조건이 있어, GO 문을 사용해 쿼리의 첫 행처럼 작동하도록 처리했습니다.

-- 재귀 CTE 실습용 테이블에서 원하는 데이터 검색
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

 

실전 SQL

퀴즈 4. industry_group 테이블에서 industry 열의 데이터가 Oil에 해당하는 symbol을 industry_group_symbol 테이블에서 검색한 다음, nasdaq_company 테이블에서 해당 symbol의 company_name을 검색하세요(CTE 형식의 코드를 사용해 작성하세요).

WITH cte 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 a.*
FROM nasdaq_company AS a
INNER JOIN cte AS b ON a.symbol = b.symbol