About

Acronym for Common Table Expression. Temporary, reusable subquery that may be recursive.

A CTE can be thought of as a derived table, similar to a subquery or a view. It can use indexes and statistics of underlying objects and is only visible in the context of the current query.

A recursive CTE (rCTE) is a special application of the feature that can refer to itself in the second part of a UNION statement, like so:

WITH cte AS (
   SELECT person_id, parent_id, 1 AS level
   FROM   person
   WHERE  parent_id IS NULL  -- has no parent

   UNION ALL
   SELECT p.person_id, p.parent_id, c.level + 1 AS level
   FROM   cte AS c 
   JOIN   person AS p ON p.parent_id = c.person_id
)
SELECT * FROM cte
ORDER  BY level
history | excerpt history