Learn SQL Database Programming

Using recursive CTEs

The syntax for a recursive CTE is as follows:

WITH RECURSIVE ctename AS ( initial query  UNION ALL recursive query )SELECT * FROM ctename;

The initial query is the base result set of your CTE. This is also referred to as an anchor member. The recursive query is the query that will be referencing the CTE name. It is joined to the anchor query by a UNION ALL. If you're doing a simple counting style CTE, then you will also use what's referred to as a termination condition so that your CTE stops when the recursive query stops returning rows.

A simple example of recursion is as follows:

WITH RECURSIVE cte (x) AS( SELECT 1 UNION ALL SELECT x + 1 FROM cte WHERE x < 10)SELECT x FROM cte;

The preceding CTE will return ...