Types of CTEs

There exist two distinct types of CTEs: recursive and non-recursive:

• Recursive: Repeated procedural loops, or recursion, are used by recursive CTEs. This recursive query calls itself for as long as the condition stated is not satisfied. At least two CTE query definitions, an anchor member, and a recursive member must be present in the recursive CTE definition. Given that the recursive CTE keeps running until the query returns the complete hierarchy, it is helpful when working with hierarchical data.
• Non-recursive: These CTEs are simple and don’t include any repeated processing of a given sub-routine. An example of a query generating and making use of such CTE can be seen in the next subsection down below.