이해하고 유지하기 어려운 길고 복잡한 SQL 쿼리를 작성하는 데 지치셨습니까? 공통 테이블 표현식(CTE)만 있으면 됩니다.

CTE는 더 큰 SQL 쿼리의 컨텍스트 내에서 참조할 수 있는 임시 결과 집합을 정의하는 방법을 제공합니다. 쿼리를 더 작고 관리하기 쉬운 조각으로 나누면 더 읽기 쉽고 재사용 가능하며 유지 관리하기 쉬운 코드를 만들 수 있습니다.

CTE는 쿼리의 뷰와 마찬가지로 쿼리 문의 범위 내에서 런타임에 참조할 수 있는 임시 뷰입니다. CTE는 간단한 하위 쿼리와 유사하게 사용될 수 있으며 하위 쿼리와 정확히 동일하게 컴파일됩니다. 그러나 런타임 시 코드의 여러 섹션에서 이름이 지정되고 참조될 수 있습니다.

CTE가 필요한 이유는 무엇입니까?

더 큰 쿼리에서 하위 쿼리를 여러 번 사용하는 경우 동일한 쿼리 내에서 여러 번 참조되는 하위 쿼리로 바꿀 수 있습니다. 코드를 더 읽기 쉽고 디버그하기 쉽게 만듭니다.

CTE는 복잡한 쿼리 논리를 특정 변환을 처리하는 더 작고 잘 정의된 섹션으로 나눌 수 있습니다. 이를 통해 디버깅 및 문서화가 더 쉬워집니다.

경우에 따라 CTE를 사용하면 하위 쿼리를 실행해야 하는 횟수를 줄여 쿼리 성능을 향상할 수 있으므로 데이터가 메모리에 보관되고 여러 번 참조될 수 있으므로 디스크 io가 줄어듭니다.

CTE 시작하기

다음은 쿼리 문의 단일 CTE에 대한 구문입니다. CTE 이름이 뒤따르는 WITH 절로 시작하고 선택적으로 열 참조 열이 뒤따릅니다. 모든 열에 이름을 지정하거나 없음을 지정하고 지정하지 않으면 열 이름이 모호하지 않은 경우 모든 열이 선택됩니다. 괄호 안의 하위 쿼리로 끝납니다.

WITH CTE_NAME (column names) AS (simple subquery)
SELECT ...
FROM CTE_NAME;
-- Calculate age of employees
WITH employee_age (NAME, age)
     AS (SELECT NAME,
                Datediff(year, birthdate, Getdate()) AS Age
         FROM   employee) -- note that no comma separates CTE and query
-- Calculate average age
SELECT Avg(age) AS mean_age
FROM   employee_age

우리의 이야기는 여기서 끝나지 않습니다. 평균 연령보다 나이가 많은 모든 직원을 반환하고 싶다고 가정해 보겠습니다. 여기에서 이를 달성하기 위해 여러 CTE를 연결해야 합니다. 연결된 CTE는 아래 구문입니다.

WITH CTE_NAME (column names) -- first CTE starts with a WITH clause
AS (simple subquery), -- comma separates successive CTEs

NEXT_CTE (column names) AS (SELECT ...
FROM CTE_NAME)

SELECT ...
FROM NEXT_CTE;

-- Calculate age of employees
WITH EMPLOYEE_AGE (name,age) AS 
  (SELECT name,datediff(year,birthdate,getdate()) as Age
     FROM EMPLOYEE),

-- Calculate average age
AVERAGE_AGE -- column names are optional
AS (SELECT AVG(age) AS mean_age
  FROM EMPLOYEE_AGE)

SELECT * 
FROM  EMPLOYEE_AGE
  WHERE age>=(SELECT mean_age FROM  AVERAGE_AGE)

읽어 주셔서 감사합니다.