Referencing the query_name
more than once in the query definition:
WITH RECURSIVE query_name (c) AS (
VALUES (1)
UNION
SELECT x FROM query_name JOIN base_table
ON base_table.x = query_name.c
UNION
SELECT y FROM query_name JOIN base_table
ON base_table.y = query_name.c
)
SELECT *
FROM query_name
Tutorial: With
— Organize Complex Queries
Other forms: cycle … set … to … default … using
, with
, 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” but non-linear recursion is prohibited in §7.17 Syntax Rule 3 j x.
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 »