공통 테이블 식(CTE) 복습

SQL Server에서의 계층형 및 재귀 쿼리

Jasmin Ludolf

Content Developer

강의 개요

재귀란 무엇이며 어떻게 사용하는가?
공통 테이블 식(CTE)을 계층/재귀 쿼리에 어떻게 쓸까?
SQL로 계층을 어떻게 표현하고 질의할까?
실제 사례(예: 조직도, 자재명세서, 비행 계획)

러시아 인형과 그 재귀적 특성을 보여주는 이미지.

CTE란?

CTE의 정의

공통 테이블 식(CTE)은 단일 문 실행 범위 내에서 정의되는, 단순 쿼리에서 파생된 임시 명명 결과 집합을 지정합니다.

CTE의 구조

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

CTE의 활용

복잡한 쿼리 관리
SELECT, INSERT, UPDATE, DELETE에서 사용 가능
하나의 WITH에 여러 CTE 정의 가능
여러 CTE를 UNION 또는 JOIN으로 결합
뷰 대체
테이블 자기참조
재귀 쿼리

IT 조직용 CTE 정의

CTE로 관리자 선택:

WITH JOBS (id, SurName, JobTitle) AS

(
    SELECT 
          ID, 
          Name,
          Position
    FROM employee
    WHERE Position like '%Manager%'
),

CTE 정의를 위한 WITH AS
Employee 테이블에서 SELECT

IT 조직용 CTE 정의

연봉 10,000 초과 직원 선택

SALARIES (ID,Salary) AS (
   SELECT
        ID,
        Salary
   FROM ITSalary
   WHERE Salary > 10000)

IT 조직용 CTE 정의

전체 쿼리:

WITH JOBS (id, SurName, JobTitle) AS 
(    SELECT ID, Name, Position
        FROM employee 
        WHERE Position like '%Manager%'),

SALARIES (ID,Salary) AS
(    SELECT ID, Salary
        FROM ITSalary 
        WHERE Salary > 10000)

SELECT JOBS.Name, JOBS.Position, SALARIES.Salary
    FROM JOBS
    INNER JOIN SALARIES
    on JOBS.ID = SALARIES.ID;

SurNmames     | JobTitle          | Salary
Paul Smith    | IT Manager        | 15,000
Adam Peterson | Sourcing Manager  | 12,500
Anna Nilson   | Portfolio Manager | 10,500

연습해 봅시다!

SQL Server에서의 계층형 및 재귀 쿼리