It is possible to refer to a CTE from itself. Statements like this are called recursive queries. Recursive queries must have a special structure that indicates to the database that the subquery is recursive. The structure of a recursive query is as follows:
WITH RECURSIVE <subquery_name> (<field list>) AS( <non-recursive term> UNION [ALL|DISTINCT] <recursive term>)[,...]<main query>
Both non-recursive and recursive terms are subqueries that must return the same number of fields of the same types. The names for the fields are specified in the declaration of the whole recursive query; therefore, it does not matter which names are assigned to the fields in the subqueries.
The non-recursive term is also called ...
