DataTerminology.comNEW
Also known as: WITH RECURSIVE, Recursive Query
A self-referential CTE that iteratively expands results, enabling hierarchical or graph-like traversal in SQL.
DESCRIPTION
A recursive CTE repeatedly references itself to build hierarchical query results, starting from an anchor set and applying a recursive step until no new rows appear. It supports organizational charts, bill-of-materials, folder trees, and graph reachability. SQL engines typically require UNION ALL between anchor and recursive parts and safety guards against infinite recursion. Ordering and cycle detection must be considered for correctness. Performance depends on indexes and pruning conditions. It offers portability compared to vendor-specific hierarchical syntax. For complex graph analytics, specialized engines may be more suitable.
ALTERNATIVE DEFINITIONS
PostgreSQL Documentation
A recursive query is one that references itself.
Defines recursion in the simplest terms within the CTE context.
SQLite Documentation
A recursive common table expression is one in which the CTE table name appears in the CTE subquery.
Clarifies the structural self-reference that makes a CTE recursive.
RELATED TERMS
Common Table Expression Hierarchy Graph Traversal Depth-First Search Breadth-First Search Parent-Child Topological Sort Transitive Closure Join
COMPETING TERMS
Hierarchical Query Graph Database
KNOWLEDGE AREAS
Programming Languages Data Engineering Data Warehousing & BI Data Modeling
TARGET AUDIENCE
Data Engineer Data Analyst BI Specialist Data Modeler Software Developer
EXTERNAL SOURCES
- Wikipedia - Hierarchical and recursive queries in SQL
- PostgreSQL - Recursive WITH documentation
- SQLite - WITH and WITH RECURSIVE syntax
- Microsoft - T-SQL recursive CTE specification
TOP QUESTIONS
When should I use a Recursive CTE?
Use it for hierarchical traversals like org charts, category trees, or path enumeration when you need SQL-native recursion.
Category: Requirements
What are common mistakes in Recursive CTEs?
Missing termination conditions or cycle checks leads to infinite recursion. Also, using UNION instead of UNION ALL can suppress necessary duplicates.
Category: Mistakes
How do I detect cycles in a Recursive CTE?
Track visited nodes in a path column and filter repeats, or use engine-specific cycle detection features where available.
Category: Implementation
Is a Recursive CTE efficient?
It can be with proper indexing and pruning. For deep or dense graphs, consider materialization or graph databases for better performance.
Category: Performance
What is an alternative to Recursive CTE in Oracle?
Oracle’s CONNECT BY provides hierarchical queries; modern versions also support recursive subquery factoring similar to CTEs.
Category: Alternatives
How do I order hierarchical results?
Maintain a path string or numeric sequence during recursion and sort by it, or use vendor features like ORDER SIBLINGS BY.
Category: Implementation
What is the biggest misunderstanding about Recursive CTEs?
That they are always slow; with proper indexing and limited branching, they can be performant and clear.
Category: Misunderstanding
Can Recursive CTEs compute transitive closure?
Yes, by iteratively joining parent-child relationships until no new edges are found, while avoiding duplicates.
Category: Example
Where do I start learning Recursive CTEs?
Begin with a simple parent-child table and build an anchor plus recursive step, verifying termination and ordering.
Category: Learning