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.
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
.
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.
The essence of SQL tuning in 200 pages
Buy on Amazon
(paperback only)
Paperback and PDF also available at Markus’ store.
Markus offers SQL training and consulting for developers working at companies of any size.
Learn more »