Can I use… with recursive (top level)

1. Without keyword recursive
2. No join in recursive branch—use comma-join (,)
3. Without column list: WITH RECURSIVE query_name AS (SELECT…)

The with recursive clause allows a query to refer to its own output:

WITH RECURSIVE query_name (column_name, …) AS (
   SELECT …
     FROM …
UNION ALL
   SELECT …
     FROM query_name -- <= Note the self-reference here
) [, …]
SELECT …
  FROM query_name

Related

• Tutorial: With — Organize Complex Queries

• Other forms: cycle … set … to … default … using, with, with in subquery, with recursive in subquery.

• Product specific forms: connect by.

Normative References

The with recursive clause is defined in ISO/IEC 9075:2016-2 as part of optional feature T131, “Recursive query”.