Can I use… with recursive (… union [distinct] …)

Union [distinct] within with recursive can be used to omit duplicate rows and thus preventing infinite loops.

WITH RECURSIVE path (a, b) AS (
  SELECT edges.* FROM edges
                WHERE a = 1
UNION
  SELECT edges.* FROM edges
                 JOIN path 
                   ON edges.a = path.b
)
SELECT *
  FROM path

The example properly terminates even if the graph stored in the edges table has cycles.

Related

• Tutorial: With — Organize Complex Queries

• Detect cycles by selected key values: cycle…set…to…default…using, cycle…set…to…default, cycle…restrict.

• Other forms: with recursive, 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”. Union [distinct] belongs to mandatory feature E071-01.