About

A CTE can be thought of as a disposable view or a reference-able subquery - it uses the indexes and statistics of the underlying objects to perform operations, and only lasts until the next query is executed. A CTE can only be reused within the query that references it.

One of the major uses of CTEs is for recursion, most commonly to iterate through hierarchical data without the need for CURSORs or WHILE loops. A CTE can refer to itself in the second part of a UNION statement, like so:

WITH CTE AS (
   SELECT Id, ManagerId, 1 as Level
   FROM dbo.People
   WHERE ManagerId IS NULL
   UNION ALL
   SELECT p.id, p.ManagerId, c.level + 1 as Level
   FROM dbo.People as P
   INNER JOIN Cte as C ON C.id = P.ManagerID
)
SELECT * FROM CTE ORDER BY Level

CTEs are used in SQL Server 2005 and later versions to simplify complicated queries and perform recursive operations.

Oracle documentation prefers the name Subquery Factoring over CTE.

Hierarchical queries often use CTEs. While they can be used for this in Oracle, often the CONNECT BY clause is used due to it's existence since version 2 (1977). Here is a comparison of the similarities and differences of CONNECT BY and CTEs on Oracle.

history | show excerpt | excerpt history