블로그를 만든 뒤로 T-SQL 에 대한 글을 처음 쓴다. 사실 DB 를 외치면서도 정작 깊이 있는 SQL 을 구현하지 못했던 과거의 모습들을 떨쳐버리고 SQL 의 기본 문맥들은 자주 사용하지만 (나 자신 뿐만 아니라 많은 DB를 사용하시는 이들...) DB에 대한 깊은 지식을 가지지 못한 것에 대해 반성한다. ^^
이번 계기를 통해, CTE (Common Table Expression) 에 관해 알아보며 시작하려 한다.

CTE (Language : sql)

   1:  WITH DirReps(ManagerID, DirectReports) AS 

   2:  (

   3:      SELECT ManagerID, COUNT(*) 

   4:      FROM HumanResources.Employee AS e

   5:      WHERE ManagerID IS NOT NULL

   6:      GROUP BY ManagerID

   7:  )

   8:  SELECT ManagerID, DirectReports 

   9:  FROM DirReps 

  10:  ORDER BY ManagerID;

간단한 예제로 먼저 시작해보겠다 (위의 예제는 SQL Server 2005 Online Book 에서 참조함). CTE 는 일시적인 결과물의 집합체를 생성한다. 쉽게 말해, CTE 는 일시적인 테이블을 만들어 주되, 그것에 이름을 붙여준다. 위의 예제에서 본다면
WITH 라는 키워드를 사용하여 DirReps 라는 일시적 Result Set 을 생성한다. 그 Result Set 은 보다시피 괄호 안의 SELECT ~~ 문으로 부터 생성된다. 하나의 Query 문 안에서 같은 CTE 이름은 사용 될 수 없으며 SELECT, INSERT, DELETE, UPDATE 문과 함께 사용 되어 질 수 있다. CTE는 또한 테이블 자신에 대한 참조로서 사용될 수 있기 때문에 Recursive CTE, 즉 순환 CTE 로 사용 될 수 있게 된다.
당연히 CTE 단독으로 사용 될 수는 없다. 반드시 CTE 뒤에는 SELECT, UPDATE, INSERT, DELETE 중의 한 가지 Query 문이 따라와야 한다. 또한 CREATE VIEW 문에서 SELECT 문과 함께 사용되어 질 수 있다.
DirReps 라는 CTE 이름 뒤에는 Column Name 이 따라온다 (사실 이 문이 왜 있어야 되는지는 모르겠으나..) 이 Column Name 은 CTE query definition(정의) 인 SELECT 문의 Column name 과 일치해야만 한다. 단, SELECT 문에서 전체 컬럼을 선택하는 경우에는 CTE 에서 Column Name 을 쓰지 않아도 된다.
CTE 문 안에서 또 다른 CTE 문은 선언될 수 없다. 그리고 CTE query definition (예제에서는 괄호안의 SELECT 문..) 이 하나 이상 사용 될 경우: UNION ALL, UNION, EXCEPT, INTERSECT Operator 를 사용하여 조인되어져야 한다.
자, 이제 CTE 에 관한 제약조건을 SQL Server 2005 online Books 를 통해 알아보자.
다음의 절은 사용 될 수 없다.

   1:  WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)

   2:  AS

   3:  (

   4:      SELECT SalesPersonID, COUNT(*), MAX(OrderDate)

   5:      FROM Sales.SalesOrderHeader

   6:      GROUP BY SalesPersonID

   7:  )

   8:  SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,

   9:      E.ManagerID, OM.NumberOfOrders, OM.MaxDate

  10:  FROM HumanResources.Employee AS E

  11:      JOIN Sales_CTE AS OS

  12:      ON E.EmployeeID = OS.SalesPersonID

  13:      LEFT OUTER JOIN Sales_CTE AS OM

  14:      ON E.ManagerID = OM.SalesPersonID

  15:  ORDER BY E.EmployeeID

위의 예제에서는 CTE가 여러차례 참조된 경우이다. 위와 같이 CTE로 생성된 일시적 Result Set 을 여러차례 참조하여 복잡한 Query 를 단순화 시켰다.

Recursive CTE (Language : sql)

   1:  WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 

   2:  (

   3:      SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel

   4:      FROM HumanResources.Employee

   5:      WHERE ManagerID IS NULL

   6:      UNION ALL

   7:      SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1

   8:      FROM HumanResources.Employee e

   9:          INNER JOIN DirectReports d

  10:          ON e.ManagerID = d.EmployeeID 

  11:  )

  12:  SELECT ManagerID, EmployeeID, EmployeeLevel 

  13:  FROM DirectReports

굉장히 복잡해 보이는데, 순환문이다. 뭘 찾고자 하는 것인지 골똘히 생각해 보길 바란다.
그 외에도 더 복잡한 설명들은

여기를 클릭에서 참조하길 바란다.