공통 테이블 식 사용
CTE(공통 테이블 식)는 SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문 하나의 실행 범위 내에서 정의되는 임시 결과 집합이라고 볼 수 있습니다. CTE는 개체로 저장되지 않고 쿼리 지속 시간 동안만 존재한다는 점에서 파생 테이블과 비슷합니다. 그러나 CTE는 파생 테이블과 달리 자체 참조가 가능하며 동일 쿼리에서 여러 번 참조될 수 있습니다.
CTE를 사용하여 다음을 수행할 수 있습니다.
재귀 쿼리를 만들 수 있습니다. 자세한 내용은 공통 테이블 식을 사용하는 재귀 쿼리를 참조하십시오.
일반적인 뷰 사용이 필요하지 않을 때, 즉 메타데이터에 정의를 저장할 필요가 없을 때 뷰를 대체할 수 있습니다.
스칼라 하위 SELECT에서 파생된 열 또는 비결정적이거나 외부 액세스가 없는 함수를 기준으로 그룹화할 수 있습니다.
동일 문에서 결과 테이블을 여러 번 참조할 수 있습니다.
CTE를 사용하면 가독성이 향상되고 복잡한 쿼리를 쉽게 유지 관리할 수 있는 이점이 있습니다. 쿼리를 개별적이고 단순한 논리적 구성 블록으로 나눌 수 있습니다. 그런 다음 이 단순한 블록을 사용하여 최종 결과 집합이 생성될 때까지 보다 복잡한 중간 CTE를 작성할 수 있습니다.
CTE는 함수, 저장 프로시저, 트리거 또는 뷰 같은 사용자 정의 루틴에서 정의될 수 있습니다.
CTE의 구조
CTE는 CTE를 나타내는 식 이름, 선택적인 열 목록 및 CTE를 정의하는 쿼리로 구성되어 있습니다. CTE를 정의한 후에는 SELECT, INSERT, UPDATE 또는 DELETE 문에서 테이블이나 뷰처럼 참조할 수 있습니다. CTE는 CREATE VIEW 문에서 정의하는 SELECT 문의 일부분으로 사용될 수도 있습니다.
CTE의 기본 구문 구조는 다음과 같습니다.
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
모든 결과 열에 대한 고유 이름이 쿼리 정의에 제공된 경우에만 열 이름 목록이 선택 사항입니다.
CTE를 실행하는 문은 다음과 같습니다.
SELECT <column_list>
FROM expression_name;
예
다음 예에서는 CTE 구조의 구성 요소인 식 이름, 열 목록 및 쿼리를 보여 줍니다. CTE 식 Sales_CTE에는 3개의 열(SalesPersonID, SalesOrderID, and OrderDate)이 있으며 각 영업 사원의 연간 총 판매 주문 수로 정의됩니다.
USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO
다음은 결과 집합의 일부입니다.
SalesPersonID TotalSales SalesYear
------------- ----------- -----------
274 4 2001
274 20 2002
274 14 2003
274 10 2004
275 56 2001
275 139 2002
275 169 2003