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 (from, to) AS (
   SELECT from, to
     FROM edges
    WHERE id = ?
UNION DISTINCT
   SELECT from, to
     FROM path
     JOIN edges ON edges.from = path.to
) …

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. Union [distinct] belongs to mandatory feature E071-01.