WITH RECURSIVE query_name AS (SELECT…)
recursive
join
in recursive branch—use comma-join (,
)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
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
.
The with recursive
clause is defined in ISO/IEC 9075:2016-2 as part of optional feature T131, “Recursive query”.
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 »