CTE (Common Table Expression)

출처 : http://technet.microsoft.com/ko-kr/library/ms190766.aspx


SQL Server 2005 온라인 설명서(2007년 9월)

공통 테이블 식 사용

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, NumberOfOrders 및 MaxDate)이 있으며 각 영업 사원의 SalesOrderHeader 테이블에 총 판매 주문 수 및 가장 최근의 판매 주문 날짜로 정의됩니다. 문이 실행될 때 영업 사원에 대해 선택한 열을 반환하기 위해 그리고 해당 영업 사원의 관리자에 대한 유사 세부 정보를 검색하기 위해 CTE가 두 번 참조됩니다. 영업 사원 및 관리자에 대한 데이터는 모두 단일 행으로 반환됩니다.

코드 복사

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

다음은 결과 집합의 일부입니다.

코드 복사

EmployeeID  NumberOfOrders MaxDate  ManagerID NumberOfOrders MaxDate
----------- -------------- ---------- --------- -------------- ----------
268         48             2004-06-01 273       NULL           NULL
275         450            2004-06-01 268       48             2004-06-01
276         418            2004-06-01 268       48             2004-06-01
277         473            2004-06-01 268       48             2004-06-01

 참고 항목

관련 자료

WITH common_table_expression(Transact-SQL)
SELECT(Transact-SQL)
INSERT(Transact-SQL)
UPDATE(Transact-SQL)
DELETE(Transact-SQL)
CREATE VIEW(Transact-SQL)

도움말 및 정보

SQL Server 2005 지원 받기