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
CTEs are used in SQL Server 2005 and later.
Postgres introduced CTEs with version 8.4 and data-modifying CTEs with version 9.1. Also called "WITH Queries" here.
Oracle documentation prefers the name "Subquery Factoring" over CTE.
Hierarchical queries often use CTEs. An alternative is the
CONNECT BYclause existing since version 2 (1977). Comparing
CONNECT BYand CTEs in Oracle.