Recursive queries are usually used in hierarchical queries.
1.Semantics of recursive query
with recursive T(a1,a2,...) as
(
non-recursive select
UNION [ALL]
recursive select involving T
)
select..... from T where ....
non-recursive select:
1.does not refer to T
2.generates an initial set of tuples for T
recursive select:
1.must be a query involving T
2. must include a where condition
3.must eventually return in empty result
2.Example: generate sum of first 100 integers
with recursive nums(n) as (
select 1
union
select n+1 from nums where n < 100
)
select sum(n) from nums;
sum
------
5050