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
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
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.